Removing duplicate records...

[puzzle]

Hi

I know a few of you will have answered this question before as I've already had a search through the forums. Basically, I have been given a rather large database (about 750,000 records) in MS Access format.

The records are all fairly standard name, address and telephone number data. I need to remove any duplicate records that are an exact match with any other record in the database if all the fields in both records are duplicated exactly. If one record is identical to another record apart from, say, a telephone number is entered in one record but not the other then both records have to remain.

At the minute there are no primary keys, and a lot of the fields contain null values.

I have tried using the append query but I think I must be doing something wrong as I end up with drastically too few records.

Any ideas?

Cheers,

Puzzle

 

[[nateobot]]

I just had to do something similar to this. I created a new table that had the same field names, field structure etc...

I added the primary key on the fields that had to be unique. Then I ran an append query from the old table to the new one. Worked like a charm.

Don't know if this is what you tried already or not.

 

[fernin8r]

i would run some code.

create a module or just use a form.

Private Sub Remove()

Dim rst As Recordset
Dim dbs As Database
Dim varX, varY, varZ

'Enter the table name in place of Table1, but don't change CurrentDb

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Table1", dbOpenDynaset)

With rst

.FindFirst

Do Until .EOF
varX = !Name
varY = !Address
varZ = !Phone_Number

.FindFirst "Name = '" & varX & "' And Address = '" & varY & "' And Phone_Number = '" & varZ & "'"
Do Until .NoMatch
If (.NoMatch = False) Then
.Delete
End If
.FindNext "Name = '" & varX & "' And Address = '" & varY & "' And Phone_Number = '" & varZ & "'"
Loop
.FindNext
Loop

End With
rst.Close
Set dbs = Nothing

End Sub

 

[fernin8r]

Name
Address
Phone Number

are the field names in the Table1