Access 2010 Ranking duplicates in one field by date field - descending

[PWD]

Good afternoon, I've been searching and can't really find a solution. I have inherited a database and queries and procedures, one of which identifies duplicates in one field that requires me to keep the latest & delete other lines. So I thought that by creating some sort of ranking would help. At the moment I have the following SELECT statement that displays the duplicates and I have to manually delete the unwanted lines:

SELECT MPI.NHSNO, MPI.ORG, MPI.DOB, MPI.SEX, MPI.ETHNIC, MPI.VETERAN, MPI.GPCD, MPI.POSTCODE, MPI.Date
FROM MPI
WHERE (((MPI.NHSNO) In (SELECT [NHSNO] FROM [MPI] As Tmp GROUP BY [NHSNO],[ORG] HAVING Count(*)>1  And [ORG] = [MPI].[ORG])))
ORDER BY MPI.NHSNO,  MPI.Date DESC;

I had a thought that, at the very least, I would be able to sort the results by this ranking column and delete all those that weren't ranked as a 1.

Any assistance of how I could do this a lot smarter would be gratefully received.

Many thanks,
D€$

 

[PWD]

Well this seems to be a bit better:

SELECT MPI.NHSNO, MPI.ORG, MPI.DOB, MPI.SEX, MPI.ETHNIC, MPI.VETERAN, MPI.GPCD, MPI.POSTCODE, MPI.Date
FROM MPI
WHERE (((MPI.NHSNO) In (SELECT [NHSNO] FROM [MPI] As Tmp GROUP BY [NHSNO],[ORG] HAVING Count(*)>1  And [ORG] = [MPI].[ORG]))
And MPI.Date <> #30/06/2014#
)
ORDER BY MPI.NHSNO,  MPI.Date DESC;

At least I can delete all the duplicates that aren't this month's data.

Many thanks,
D€$

 

[PHV]

Something like this ?
DELETE FROM MPI WHERE NHSNO In (SELECT NHSNO FROM MPI WHERE [Date]<>#2014-06-30# GROUP BY NHSNO,ORG HAVING Count(*)>1)


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWD]

That sounds like it's worth a try, thanks!

Many thanks,
D€$