[AccessD] OT: Solved problem, Query, Who didn't attend an in-service

Thanks Phil, it worked. Thanks Artur, Susan for your help.

Charles Hise


This E-mail is confidential, may be legally privileged, and is for the
intended recipient only. Access, disclosure, copying, distribution, or
reliance on any of it by anyone else is prohibited and may be a criminal
offence. Please delete if obtained in error and E-mail confirmation to the
sender.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Phil Jewett
Sent: Saturday, April 09, 2005 12:45 PM
To: accessd at databaseadvisors.com
Subject: RE: [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

 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com