Query of excluded items

[Guest]

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

 

[Marshall Barton]

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

 

[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