How design query to delete duplicate records in a table

Well, the problem with this is what is in the other records?  How do you determine which record to keep and which to delete within the duplicates?  If you have a Date field, or an autonumber ID field, you could elect to use a rule that lets you delete all but the first or earliest, or last/most recent date.  Generally, I just run a Duplicates query using the query wizard, where I identify the fields I want to use to define the duplicates.  Then I select to display all of the other fields as well, so that I can examine each record to determine which record to keep and which to delete.  This also gives me the ability to create a composite record from the data in multiple records, before deleting, if I need to.

Try this way in a command button click event:
This example assumes table: b(f1, f2).
It loops through the table's record set and comparing current record with previous one, and deletes if it is a duplicate.

Private Sub Command4_Click()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM b ORDER BY f1, f2")
    Dim sf1 As Variant, sf2 As Variant ' hold the previous record fields values to compare with
    
    rs.MoveFirst
    sf1 = rs("f1")
    sf2 = rs("f2")
    rs.MoveNext
    Do While Not rs.EOF
        Debug.Print rs("f1"), rs("f2")
        If rs("f1") = sf1 And rs("f2") = sf2 Then
            rs.Delete
        Else
            sf1 = rs("f1")
            sf2 = rs("f2")

        End If
            rs.MoveNext
    Loop
End Sub

Select allOpen in new window

Do I need to replace any part of the supplied code with my real table name and field names?

Just keep in mind that both of those two latter techniques are "dumb" techniques, which work great if you are not concerned with any of the data in the other fields of the table.

I don't mean they are "dumb" as in "I wouldn't use them", I just mean that you have no control over the data in any of the other fields, so you may keep the one record with nothing in any other field, while several of the other records (deleted) have values in those fields.

Dale,
The method I suggested included a suggestion to use a sort to control which of the "duplicates" gets added so it is not totally "dumb".

agreed.