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

I may be missing something, but so far I don't see the issue here. 
tblCourses, I assume, contains the range of courses available. 
tblEmployee contains the employees. tblEmployeeTraining contains a list 
of atttendees to various courses. Excellent design.

I changed the table names slightly to fit into my example database, but 
you should be able to transpose them quickly. For the employees who have 
atteneded any given course:

SELECT     LastName
FROM         dbo.Employees
WHERE     (EmployeeID NOT IN
                          (SELECT     EmployeeID
                            FROM          CoursesAttended
                            WHERE      CoursesTaughtID = 1))

This assumes, of course, that you know the CoursesTaughtID. In the above 
I tested it with CoursesTaughtID = 1. Substitute the courseID of your 
choice.

Arthur


Charles Hise wrote:

>Sorry, for the confusing statement, I have the following tables:
>Tbl 1			Tbl 2				Tbl 3
>tblEmployee	tblEmployeeTraining	tblCoursesTaught
>empId		empId				date
>Name,Etc.	id				coursetitle
>Status	tblCoursesTaught_ID	id
>
>Status filters for Current and onLeave employees only
>Hope this helps.
>
>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 Susan Harkins
>Sent: Saturday, April 09, 2005 11:59 AM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Query, Who didn't attend and in-service
>
>Have you tried
>
>WHERE tblCoursesTaught.ID=10 AND tblEmployee.status=2)
>
>I don't understand why you're including the 1 value with an Or operator --
>active seems to suggest that they attended?
>
>Susan H. 
>
>Susan
>10 is the courseID and status 1 active 2 on leave 3 terminated 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 Susan Harkins
>Sent: Saturday, April 09, 2005 11:49 AM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Query, Who didn't attend and in-service
>
>What does the id value 10 signify? Also, what do the status values of 1 and
>2 represent?
>
>Susan H. 
>
>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
>
> 
>
> 
>
>
>
>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.
>
> 
>
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005