Let's assume you have tables like:
tblTechnicians: TechnicianID, Forename, LastName, ...;
tblSkills: SkillID, SkillDescription, ...;
tblTechicianSkillSet: SkillSetID, TechnicianID, SkillID;
tblTechnicianTrainings: TechnicianTrainingID, SkillSetID, TrainingDate, TrainingApproved (TrainingApproved may have e.g. values TRUE/FALSE, or 1/0).
Let's continue step-wise.
To get the list of all technicians:
Code:
SELECT tech.TechnicianID, tech.ForeName & " " & tech.LastName As FullName FROM tblTechnicians tech
To get requested skillsets for technicians:
Code:
SELECT tech.TechnicianID, tech.ForeName & " " & tech.LastName As FullName, tss.SkillID, ski.SkillDescription FROM (tblTechnicians tech INNER JOIN tblTechNicianSkillSet tss ON tss.TechnicianID = tech.TechnicianID) INNER JOIN tblSkills ski ON ski.SkillID = tss.SkillID
To get the results of trainings (To make it easier, I assumed that for every skill was a single training, or none. When non-approved trainings are repeated, the easiest way is to create a saved query which returns the result of latest training, and to use this query instead of trainings table.):
Code:
SELECT tech.TechnicianID, tech.ForeName & " " & tech.LastName As FullName, tss.SkillID, ski.SkillDescription, ttr.TrainingDate, ttr.TrainingApproved FROM ((tblTechnicians tech INNER JOIN tblTechNicianSkillSet tss ON tss.TechnicianID = tech.TechnicianID) INNER JOIN tblSkills ski ON ski.SkillID = tss.SkillID) LEFT JOIN tblTechnicianTrainings ttr ON ttr.SkillSetID = tss.SkillSetID
Adding the WHERE clause to final query allows to get:
With 'WHERE ttr.TrainingApproved = TRUE', or 'WHERE ttr.TrainingAproved = 1', or whatever you have for approved- a list of all approved trainings for all technicians;
With 'WHERE ttr.TrainingApproved = FALSE', or 'WHERE ttr.TrainingAproved = 0', or whatever you have for non-approved- a list of all non-approved trainings for all technicians;
With 'WHERE Nz(ttr.TrainingApproved, FALSE) = FALSE', or 'WHERE Nz(ttr.TrainingAproved,0) = 0', or whatever you have for non-approved- a list of all non-approved or missing trainings for all technicians;
With 'WHERE ttr.TrainingApproved Is Null' - a list of all missing trainings for all technicians.