Deleting duplicate items

[antvon]

Hi

I wish to delete duplicate items from a table using
a delete query but only to delete one of the dups
Preferably a single query

Example
101
101
102
102

run delete query

101
102

Thanks in advance

 

[psemianonymous]

0. Back up your data, I don't want to feel responsible.

1. Make a GROUP BY query which will get one instance of each duplicate. Basically, if you're grouping by NAME and DOB, for instance, you will see one record per "group" of NAME/DOB. Include the unique Autonumber field in this query and put "First" or "Min" or "Max" for this ID--whichever you decide.
Now you have a list of items you would like to keep.

2. Make a delete query which says "DELETE * FROM tableNAME WHERE NOT (ID IN (qryYOURGROUPBYQUERY))"
This will (hopefully) delete everything in the table that is not in the list of items that you "wish to keep"


If #2 doesn't work directly, maybe someone else can craft a DELETE query better than I can--I find them difficult to work with - with all the restrictions.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[antvon]

Hi foolio12

Sorry for the late reply
Suddenly got swamp with two crisis

Still not out of the dog box yet but will get around
to trying out your suggestion.I can't see any reason
why it should not work though.

I hate to show my ignorance but what is DOB in your
NAME/DOB reference?

 

[psemianonymous]

DOB -> date of birth -> birthday

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm