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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 #?
 
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
 
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>)
 
Back
Top