[AccessD] Query, Who didn't attend an in-service

This is solved with a subquery to exclude those that DID attend course
10:
 
SELECT tblEmployee.empID from tblEmployee
 
WHERE (tblEmployee.status = 1 or tblEmployee.status = 2)
 
AND tblEmployeeID NOT IN
 
(SELECT tblEmployee.empID
FROM tblEmployee LEFT JOIN (tblCoursesTaught RIGHT JOIN tblEmplTraining
ON tblCoursesTaught.ID = tblEmplTraining.tblCoursesTaught_ID) ON
tblEmployee.empID = tblEmplTraining.empID
WHERE tblCoursesTaught.ID = 10) ;
 
Almost always when you are working on a query that includes the dreaded
words 'and did not...' then you need a subquery to solve it.
 
Phil Jewett
Phil Jewett Consulting
pjewett at bayplace.com
(619 318-4899
 
Original message:
 
Access 03. I am trying to develop a query to find distinct employees who
did not attend an in-service. The following gives me the employees who
did attend;

SELECT DISTINCTROW tblEmployee.empID

FROM tblEmployee LEFT JOIN (tblCoursesTaught RIGHT JOIN tblEmplTraining
ON tblCoursesTaught.ID = tblEmplTraining.tblCoursesTaught_ID) ON
tblEmployee.empID = tblEmplTraining.empID

WHERE (((tblCoursesTaught.ID)=10) AND ((tblEmployee.status)=1)) OR

(((tblCoursesTaught.ID)=10) AND ((tblEmployee.status)=2));

If I use <>10 for tblCoursesTaught.ID, I get the same employees who
attended other in-services.

I have been working on this for weeks, research, etc. and am lost.

I would appreciate any help

Charles Hise