| | |
|
|
|
Telling a Query to pick only one entry out of multiple duplicates |
|---|
| message from =?Utf-8?B?Q2luZHkgSw==?= on 18 May 2004 |
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 #?
|
| John Vinson replied to =?Utf-8?B?Q2luZHkgSw==?= on 18 May 2004 |
A Criterion on the ID field of
=DMax("[ID #]", "[tablename]", <criteria to identify the group>)
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
| Pavel Romashkin replied to =?Utf-8?B?Q2luZHkgSw==?= on 18 May 2004 |
I think you will need to create and save a query first that will find
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
Cindy K wrote:
|
|
Archived message: Telling a Query to pick only one entry out of multiple duplicates (MS Access Error Message)
