Multiple table fields related to multile records in another table

[BrianLe]

In Access 97, I'm trying to create a database for jobs, tasks (which are involved in the job), and hazards (which are involved in the task). The database will be populated by assigning hazards to the tasks, and tasks to the jobs. So for each job, there are various tasks to pick from, and for each task, there are various hazards to pick from. There could be 20+ tasks in a job, and 20+ hazards in a task. I've created the following tables:

tblJob
JobID
JobDesc
TaskID1
TaskID2
TaskID3
...

tblTask
TaskID
TaskDesc
HazardID1
HazardID2
HazardID3
...

tblHazard
HazardID
HazardDesc

Each Job will have one or more Tasks, and each Task will have one or more Hazards.

From the database, I will want to create reports that show the hazards involved with each job.

Assuming the following tables.

tblJob

JobID JobDesc TaskID1 TaskID2 TaskID3
1 A 2 4 5
2 B 2 3 6
3 C 3 4 5
...

tblTask

TaskID TaskDesc HazardID1 HazardID2 HazardID3
1 D 1 3 5
2 E 4 5 6
3 F 3 4 6
4 G 1 3 5
5 H 2 4 5
6 I 3 5 6
...

tblHazard

HazardID HazardDesc
1 T
2 U
3 V
4 W
5 X
6 Y

A report of Jobs should look like this.

Job Task Hazard Hazard Hazard
A E W X Y
G T V X
H V W X

B E W X Y
F V W Y
I V X Y

C F V W Y
G T V X
H V W X

The table structure doesn't seem right since there aren't any fields in common to them to enable relating one to another.

Am I on the right track?

Thanks,

Brian

 

[MajP]

Your tables should simply be

tblJob
JobID
JobDesc

tblTask
TaskID
TaskDesc

tblHazard
HazardID
HazardDesc

then a join table

tblJob_Task_Hazard
jobID_FK (foreign key to job table)
taskID_FK (foreign key to task table)
hazardID_FK (foreign key to hazard table.

example
1 1 1 (job 1 has task 1, and task 1 has hazard 1)
1 1 2
1 2 3
2 1 4

This is the proper way to store the data. The trick is the interface. I would have three linked continous forms on a main form. Select a job, second subform shows related tasks, click on one of the tasks and third subform shows the related hazards. You then may want to look at a crosstab query to turn records into columns.

 

[PHV]

Your table aren't normalized.
You should use junction tables for many to many relations.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

Thanks MajP and PH. I'll set up my tables as suggested and try to set up the forms too. I may be back for more help on the forms. If so, I'll post in the Access Forms forum. I'll let you know how it comes out.

Brian