Database Design and Form Layout/Setup

[gungrave19]

I have a database with 3 main tables: Employees, Jobs, and Procedures.

Criteria:
1. Each employee can have multiple jobs and each job can have multiple employees.
2. Each job can have multiple procedures and each procedure can be in multiple jobs.

From this criteria I have a few many-to-many relationships so I have created a junction/associate table between Employees and Jobs and one between Jobs and Procedures. (See attachment)

Additional Criteria:
1. When an employee is qualified for a procedure his qualification date and requalification date (qualification date + 1 year) is stored in another junction table between Employees and Procedures since an employee can have multiple procedures and vice versa. The reasoning behind that is the qualification date and requalification date is NOT specific to the procedure but to the employee i.e. Employee A can be qualified for Procedure 1 today but Empoyee B can be qualified for the same procedure a month ago. (Also the reason why the qualification dates are not specific to the employee/job relationship is that it is common for a single procedure to be updated. When that happens the employee only needs to be requalified for that procedure not all the procedures related to his job.)

So here's what I need to do:
Create a form that cycles through employees, showing their related jobs and subsequent related procedures with qualification dates. I've tried creating a form based on the Employees table then adding a subform that shows the jobs related to the selected Employee and finally creating a subform (within the Jobs subform) that shows the Procedures related to the jobs that relate to the employee. That is working great. However my problem is that I can't get the qualification dates to show up.

Also I need to make sure that when a user adds or edits an employee and selects a job for the employee all of the associated procedures will display as well.

Any ideas? I'm open to any and all! Please ask any questions; I'll be available to answer them or provide more info/screenshots if needed.

Thanks so much.

 

[spikepl]

Good job on the data structure. But there is no point in us trying to recreate in our minds what you already have IRL; attach a db for us to play with, and I am sure someone will play

 

[gungrave19]

Good point, Spike.

I've attached my sample database. Data should be pretty straightforward. I've been using the Employees form to try and get what I need to display. I was also messing with a copy of it too.

Please forgive me for any bad naming conventions/design.

 

[spikepl]

Perhaps something along the lines of the attached (untested)

 

[gungrave19]

Thanks Spike! After a bit of testing, that seems to have done it.