Remove duplicate records?

[Johan Myrberger]

How do I remove duplicate (identical) records in a table?

After having imported an Excel sheet into an Access table I have a
nukber of records that are identical. I'd like to remove every
duplicate, and only keep one instance of each. (In this case all fields
in the table are involved, and should be equal if it's a duplicate)

I have created a query that shows the number of duplicates for each
record through the "Find Duplicates Query Wizard", but how to I go from
here to actually remove them?

Regards
/Johan Myrberger

 

[John Ortt]

The easiest way is to copy the table (in structure form only) and then set a
primary key across all the fields which have the duplicate values.

Then create an append query and append all the records from the original
file into the new one.

This should result in only the first record being appended (you will have to
click run query anyway when the "can't append all records" message pops up).

Hope thats of help,

John

 

[Jeff Boyce]

As John points out, you can use your "import" table as a temporary holding
space, and put your "distinct" rows into another table. Also consider, if
you'll be doing this on a regular basis, setting up your permanent table
with the indexing John mentions. Then you'd just need to run an append
query from your temporary to your permanent table.

By the way, Excel data may not be well-normalized, so importing it directly
into Access may make your work (and Access') much harder. The append query
approach also gives you the opportunity to take poorly-normalized data (the
temporary "import" data) and distribute it to well-normalized tables via
multiple append queries.

 

[Johan Myrberger]

Thanks,

this method solved my problem.

I found an additional problem (the property "Allow Zero Length" was set
to No on all fields) that caused a number of entries (with some fields
blank) not to get imported. I am not sure if this occured when I set all
the fields as Primary Key, or if I had this problem before. Anyway -
everything is working as expected fro teh moment.

Regards
/Johan Myrberger

 

[Guest]

I have had luck by working with the data base in the table mode and sorting
on different fields....I cleaned up a fair 1600 record database that had 26
fields in it that I imported from Excel...it took a few hours but worked just
fine. Sort then delete duplicate records....sort make all fields correct
and sort again.....Good Luck