Query to find what isn't there

[NISMOJim]

I thought this might be easy, but I can't quite wrap my head around it. Here's what I need to do...



I have a table (tblPermit) that lists all drivers. Each name is only entered once.

Another table (tblTraining) lists the different training courses an employee has been through. The name may be entered several different times, once for Course1, again for Course2, etc.

What I need to do is query for all names in tblPermit that have not received training for Course4.

If I query for all names in tblPermit that have had Course4 in tblTraining, that works fine. I had a quick thought that the criteria for the "Course" field could be "Not Like Course4", but that lists all of the training they have had except for Course4, whether they have been through Course4 or not, which doesn't tell me what I need to know.

Is there a way I can take the dataset of names that have been through Course4, then create a new dataset with those names removed from tblPermit? Not quite sure how to go about this the best way. Any advise is appreciated.

[ssanfu]

You have to use a query with a sub query or two queries.

I use two queries because it is easier for me.

It sounds like you have 3 tables:
tblPermit: table of driver names
tblCourses: table of courses

tblTraining: a junction table for Driver/ training courses

In my test dB, Course4 had a PK of 4.

1st query:

SELECT tblPermit.DriverID, tblPermit.DriverName, tblCourse.CourseID
FROM tblPermit INNER JOIN (tblCourse INNER JOIN tblTraining ON tblCourse.CourseID = tblTraining.TrainingID_FK) ON tblPermit.DriverID = tblTraining.DriverID_FK
WHERE (((tblCourse.CourseID)=4));

This query gets all drivers WITH Course4 training. I named this query "WithCourse"

2nd query:

SELECT tblPermit.DriverID, tblPermit.DriverName
FROM WithCourse RIGHT JOIN tblPermit ON WithCourse.[DriverID] = tblPermit.[DriverID]
WHERE (((WithCourse.DriverID) Is Null));

This is the drivers without Course4 training. I named this query "DriverWithoutCourse"


In the first query, if you reference a control on a form for the course, you can find drivers that don't have that course without editing the query.

[vincent-leeway]

Perfect approach, I was also thinking along your lines, just reversing the condition but as you mentioned it gives incorrect result. great work ssanfu.

[NISMOJim]

Mine is set up a bit different, but once I understood what you were doing, it all clicked. Pretty darned brilliant way of going about it. Working well, thank you very much!

[ssanfu]

You're welcome