I took a look at your database, and here's my two cents - for what it's worth (which, in the current economy is maybe half a penny at the most
). Keep in mind, I did glance through most of this thread, but did not read every post in detail, so some of this may have been mentioned before by Rain or jdraw.
If you break this down to its simplest form, you're trying to model a relationship between
Staff (employees) and
Jobs. Each Staff member needs to be trained on multiple jobs, and each Job can have multiple Staff members trained for it. So, you have the following entities;
1)
Staff
2)
Jobs
3)
Training
and, as I understand things from previous posts in this thread, since each instance of Training may require multiple SOP's, then we can consider SOP's as another entity as well.
Now, as per the description of your model, you have a many-to-many relationship between
Staff and
Jobs. A many-to-many relationship needs a junction table, which I know has already been discussed here and you have taken under advisement. Where you went wrong (IMO) is when you created a new table (tblStaffTraining) and set it up as a junction table between
Staff and
Training. First, the m-to-m relationship is between Staff and Jobs,
not Staff and Training. Second, you didn't need to create a new junction table to begin with because one already existed (hint: it's the Training table).
Training is the junction between Staff and Jobs. In this case, your Training table happens to need a child table of it's own (SOP's), which is fine. Sometimes that's what the model requires. Now, normally, I would not use a surrogate primary key in a junction table, I would just use a compound key based on the FK fields. However, in cases where a junction table has a child table of it's own, I
would use a surrogate key to establish the relationship with the child table (hopefully, my comments on this matter will not throw this thread into some tangential discussion about compound keys vs. surrogate keys, blah blah blah). Actually, this child table (tblTrainedSOPData) is itself a junction table, because you really have another m-to-m relationship here between Training and SOP's.
So, my advice would be for you to try the following and see if it makes more sense for your model. Do this on a test copy of your app of course.
1) Remove tblStaffTraining. It serves no purpose that I can see and is only confusing you.
2) Add the fields StaffID and JobID to tblTrainingRecords. Make them Number data type. Get rid of the EmployeeNo and JobTitle fields you have there now. The only values you want to store here are the PK values from the other tables as FK's. Establish relationships between Staffs/Training and Jobs/Training based on these new fields.
3) Leave the existing TrainingID PK field in tblTrainingRecords. That's your surrogate key.
4) Leave the existing relationship between tblTrainingRecords and tblTrainedSOPData intact, but change the SOPID field in tblTrainedSOPData to Number instead of Autonumber.
5) Add a SOPID (Autonumber) field to tblSOPList and make that the PK, instead of SOPName.
6) Create a relationship between the new SOPID field in tblSOPList and the existing SOPID field in tblTrainedSOPData. Remove the SOPTrained field from tblTrainedSOPData. Again, you only need to store the SOPID, not the name.
7) Make SOPID and TrainingID a compund PK in tblTrainiedSOPData.
Final relationship model might look like;
Note: You may need to remove some existing data from your tables in order to make some of these changes, and you will need to make some adjustments (like record sources and/or Master/Child links) to your forms (or create new forms) when this is done.
