INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Multiple table fields related to multile records in another table

Multiple table fields related to multile records in another table

(OP)
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
 

RE: Multiple table fields related to multile records in another table

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.

RE: Multiple table fields related to multile records in another table

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close