how do I create a Delete Duplicates Query in Access

[Howard Maidman]

I want to be able to delete the second or multiple instance of duplicate
records in Access 2007. The query wizard for duplicate records returns all
instances ....GREAT! Oh I have 4500 records to trawl so a manual is out of
the question as approx 3300 of these are duplicated....thanks to the settings
on my pda synch but that's another story!

 

[Ken Sheridan]

Firstly make sure the table has a primary key column with unique values, e.g.
an autonumber (AddressID in the example below). You can then delete all but
those with the MIN value for each set of duplicate rows by using subquery in
the WHERE clause. The following simple example deletes all but one of each
set of rows with the same LastName values:

DELETE *
FROM Addresses AS T1
WHERE AddressID <>
(SELECT MIN(AddressID)
FROM Addresses AS T2
WHERE T2.LastName = T1.LastName);

Note how the table is given aliases T1 and T2 to distinguish the two
instances of the table and correlate the subquery with the outer query.

You might need to correlate on more than one column of course, e.g. WHERE
T2.LastName = T1.LastName AND T2.FirstName = T1.FirstName AND T2.Email =
T1.Email, which would not treat two people with the same names as one,
assuming they have different email addresses. I once worked with two Maggie
Taylors, so it’s a real possibility!

It goes without saying that the table should be backed up first.

Ken Sheridan
Stafford, England