I have exact duplicate records in my table. I would like to remove one of the records so there are no more duplicates but the original record remains. What is the best way to get these quickly removed? thanks for any help!
Quick route, copy your existing table choosing Structure only.
Go to design mode and set one of your columns as the primary key (or indexed with no duplicates). This MUST be a column that is otherwise unique except in the duplicate record. [Serial number] is a potential candidate, for example, [Country] probably isn't.
Create a new query in design view and make it an append query to your new table.
Run the query. It will generate an error that x records could not be inserted because of key/index violations, this is fine, allow the query to run.
If you check your new table you should now have a de-duped table.
[edit]
Actually I've left my brain behind today. If your rows are duplicated exactly, i.e. there's no autonumber column
In SQL view:
Code:
SELECT DISTINCT *
FROM existingTable
INTO newTable
Other wise you can just SELECT DISTINCT on every column except your autonumber to remove duplicates
