Unmatched query between three tables


C

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
 
Ad

Advertisements

J

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
 
Ad

Advertisements

J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top