Search for duplicate ID with different users 1

[laina222]

I need to write a query to do the following:
My table contains 4 fields, a (1) Primary Key field (Access auto number),(2) ID, (3) sequence, and (4) username. What I need is for Access to pull the records where the ID and sequence are the same, but the username is different.

So for example:

PK ID Sequence Username
1 12345 1 John
2 67891 2 Bob
3 12345 1 Kevin
4 67891 3 John

So I'd want the query to pick up Records 1 and 3, because the ID and sequence are the same but the username is different.

Thanks for any help in advance!

 

[PHV]

SELECT * FROM myTable A INNER JOIN (
SELECT ID, Sequence FROM myTable GROUP BY ID, Sequence HAVING Max(Username)<>Min(Username)
) B ON A.ID=B.ID AND A.Sequence=B.Sequence

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244