Looking to delete duplicate and leave just one unique record

[jms493]

I have and database of Social Security Numbers and the rest is name and addresses.

111-22-0090_ joe smith_ 12 monroe lane_ nyack ny 12345
111-22-0090_ joe smith_ 32 lake street_ monroe ny 12578

I want to delete the 1 of these and just leave one. Doesnt matter which one. Even if I there is 6 of them, IF the SSN is the same, I want them deleted from my table just leaving 1.

I want a table of just unique ssn numbers, names and addresses.

I tried the unique values set to 'yes'.
It helped but that looks at the entire row not just specific fields. ANy clue of what i can do???

 

[SydneyGeek]

Re: Looking to delete duplicate and leave just one unique re

You'll need 2 VBA routines; the first places the SSN in its own field (create the field first). The second marks and deletes the duplicates.
Make sure you make a backup of your table before running these routines!

Function MakeSSN()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim MyStr As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Conversion Errors")
With rst
    .MoveFirst
    Do Until .EOF
        .Edit
        MyStr = Left(![Error Description], InStr(1, ![Error Description], "_") - 1)
        ![SSN] = MyStr
        .Update
        .MoveNext
    Loop
End With
Set rst = Nothing
End Function

Function UniqueSSN()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim MyStr As String
Dim strSQL As String
Dim strSQL1 As String

'define query strings
Set dbs = CurrentDb()
strSQL = "SELECT [Conversion Errors].* " _
& "FROM [Conversion Errors] " _
& "ORDER BY [Conversion Errors].SSN;"

strSQL1 = "DELETE [Conversion Errors].* " _
& "FROM [Conversion Errors] " _
& "WHERE ([Conversion Errors].SSN = 'Dup');"

'mark duplicates
Set rst = dbs.OpenRecordset(strSQL)
With rst
    .MoveFirst
    Do Until .EOF
        MyStr = ![SSN]
        .MoveNext
        If ![SSN] = MyStr Then 'not unique
            .Edit
            ![SSN] = "Dup"
            .Update
        Else 'unique
            MyStr = ![SSN]
            .MoveNext
        End If
    Loop
End With
Set rst = Nothing

'delete duplicates
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL1)
DoCmd.SetWarnings True
End Function

Create a new module and paste these routines in. Change field and table names to suit. PLace your cursor in the first routine and press [F5]. Place your cursor in the second routine and press [F5].
That should do it.

Denis

 

[jms493]

Re: Looking to delete duplicate and leave just one unique re

Thats great. Thanks for the response.

I have never entered vb code in access b4 so i might need some more help and explanation.

But i will try.

If you have any more hints or tips for me please advise. ray:

 

[jms493]

Re: Looking to delete duplicate and leave just one unique re

If my database is named "db1" and my table is name "Customers". What else do i need??

 

[SydneyGeek]

Re: Looking to delete duplicate and leave just one unique re

Don't worry about the database name. Access will work on the current database (the one you have open).

The table name Customers should replace Conversion Errors
The field name that contains the SSN_Name_Address data should replace Error Description
Do this in both routines, keeping the quotes and brackets exactly as they were.
Also, create the SSN text field in the table before trying to run either routine, or you will get errors.

Denis