MS Access query complexity on finding duplicates I have a list of names and ID's, with many multiplication (it's a payroll list). I'd like to see those ID's and names where the name changed somewhen.
Input:
id1 name

1   Josephine Baker
2   Norma Jeane Morteson
2   Marilyn Monroe
33  Sandra Bullock
15  Issur Danielovitch
1   Freda Josephine McDonald
15  Kirk Douglas
28  Meg Ryan
28  Margaret Hyra
36  Vin Diesel
36  Mark Sinclair
8   Sean Connery
11  Mel Gibson
16  Julia Roberts
Desired output:
ID  Name

1   Freda Josephine McDonald
1   Josephine Baker
2   Marilyn Monroe
2   Norma Jeane Morteson
15  Issur Danielovitch
15  Kirk Douglas
28  Margaret Hyra
28  Meg Ryan
36  Mark Sinclair
36  Vin Diesel
I'm running this query. It works well, it is fast enough, I just have a feeling that this could be a lot more professional.
SELECT first(ST.id1) as ID , first(ST.nam) as Name
FROM Stars AS ST 
INNER JOIN 
       (select id1, first(nam), count(id1) from 
            (select [ST1.id1]&amp;[ST1.nam], first(ST1.id1) as id1, first(ST1.nam)  as nam, 
                   count(*) from Stars ST1
             group by [ST1.id1]&amp;[ST1.nam])
       group by id1
       having count(id1)&gt;1) AS SJ 
ON SJ.id1=ST.id1
GROUP BY ST.id1, ST.nam
ORDER BY ST.id1;

The logic behind is to select unique id&amp;name pairs, then select those id's that come up more than once, then (self)join the table and select the unique names to those id's.
Any advise is welcome!
You'll have to take this with a huge grain of salt, because the following advice is for generic, standards-abiding SQL - which Access is not.
In regular SQL, this is as simple as:
select dupes.id1, ST1.nam
from (
    select id1
    from ST1
    group by id1
    having count(*) &gt; 1
) dupes
join ST1 on ST1.id1 = dupes.ST1
order by dupes.id1;

Another way to do it without a join at all is a partition (window) function that adds a count column within each group. Unfortunately, the Access idea of a partition diverges significantly from the rest of reality, so this probably isn't possible for you. Regardless, for what it's worth, it would look like
select id1, nam
from (
    select id1, nam,
           count(*) over (partition by id1) as n
    from ST1
)
where n &gt; 1
order by id1;

If at all possible, consider moving away from Access. It introduces more problems than it solves.