Delete duplicate records

[Dimitris]

Hello.
I ran the duplicate wizard in the Querie section and I found a lot of
duplicate records. Records that may be entered 2,3 or more times.

I want just to keep one unique record in the table and delete all the other
duplicate records. Can someone please tell me how to do that.

Please note that I am a newbie so detailed instructions would be very
appreciated.

Thank you.
Dimitris

 

[Allen Browne]

Use a subquery to identify the primary key value of the 'first' record, and
delete the others.

This example assumes a table named Table1, with a primary key named ID, and
'duplicate' means records where SomeField and AnotherField are the same:

DELETE FROM Table1
WHERE ID <> (SELECT Min(ID) AS MinOfID
FROM Table1 AS Dupe
WHERE Dupe.SomeField = Table1.SomeField
AND Dupe.AnotherField = Table1.AnotherField);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Dimitris]

Thank you for your answer Allen.

The problem is that the table does not have a primary key. The field which
I search for duplicate records is the field "TELEPHONE". But no primary key
for any field. And there are about 1 million records in the table and a
primary key is not wanted in this particular database.

So what can I do now?

 

[Allen Browne]

Without a primary key, you are stuck. That's the nature of relational
databases.

The only thing you could do is create a Totals query.
Group By the TELEPHONE field.
Choose First under other fields.
Turn it into a Make Table query.
Run. It yields a table with de-duplicated table, but the records may not be
as you expect.

A better solution would be to import the records into another table. Add a
primary key. De-duplicate. Then export the results.