Comparing similar records in single table

[Guest]

I receive a table from another department that contains
employee ids and security roles. A sample would look
like this:

EmpID SecRole
1234 ABC
1234 CDE
1234 FGH
6789 ABC
6789 CDE
4321 FGH

After an audit, it was determined that some roles conflict
with each other. I need to identify those folks on an
ongoing basis.

I could redo the table into two tables and do a query that
shows every user who has roles ABC and FGH but I'm wondering
if a query can be done on the single table that I receive.

So far, I haven't been able to work it out. What I've tried
was creating a GroupBy query to give a list of unique IDs and
then another query (using the first one) to show users who have
a record with ABC and another record with FGH. I'm most likely
getting too complicated and the answer is much easier.

thanks,
gary

 

[Marshall Barton]

I receive a table from another department that contains
employee ids and security roles. A sample would look
like this:

EmpID SecRole
1234 ABC
1234 CDE
1234 FGH
6789 ABC
6789 CDE
4321 FGH

After an audit, it was determined that some roles conflict
with each other. I need to identify those folks on an
ongoing basis.

I could redo the table into two tables and do a query that
shows every user who has roles ABC and FGH but I'm wondering
if a query can be done on the single table that I receive.

So far, I haven't been able to work it out. What I've tried
was creating a GroupBy query to give a list of unique IDs and
then another query (using the first one) to show users who have
a record with ABC and another record with FGH.

Try something along these lines:

SELECT T.EmpID
FROM table AS T, table AS X
WHERE T.EmpID = X.EmpID
AND T.SecRole = "ABC"
AND X.SecRole = "FGH"