Finding Missing records

[Drew]

I have a staff training access database that I need to write a query for...
here are the details, (these were not built by me, I just have to fix them)

StaffCoreTable
StaffID - Unique ID for staff
StaffFName - First Name
StaffLName - Last Name
Other fields are present, just not relevant now

ClassesTaughtTable
ClassTitle - Title of Class
ClassNo - Unique class number
StartDate - Date of start
EndDate - Ending date
Comments - Comments regarding class

TrainingTable
StaffID - ID from StaffCoreTable
ClassNo - Class # from ClassesTaughtTable
Score - score of class

How can I find out which employees have NOT had a certain class?

Thanks,
Drew Laing

 

[Joseph Meehan]

I have a staff training access database that I need to write a query
for... here are the details, (these were not built by me, I just have
to fix them)
StaffCoreTable
StaffID - Unique ID for staff
StaffFName - First Name
StaffLName - Last Name
Other fields are present, just not relevant now

ClassesTaughtTable
ClassTitle - Title of Class
ClassNo - Unique class number
StartDate - Date of start
EndDate - Ending date
Comments - Comments regarding class

TrainingTable
StaffID - ID from StaffCoreTable
ClassNo - Class # from ClassesTaughtTable
Score - score of class

How can I find out which employees have NOT had a certain class?

Thanks,
Drew Laing

Set up a query with the TrainingTable and StaffCoreTable. You want to
show all from the TrainingTable. Sort on Score (assuming anyone who had a
class will have an entry and the blanks did not have the class.

 

[Drew]

The assumption is incorrect. Since the staff does not have the class, they
were never entered, therefore that query will not work.

In otherwords, for class 40, there are only 5 records, so in other words I
would need to return the 138 other people in the database.

Thanks,
Drew

 

[Douglas J. Steele]

What you want is LEFT JOIN between StaffCoreTable and the other two tables,
and only return those rows in StaffCodeTable where the resultant row in
TrainingTable is null.

 

[Joseph Meehan]

The assumption is incorrect. Since the staff does not have the
class, they were never entered, therefore that query will not work.

In otherwords, for class 40, there are only 5 records, so in other
words I would need to return the 138 other people in the database.

Sorry, you are misunderstanding my suggestion. That is where the query
comes it. It will return the 138 people assuming they were in the Staff
table to begin with.