Access 2007: Delete Duplicates

Microsoft Access 2000/visual basic f/app...
August 19, 2009 at 14:43:36
Specs: Windows Vista
I have a database with 100.000 entries.

If I run the Duplicates Query wizard, it shows 9000 duplicate entries based on First Name, Last Name and Address. I just want to delete all the duplicates entries.

Deleting them manually from the query is taking me hours.

I know how to write the deletion query in MySQL... but in Access SQL I'm not so sure.

Can someone help me?

It's just one table, and I'm searching on the fields: First Name, Last Name, Address.


See More: Access 2007: Delete Duplicates

Report •

August 21, 2009 at 08:19:29
A common method to delete duplicates with MSAccess is as follows:

Create a new Table with the same structure(FIELDS) as your current table.

Make the FirstName, LastName and Address fields a composite primary key. (One primary key made up of these three fields)

Create an append query, and copy your original table to the new table. Access will do the copy and will tell you there are 9000 records that can't be added because of key violations. These are the duplicates.

So you new table won't have the duplicate records.

And you still have the original data(all records) in your current table.

Report •

August 21, 2009 at 08:32:34
Mm. I tried that. The problem is there are some with no First Name or Last Name, and just a Company Name. This is problematic because primary keys fields cannot be blank.

I need a search and delete query.

Report •

August 21, 2009 at 10:08:10
Here's what I've come up with... I still need help though. The search conditions seem wrong and I don't know how to delete the record yet...

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM cTable") 'Iterate through me
    Set sm = db.OpenRecordset("SELECT * FROM cTable") 'Search me
    Do Until rs.EOF
        sm.FindFirst (sm![First Name] = rs![First Name] & sm![Last Name] = rs![Last Name] & sm!Address = rs!Address)
        While sm.NoMatch <> True  'If there was a match
        ' else just move on
            'MsgBox "Match Found. ID=" & sm![First Name]
            'Delete record
            sm.FindNext (sm![First Name] = rs![First Name] & sm![Last Name] = rs![Last Name] & sm!Address = rs!Address)
    Set db = Nothing
End Sub

Report •

Related Solutions

Ask Question