Keeping the "First" Duplicate Record, Delete Subsequent

[jgraves]

I am using Access 2002.
I have a project that uses the "Find Duplicates" query to display duplicate
records in a table, using "Part Number" as the field that determines whether
the record is a duplicate. I use the trick of creating a copy of that table
then defining "Part Number" as the key so that when I append records from the
original table, they are forced to be unique.
The duplicate records are identical except for one other field ("Bar Code").
I want to keep the first record as it appears in the original table, and
delete subsequent occurences of the record with the same part number. But
when I append the data to the copy of the orignal table with the key defined,
I can detect no method by which Access determines which record to keep.
Is there any way to control which record gets thrown out when I do the
"Append" trick?

 

[John Spencer]

As far as I know there is no method to use the append trick and specify
which of the duplicate records to keep.

How do you determine which of the duplicates you want to keep? Is it the
one with the lowest Bar Code or the highest Bar Code or some other criteria?

Check out the following URL
http://www.fabalou.com/Access/Queries/delete_dupe_records.asp

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 TelephoneNumber


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 TelephoneNumber) AS QKeepThese
ON TheTable.PrimaryKeyField= QKeepThese.FirstID
WHERE qKeepThese.FirstID is Null)

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "RemoveDuplicates.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L