Tables and linking

  • Thread starter Thread starter Michael Waters
  • Start date Start date
M

Michael Waters

Hello,
I am setting up a database to keep track of
frequencies in various police scanners. There are 3
police scanners, with up to 400 channels in each one. I
set up one table that has the following fields, CH Number
(which is the pirmary key), Frequency, Location, and Type
of service. The problem I am having is most of the
frequencies are used by multiple towns in the area. So I
am trying to set up a way to link this table to another
one that will have the locations. I can't seem to get
this to work at all. The way I would like to do it is if
say frequency 33.900 is used by both Hershey, and
Harrisburg, then I'll enter the Ch number, frequency, and
type on one form, then either on a subform, or on another
form that I can bring up enter the two(or more)
locations. I would like to have it set up then that I can
do various reports. One showing the CH Number, frequency,
type, and all the locations. Then also another one that
will be broken down by locations. So it will show say
Hershey, and then all the frequencies used by them, behind
them the type and CH number. Then it would show the next
town/area and the same information for all the town/areas
and frequencies. If anyone can give me any suggestions
that would be great.

Thanks,
Mike
 
This can either be easy or complicated. It depends upon
what else you require (that was not mentioned yet).

Are you keeping track of each scanner's settings
seperately or all the scanners to be programed exactly the
same?

Are you permitting several channels to have the same
frequency or not?

If we throw out the multiple scanner and multiple channels
with same freq problems, we can reduce this to four tables.

tblChannels, tblFreqs, tblUsage, and tblChannelFreqs.

I would do it this way to keep the constraints on the one
sides such that:

tblChannels has a primary key of each channel 001 - 500
tblFreqs list all the frequencies you care to ever use as
its primary key. Both of these tables may or may not have
any other interesting attributes you care to track about
the primary keys (such as common use of a given freq, if
you prioritize or lockout a channel, etc.).

Then, two intersection tables that create two different
sets of data.

tblChannelFreqs would just have two fields, the primary
key of each of the previous two tables listed (that is,
the channel and the freq that it is programed for).

tblUsage would have three fields, the primary key of
tblFreqs and a location and purpose.

hope this is helpful.

Dwayne
 
Thanks for the reply. I did kind of get it to work by
having a table where I entered the information several
tiems for each location/frequency, but would like to be
able to do it all with one form(and subforms)/line on that
form where I only have to enter the CH number Frequency
and Use once.
As for the questions you asked. Each scanner will be
programmed differenly. The suggested notation for the
scanners and channels is (scanner number).(Channel number)
and example would be 1.10 is scanner 1 channel 10.
Each scanner/channel should all have different
frequencies in them, unless someone makes a mistake and
enteres one twice, in which case hopefully the database
woudl catch it.
I'll try starting with your suggestion and see what I
can do.

Mike
 
Back
Top