Query of excluded items

P

Paul

I am designing a database to track training. I have a query that tell me the
mandatory classes, and I have a table with the classes taken data (date
taken, expires, etc). What I need is a query that show me the classes taken
that are not mandatory.

Example:

Query 1: Mandatory Classes
Employee ID, Class #
1 1
1 2
1 3
1 4

Table 2: Classes Taken
Employee ID, Class #
1 1
1 2
1 8
1 9

Desired Query Results
Classes taken but not required
Employee ID, Class #
1 8
1 9

Thanks
 
M

Marshall Barton

Paul said:
I am designing a database to track training. I have a query that tell me the
mandatory classes, and I have a table with the classes taken data (date
taken, expires, etc). What I need is a query that show me the classes taken
that are not mandatory.

Example:

Query 1: Mandatory Classes
Employee ID, Class #
1 1
1 2
1 3
1 4

Table 2: Classes Taken
Employee ID, Class #
1 1
1 2
1 8
1 9

Desired Query Results
Classes taken but not required
Employee ID, Class #
1 8
1 9


I think the unmatched query wizard would produce something
like:

SELECT T.[Employee ID], T.[Class #]
FROM [Classes Taken] As T LEFT JOIN [Mandatory Classes] As M
ON T.[Employee ID] = M.[Employee ID]
WHERE M.[Class #] Is Null
 
M

Michel Walsh

SELECT a.employeeID, a.[Class#]
FROM table2 AS a LEFT JOIN table1 AS b
ON a.employeeID = b.employeeID
AND a.[class#] =b.[class#]
WHERE b.employeeID IS NULL



should do.


Hoping it may help,
Vanderghast, Access MVP
 

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