Good afternoon. I have inherited some code that identifies duplicate records but, at the moment, we have to delete the unwanted lines manually. I'd like to modify this code so that it deletes duplicates, leaving the latest records in place.
Any ideas please?
Many thanks,
D€$
Code:
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.ORG, MPI.Date DESC;
Any ideas please?
Many thanks,
D€$