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:
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?