MS Access query complexity on finding duplicates

Question

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]&[ST1.nam], first(ST1.id1) as id1, first(ST1.nam)  as nam, 
                   count(*) from Stars ST1
             group by [ST1.id1]&[ST1.nam])
       group by id1
       having count(id1)>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&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!

Answer 1

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(*) > 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 > 1
order by id1;

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