Unmatched Query From 3 Tables 1

[LowBrow]

This is getting a little too confusing for me, and I may be considering this from the wrong perspective.
I have three tables:

tblStaff- Table of Staff
StaffID
StaffFName
StaffLName

tblTrain- Table of Training Completed
TrainID
TrainTitleID
RequirementID
StaffID

tblTrainingTitle- Table of all Trainings Available
TrainTitleID
TrainingTitle
RequirementID

I want a query to tell all staff who have NOT completed a training with a RequirementID <3. I can write a query which tells me which trainings staff have taken no trainings, and I can write a query which tells me which trainings with a RequirementID <3 have been taken by no one, but I cannot seem to identify which trainings with a RequirementID <3 have not been taken by which staff.
Here is some additional info (if it helps)
Not all trainings have been offered (so the TrainingID may not exist in tblTrain).
Not all staff have had any training at all (so the StaffID does not exist in tblTrain).

My attempts have resulted in either JOIN errors (I assume because tblStaff and tblTrainingTitle share no keys, except in the tblTrain) or zero returned records (which would not be the case if I had the correct query written).

If you can help, it will be much appreciated!

 

[PHV]

Brute force method.
Create a cartesian product saved query named, say, qryAllStaffTrain:
SELECT StaffID, TrainTitleID
FROM tblStaff, tblTrainingTitle
WHERE RequirementID < 3;

And now your unmatched query:
SELECT A.StaffID, A.TrainTitleID
FROM qryAllStaffTrain A LEFT JOIN tblTrain B
ON A.StaffID=B.StaffID AND A.TrainTitleID=B.TrainTitleID
WHERE B.StaffID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LowBrow]

As far as I can tell, this is working like a dream! Thanks!

I need to do a more thorough review, but the number of records returned is about where I expected it, and there were no errors in the executing.

One problem I did note, which is not with the code, is that when I switched from the SQL window to the design window, the code became inoperative. But when I saved and executed without entering the design window, the code ran perfectly.

Thanks, again!