Unmatched query between three tables

[Craig]

Hello,

I have an MS Access 2003 database that contains employee training records as
follows:

tblEmployees (empID, name)
tblTrainingCourses (courseID, courseName)
tblTrainingIncidents (courseID, empID, dateCompleted)

One employee can take many courses, and one course could be taken by the
same employee many times (but most likely on different dates).

What I want to do now is create a query that will show the employees that
have NOT taken a particular course.

I have looked at the Unmatched Query Wizard in Access 2003 but it only seems
to be designed for a two-table relationship.

At this point I'm not sure where to begin. Can someone help me to get
started? Any help is greatly appreciated!

Thanks,
Craig

 

[Jeff Boyce]

Craig

If you first created a "Cartesian Product" query between tblEmployee and
tblTrainingCourse, you'd get all possible training courses for all
employees. Then, using that query, you could create the unmatched query
between tblTrainingIncident and the Cartesian Product.

That should show you all Employee X Course that are NOT in the
tblTrainingIncidents (this seems to be what you're after, who hasn't taken
which course(s)).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

First, a query to identify everyone that HAS taken the course.

IF it is a small number of course and employees you can use

SELECT tblEmployees.*
FROM tblEmployees
WHERE tblEmployees.EmpID NOT IN
(SELECT EmpID FROM tblTrainingIncidents WHERE CourseID = 22 and EMPID is
not null)

If that is too slow then post back for some other suggestions or perhaps you
can follow Jeff Boyce's idea for a solution.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County