Problem deleting duplicates from a huge table!

[rob247]

Hi,

I have a table with about 15,000 records in it. I need to delete all but one of each duplicate social security records. I have tried solutions in other posts on this board such as...

A. Using the find duplicates query which completely ignores half of my records when done.

B. Index the table using the SSN column, which only gives me an error telling me why I can't do it.

C. Tried running code in a module with the run code command in a macro but cannot figure out the errors that are supposedly in the code.

D. I have also tried breaking the table down into 3 smaller 5,000 record tables with little to no success.

Any help would be much appreciated, Thanks!

Thanks,
Robbie

 

[dgm]

The find duplicates query should work if your only searching for duplicates in the SSN field.

How do u know which record to delete? Are all duplicate records the same for each SSN?

 

[EMP]

If you want to keep just one record for each SSN, the following can help you keep the last record for each SSN:-

Create a Totals query (in query design, click on the Totals button on the toolbar). Choose Group By for field SSN, Last for each of the other fields.

Change the Totals query to a Make-Table query (in query design, choose menu Query, Make-Table Query...) Run the query to create a new table.

Delete the original table.

 

[schumarg]

Delete dups

Open Table, then menu Tools > Office Links > Analyze it with MS Excel. Create a macro in Excel and paste the following code, go back to your worksheet and highlight the SSN data column (only the data, not the whole column or the header) and run the macro. You may have to run the macro a few times, it will let you know when theres no more duplicates to delete. Then simply copy paste the data back to the table. ps: you should make backups before trying this.

Sub DelDups()

' Deletes duplicate rows in the selected range.
' All columns in the selected range must be identical for
' a row to be deleted. The entire row, not just the selected
' cells in the row, will be deleted if a duplicate is found.
' The first instance of the duplicate row is the copy that
' will be retained.

Dim iRow As Long
Dim jRow As Long
Dim iCol As Integer
Dim LastRow As Long 'The last row in the selected range
Dim FirstRow As Long 'The first row in the selected range
Dim FirstCol As Integer
Dim LastCol As Integer
Dim DelCount As Long 'The count of duplicate rows removed
Dim DupFound As Boolean 'True if duplicate row found

DelCount = 0

FirstRow = Selection.Row
LastRow = FirstRow + Selection.Rows.Count - 1
FirstCol = Selection.Column
LastCol = FirstCol + Selection.Columns.Count - 1

For iRow = FirstRow To LastRow - 1

For jRow = iRow + 1 To LastRow

DupFound = True
For iCol = FirstCol To LastCol
DupFound = DupFound And (Cells(jRow, iCol) = Cells(iRow, iCol))
If Not DupFound Then Exit For
Next iCol
If DupFound Then
' Duplicate row found--delete it
Rows(jRow).Delete
LastRow = LastRow - 1
DelCount = DelCount + 1
End If

Next jRow

Next iRow

Beep
MsgBox DelCount & " duplicate rows deleted.", _
vbInformation, "Duplicate Removal Results"

End Sub


enjoy.........

 

[Pat Hartman]

No coding is required

1. Copy the structure (not data) to a new table.
2. Change the new empty table to make SSN unique.
3. Create an append query that selects all the rows and appends them to the new table. You will get an error message telling you that x rows were not appended due to key violations. Just click ok.

 

[rob247]

MANY THANKS!

Thanks Guys,

All of your ideas were helpful and I tried them all. The one that worked for me was Pat Hartman's idea.

Thanks Again,

Robbie