Deleting Duplicates

[Brian Keanie]

Used the "find duplicates" wizard to identify approx 500 duplicates in a
single table. How do you delete the duplicates without doing it one at a
time?

[ByteMyzer]

"Brian Keanie" <bk******@sympatico.ca> wrote in message
news:fq*******************@news20.bellglobal.com.. .

Used the "find duplicates" wizard to identify approx 500 duplicates in a
single table. How do you delete the duplicates without doing it one at a
time?

Does the table in question have a primary key? If it does, you can use a
delete query like the one in the following example scenario,
A table named MyTable, with a primary key field named MyKey, and duplicate
values in fields MyField1 and MyField2:

DELETE T1.*
FROM MyTable T1
WHERE T1.MyKey<
(SELECT Max(T2.MyKey)
FROM MyTable T2
WHERE T2.MyField1=T1.MyField1
AND T2.MyField2=T1.MyField2;);

If the table in question does NOT have a primary key, however, it becomes
necessary to employ a Visual Basic Sub with QueryDef and Recordset objects
to perform the task. Take the above scenario, without a primary key. The
Visual Basic Sub might look something like this:

' ----------<< begin code >>----------
Public Sub DeleteDuplicates()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT T1.* " _
& "FROM MyTable T1 " _
& "WHERE EXISTS " _
& " (SELECT T2.MyField1, T2.MyField2 " _
& " FROM MyTable T2 " _
& " WHERE T2.Myfield1=T1.Myfield1 " _
& " AND T2.Myfield2=T1.Myfield2 " _
& " GROUP BY T2.MyField1, T2.MyField2 " _
& " HAVING COUNT(T2.*)>1; " _
& " )" _
& ";")

Do While Not rs.EOF
rs.DELETE
rs.Requery
Loop

rs.Close
Set rs = Nothing

End Sub

' -----------<< end code >>-----------