I have a "database" in Access that was sent to me by a
client. The database consists on only one table. In
that table, I have duplicate data as determined by the
first column of information.
What is the easiest way to de-dup the table so that each
record is unique in the first column?
A "duplicate record", so far as you're concerned, is one that has a
duplicate value in that one column? Here's a quick and dirty way:
1. Copy and paste the *structure only* of the table to a new table --
call it, maybe, "Dedup".
2. Open table Dedup in design view, select the field that should have no
duplicates, and change its Indexed property to "Yes (no duplicates)".
Then save and close the table.
3. Create and execute an append query that copies all records from the
original table to table Dedup. When you run it, you will get a message
warning you about records that can't be appended due to key violations,
and asking if you want to ignore the errors and run the query anyway.
Click the Yes button.
4. After verifying by inspection that table Dedup contains the records
you want, rename or delete the original table, then rename table Dedup
to the former name of that table.
5. If you need to, you can now remove the unique index you created on
the field you were concerned with. However, if there aren't ever
supposed to be duplicate values in this field, you may as well leave the
index.
