New Database

[Cabot]

I have attempted to create a new database and am having a few problems... I am curious to know the best way to set up the following database: used to schedule certain types of patrols on a specific pipeline section during specific months for a specific person.

Here is basically how I am set up now...
The main table, Pipelines - with the primary key set on the pipeline number, contains general information about the pipeline.

A table for each of the following types of patrols which includes which month the patrol will take place, a description of the section, etc. These tables can duplicate the pipeline number since each section to be patrolled may be somewhat different.

Annual
Bi-Annual
Tri-Annual
Class
Highway
Railroad
Stream
Valve

I can run a query with the criteria of a certain person for a certain month for each of the tables above separately (ex: Annual table for John Doe, patrols during the month of March... I am however, unable to run a query for all of them at once (ex: all tables, for John Doe, patrols during the month of March) I get zero records... is it because these table are not related to one another? Each one is related back to the main pipeline table.

I have also attemped to run each query separately and then combine each of them into a report without success of what I want to see.

Confused yet? Any help would be greatly appreciated... I am willing to redesign if necessary... I have attempted this database from several angles and so far nothing is working.

 

[The_Doc_Man]

Too many tables.

Make one PATROL table with one of the fields being the frequency of the patrol. Another COULD be the person assigned to that patrol at this time.

To keep it pure, have a PatrolPerson table to provide an ID number for your patrollers.

Then the patrols table might contain [pipelineID], [PersonID], [PatrolFrequency].

 

[Cabot]

I actually started creating just a single patrol table in the beginning, but wasn't sure, so I ditched the idea... now that I see that was the right way to proceed, should I make the type of patrols as one field as drop down or each its own field as a check box, same question for the month.

I suppose either would work fine, I'm just wondering which way is best.

Thanks for help.