Deleting duplicates query....

[Dale]

Hello...

I know almost nothing about MS Access. So please bare with me.....

I am using Access 2000. I want to know how to write a query that will
search for records that are duplicates, then search for the records that has
the most data in it, and delete the record that has less data than the other
one.

I needed this for a contact manager than I use for faxing. I don't want to
send 2 (or more) faxes to the same fax number.

Note: All the data comes from the same database, from the same table.

If you can help, please explain the syntex of the query so I can learn how
to do it.

If it is too complicated, I can e-mail a sample of the data to anyone that
can help.

Thanks in advance...
Dale M. Allen
(e-mail address removed)
www.daleallen.com

 

[Allen Browne]

It's actually easier to create a de-duplicated table than to remove the
duplicates. It's also safer: you get to check the results before you delete
anything.

1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the fields into the grid.
In the Total row, accept Group By under all fields that define "duplicate"
for you. Under the other fields, if you don't care what you get, choose
"First" in the Total row.

4. Change the query to a Make Table query (Make Table on Query menu).

5. Run the query.

Test the results, and when you are happy, you can delete your old table.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), there is a small Access sample
database called: "RemoveDuplicates.mdb" which illustrates one way to do
this. NOTE: always back up your table before attempting to delete
duplicates. Deletions in Access are rather permanent.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L