Looking for Non-records...?

[Cornfused]

My goal with this question is to learn how to construct a select query that
will locate records of employees that have NOT attended a particular course.
Here's the information...

tbl_Employees: EE_ID(pk), LName, FName, etc...
tbl_Course_Archive: Course_ID(pk), Course_Code, Course_Name, etc...
tbl_EEID_CCode: EE_ID, Course_Code

The employee can attend more than one course and would therefore be listed
potentially more than once in the tbl_EEID_CCode.

I would like, through a parameter query, to have the user identify the
course name ("*" & [Enter Course Name] & "*") and the query return all of the
employee names that have NOT attended the user-identified course.

I know there is an easy answer, but I'm having difficulty wrapping my brain
around the problem.

 

[Jeff Boyce]

One way to approach this is backwards!

You can first get all the potential Employee X Course combinations, then get
all the actual Employee X Courses, then do an "outer" join that shows any
potential ExC not in actual ExC.

To get the first one, a query that includes both tables WITHOUT joins gives
all possible combinations. The second one you already have. The third one
joins the first two queries, as described above.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Best way I know to do this is to find all employees that have taken the
course and then use that and the employee table in an unmatched query.

UNTESTED SQL Statements Follow

Query One: Saved as qTaken
SELECT C.EEID
FROM tbl_EEID_CCode as C
INNER JOIN tbl_Course_Archive as CA
ON C.Course_Code = CA.Course_Code
WHERE CA.Course_Name = [Enter Course Name]

Query Two: Using the saved query and the employee table
SELECT E.EE_ID, LName, FName
FROM tbl_Employees as E LEFT JOIN qTaken
ON E.EE_ID = qTaken.EE_ID
WHERE qTaken.EE_ID is Null


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..