If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
duplicate records
Hi,
I have a table containing about 3500 records All have a field named "volgnummer" Records with the same "volgnummer" should be deleted except for one of course How can this be done? Thanks |
#2
|
|||
|
|||
duplicate records
One way to handle this would be to build a new table with a compound unique
index based on the volgnummer field. Then import all the records into this new table and ignore the errors. Once the import is successful, delete the old table and rename the new table to the old table's name. Another way, (BACK UP your data before you do this) Build a query based on the table that will identify the primary key values you want to keep and save that as qKeepThese. If you don't care which of the "duplicate" records you want to keep then you can use the First aggregate function to more-or-less randomly select one. Query One: This query is the key to identifying which records to keep SELECT First(PrimaryKeyField) as FirstID FROM TheTable GROUP BY VolgNummer Query Two: DELETE DistinctRow T.* FROM TheTable as T WHERE T.PrimaryKeyField IN (SELECT PrimaryKeyField FROM TheTable LEFT JOIN QKeepThese ON TheTable.PrimaryKeyField= QKeepThese.FirstID WHERE qKeepThese.FirstID is Null) All in one query would be as follows - only works if field and table names don't contain "Special" characters. DELETE DistinctRow T.* FROM TheTable as T WHERE T.PrimaryKeyField IN (SELECT PrimaryKeyField FROM TheTable LEFT JOIN (SELECT First(PrimaryKeyField) as FirstID FROM TheTable GROUP BY volgnummer) AS QKeepThese ON TheTable.PrimaryKeyField= QKeepThese.FirstID WHERE qKeepThese.FirstID is Null) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Jean-Paul wrote: Hi, I have a table containing about 3500 records All have a field named "volgnummer" Records with the same "volgnummer" should be deleted except for one of course How can this be done? Thanks |
Thread Tools | |
Display Modes | |
|
|