2

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?

  • Try window function with ranking. You would PARTITION BY the set of fields you want to be unique and rank based on the ID field. You can insert result into the dup table or just run delete on it right away deleting everything below rank 1. – yahor Mar 13 '16 at 19:02
1

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
  • Perfect, that let me append just one of the duplicates, including unique ID, so I could run a duplicate delete based on a join on the ID. Thanks! – TCassa Mar 14 '16 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.