duplicate records

[Jean-Paul]

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

 

[John Spencer]

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