Ms-access – De-duplication method for fields that are identical except for Unique ID

duplicationms access

I'm looking for a decent de-duplication method. Basically I have a table which has five fields plus a Unique ID (key) field. I've run a duplicate which detection query which finds any records where ALL fields match except the unique ID, which gives me this:

enter image description here

So the first two records are identical apart from the unique ID (end column), and the next two are identical, etc. I have a few hundred of these so rather than just clicking and deleting, what would be the quickest way of just deleting ONE of these two records? I've tried a couple of append queries, such as 

INSERT INTO tblDupScanLines ( ScanTime, ScannerNo, ScanDate, EventName, PID, )
SELECT DISTINCT qryDupScanLines.ScanTime, qryDupScanLines.ScannerNo, qryDupScanLines.ScanDate, qryDupScanLines.EventName, qryDupScanLines.PID
FROM qryDupScanLines;

But I get stuck because I then don't have anything to dedupe them against on the main table (I've done something similar before but using MinOfID to delete whichever one had the lowest ID number) but trying to import the unique ID column as well would import ALL records and not the DISTINCT ones.

Can anyone help?

Best Answer

It seems to me that this should work:

INSERT INTO tblDupScanLines (ScanTime, ScannerNo, ScanDate, EventName, PID, ID)
SELECT qryDupScanLines.ScanTime, qryDupScanLines.ScannerNo, qryDupScanLines.ScanDate, 
    qryDupScanLines.EventName, qryDupScanLines.PID, MIN(qryDupScanLines.ID) AS minID
FROM qryDupScanLines
GROUP BY qryDupScanLines.ScanTime, qryDupScanLines.ScannerNo, qryDupScanLines.ScanDate, 
    qryDupScanLines.EventName, qryDupScanLines.PID
Related Question