Telling a Query to pick only one entry out of multiple duplicates

C

Cindy K

I ran a querie to pick out duplicates in a table. I want to change this to a query so that it deletes and keeps only one entry. I do care which it keeps.

Is there a way to put an expression in to say choose the highest "ID #" then I could run it, delete those, then run it a couple more times to weed these out. What would a simple expression be to ask it to pick the Highest ID #?
 
P

Pavel Romashkin

I think you will need to create and save a query first that will find
the highest ID, then join it with the table and delete the remaining rows.
A simple way of doing it would be:

Make SavedQry, grouping by all fields except ID:
SELECT Max(ID) FROM MyTable GROUP BY Field1, Field2, etc ... HAVING
COUNT(ID) > 1

DELETE ID FROM MyTable LEFT OUTER JOIN SavedQry ON MyTable.ID =
SavedQry.ID WHERE IsNull(SavedQry.ID)
My concern is whether this will run with this join. If not,
DELETE ID FROM MyTable WHERE MyTable.ID NOT IN SavedQuery
should do it.
You only need to run it once to get rid of all duplicates. Please make
sure that these queries display the right records before you actually
delete, because it is all air code and I could have gotten it backwards :)
Pavel
 
J

John Vinson

What would a simple expression be to ask it to pick the Highest ID #?
Click to expand...

A Criterion on the ID field of

=DMax("[ID #]", "[tablename]", <criteria to identify the group>)
 
Top