Query for missing values from Training database

[madwoman]

Hi

I have inherited a database that contains details of staff training data and the tables contain the following:

Personal Information Table:
PersonalID
Surname
Forename
EmploymentStatus (this contains either Staff, Operative, or Supervisor)

Training Courses Table:
CourseID
Course Name
Course Description
Supervisor (Yes/No)
Operative (Yes/No)
Staff (Yes/No)

Training Courses Attended Table:
RecordID
PersonalID
CourseID
Date

Each of the courses in the Training Courses Table should be attended by one or more of the groups identified in the EmploymentStatus field (ie. Supervisor, Operative, Staff) and the relevant field in the Training Courses Table is flagged eg.
Training Courses Table:
CourseID, CourseName, Supervisor, Operative, Staff
100, basic safety, Yes, Yes, Yes
101, safety management, Yes, No, No
102, working with ladders, No, Yes, No
103, VDU, No, Yes, Yes

I need to identify which individuals have not attended the courses that they should have been completed (ie. compare courses attended with the list of courses associated with the EmploymentStatus associated with individual staff members, and identify which courses have no attendance dates).
How can I structure the query, I can't see how to do this with the existing tables, but I think it should be possible, but my Access expertise is just not good enough to work through this.

Any advice would be much appreciated.

 

[FoFa]

This is just one way to approach it, by breaking the queries up.
First query gives you the people, and the course they need to take.
Call this Query1:
SELECT PIT.PersonID, TCT.CourseID
FROM [Personal Information Table] as PIT
INNER JOIN [Training Course Table] as TCT ON Staff = True
WHERE EmploymentStatus = "Staff"
UNION
SELECT PIT.PersonID, TCT.CourseID
FROM [Personal Information Table] as PIT
INNER JOIN [Training Course Table] as TCT ON Operative = True
WHERE EmploymentStatus = "Operative"
UNION
SELECT PIT.PersonID, TCT.CourseID
FROM [Personal Information Table] as PIT
INNER JOIN [Training Course Table] as TCT ON Supervisor = True
WHERE EmploymentStatus = "Supervisor"

This second query then bounces it up against the actual courses taken, and set a new column to yes or no if it was taken (well is in the table at least).
Call This Query2:
SELECT Q1.PersonID, Q1.CourseID, IIF(ISNULL(TCA.RecordID),"NO","YES") as Taken
FROM Query1 as Q1
LEFT JOIN [Training Courses Taken] as TCA on Q1.CourseID = TCA.CourseID

Now you can write one or more queries against Query2 and join the first two tables and pull whatever information you need at that point.
I have not tried this approach, (since the union queries are truly not joined) but it is worth a shot.