Deleting Duplicates

[Guest]

I have a HUGE database that I have to weed all the duplicates out of before I
can upload it into an online database.

I have run a Find Duplicates Query, and it returned over 42,000 results. Is
there a quicka nd easy way ot delete these duplicates?

 

[Guest]

It can be done with a DELETE query such as the following where you want to
retain one instance of each LastName, and MyID is a uniquely valued field
such as an autonumber:

DELETE *
FROM
MyTable AS T1
WHERE MyID >
(SELECT MIN(MyID)
FROM MyTable As T2
WHERE T2.LastName = T1.LastName);

Or a simple way is to copy the structure of the table to a new empty table,
and create a unique index on the field or fields in the table which determine
what rows are duplicated. If you then append the rows from the original
table to the new empty table with an append query only one instance of each
duplicated row will be appended due to the index violations. You can then
delete the original table (having backed it up of course!) and rename the new
one.

Ken Sheridan
Stafford, England