Getting rid of duplicate entries

[baxwink]

It's been a while since I have done any programming with Access and I am stumped about how to get rid of duplicate entries in a table. I have a table that someone has imported data into each month and I've just realized that rather than append the table, they have continued to add the same data over and over. All data is exact. I need to know how to do a query or something that will search my table and remove all entries that are the same except one.
Thanks.

baxwink@aol.com

 

[hellferret]

This may not be the best way to do it, but you can do it by writing a query that returns all fields and groups by all fields.

ie:

SELECT ID, Name, Address, Account FROM tblTable GROUP BY ID, Name, Address, Account;


This will create a recordset identical to your existing one but without duplicates.

 

[ghubbell]

A pretty cool method is to copy your table and paste it back "Structure only". In this new table find your field(s) that you don't want dup's in and set it(them) indexed property "Yes(no duplicates)". Now by means of an append query or copy/paste, pour your original table into the new one. Access will automatically reject any duplicates. You can study your results and if you're happy, rename your old table, rename your new table, "rehook" your relationships and you're done! Gord
ghubbell@total.net