Find 2 like values 1

[jadams0173]

Hello all!

I have a table like this. Both are text values.

Class EmpID
1000 1234
1000 4567
1017 1234
3360 7890
3360 9876
3361 7890

I need to return the EMPID and Class for all EmpID's who have had 1000 but never have 1017 and who have had 3360 but never taken 3361. Is this possible to do in one query?

The above example would return
Class EmpID
1000 4567
3360 9876

Many thanks in advace. I'm currently doing this but using 2 queries and recordsets.

 

[lespaul]

typed, untested:

SELECT Class, EmpID FROM TableName
WHERE Class = '1000' AND EmpID NOT IN (SELECT EmpID FROM TableName WHERE Class = '1017')
UNION
SELECT Class, EmpID FROM TableName
WHERE Class = '3360' AND EmpID NOT IN (SELECT EmpID FROM TableName WHERE Class = '3361')

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[jadams0173]

Thank you Leslie