Deleting Duplicate Records in a single table

[Mike P]

I have a table with duplicate records in it. This happens during an External
Data file import from an Excel Worksheet, sometimes the user imports the same
worksheet twice. The table does NOT have an Autonumber ID as a field. The
table is indexed with duplicate fields allowed. How do I write a Query to
delete multiple records?

The two Fields which identify if the data is duplicate are:
Symbol, EventDate

 

[Jeanette Cunningham]

Mike,
You can create a new blank table which is a copy of the table structure of
the original table.
Set up a unique index for the combination of Symbol and EventDate on this
table.
Append all the data from the original table to the copy.
Access will give you the message that it can't append all the data. (the
records it can't append will be the duplicates).
Accept this and click yes.
You should end up with the records minus duplicates in the copy.

Now rename the original table in a way so you know it is out of date.
Rename the copy to the original table name.
Recreate any relationships if necessary.
You should have a working table that will not allow duplicate records.


Jeanette Cunningham