Help: Need to delete duplicates

[smduello]

I have an access table that has over 470,000 rows. I need to delete
the duplicates. None of the "append" query ideas will work as it
involves copying and pasing the structure, and Access won't allow me
to copy 470,000 rows. Does anyone have a suggestion on how to
accomplish this?

 

[Jerry Whittle]

What error do you get when you run the append query? It could be that the
file size is nearing the 2 GB limit. First make a backup copy of the database
then run a compact and repair. Then try the append query ideas.

 

[John W. Vinson]

I have an access table that has over 470,000 rows. I need to delete
the duplicates. None of the "append" query ideas will work as it
involves copying and pasing the structure, and Access won't allow me
to copy 470,000 rows. Does anyone have a suggestion on how to
accomplish this?

Nobody would suggest copying and pasting ten rows, much less 470000.

Copy and paste the *STRUCTURE* of the table - not the table. Select the table
in the tables window; type Ctrl-C to copy and Ctrl-V to paste. You'll get a
popup window offering three choices - Structure Only, Structure and Data, and
Add records to existing table. Check Structure Only.

You'll then be asked for a new table name. Open this new table, and set a
unique Index on the combination of fields which identify a duplicate.

Compact the database at this point to be sure it's starting clean.

Then create a new Query based on the original table. Change it to an Append
query and select the new table. Run the query using the ! icon; you'll get a
warning message "25236 records were not appended due to key violations" -
that's the duplicates you're trying to get rid of.


John W. Vinson [MVP]

 

[Ken Sheridan]

Provided the table has a column (or columns) with unique values, e.g. an
autonumber, it can be done with a DELETE query. Say the duplicates are
identified by LastName and FirstName columns (unrealistic of course as names
can legitimately be duplicated) and the unique values are in a column YourID:

DELETE *
FROM YourTable As T1
WHERE EXISTS
(SELECT *
FROM YourTable As T2
WHERE T2.LastName = T1.LastName
AND T2.FirstName = T1.FirstName)
AND YourID >
(SELECT MIN(YourID)
FROM YourTable As T3
WHERE T3.LastName = T1.LastName
AND T3.FirstName = T1.FirstName);

Ken Sheridan
Stafford, England