Remove Duplicate Records MS Access SQL Microsoft

Question

In Access, I am able to run a SELECT Query that finds the duplicate records that I need to delete, but cannot execute a query that will delete them.

This is the code for the query that shows me the correct records of duplicates and groups them. It shows me the 1083 records (They are grouped)

SELECT Count(tblDat01Prod.[MFG #]) AS [CountOfMFG #], Last(tblDat01Prod.[MFG #]) AS [LastOfMFG #], tblDat01Prod.[MFG #]
FROM tblDat01Prod
GROUP BY tblDat01Prod.[MFG #]
HAVING (((Count(tblDat01Prod.[MFG #]))>1));

This is the code for deleting the records, it wants to delete all records, event though I only want it to delete 1083.

DELETE tblDat01Prod.[MFG #]
FROM tblDat01Prod
WHERE (((tblDat01Prod.[MFG #]) In (SELECT Last(tblDat01Prod.[MFG #]) AS LastMFG
FROM tblDat01Prod
GROUP BY [MFG #]
HAVING (((Count(tblDat01Prod.[MFG #]))>1));)));

Can you please advise on how to correct?

Answer 1

The query won't work as is, for several reasons. The main reason is that the counts of items will change as you delete them, so the engine can't keep track of what to delete.

That being said, your query doesn't really make sense. The Last function should not be on the same column as the Count. Otherwise you are going to delete every duplicate, not just the last one of each duplicate.

If you have a primary key, you could do this in two steps. If you don't, it will take extra steps because you need a unique field:

Alternate Step if you have no primary key: Add an Autonumber field called RecordId. If you already have a primary key use it instead of RecordId in following steps.

First, create a temp table with the Ids you want to delete. We'll call this temp_Delete:

SELECT Last([RecordId]) AS LastId INTO temp_Delete
FROM tblDat01Prod
GROUP BY [MFG #]
HAVING Count([MFG #])>1

Second, run a delete statement which uses the temp_Delete table to limit what you are deleting. You will join on the RecordId field (caveat: you may not be able to do this in the visual editor because it will mess up your sql. You'll have to write and run it manually.)

DELETE DISTINCTROW  tblDat01Prod.* 
FROM  tblDat01Prod INNER JOIN temp_Delete 
ON tblDat01Prod.RecordId = temp_Delete.LastId

Safety Step: If you are at all concerned about losing important data, I would advise injecting another step before the delete. Add a boolean IsDelete column to tblDat01Prod and use the temp_Delete table to update matching fields to True. Then compare which records are set to be deleted and which aren't before you delete. Then delete records where IsDelete is true rather than Step 2 above.

Finally, delete your temp_Delete table and any fields you added to tblDat01Prod that you don't want to retain.

Answer 2

Thank you very much for your help Don! I was able to modify your code a little bit to my needs to make sure the proper data was deleted, but you were a fantastic help +1 for you (if I had the reputation to do so).

Since I needed to delete the oldest record entered into the Database I sorted the table in Sheet view by Date Entered, created a Column DeleteOldestEntry, pasted the PK's in excel and created an increasing number list, pasted those numbers back into access in DeleteOldestEntry. Then I modified your code to this.

SELECT First(tblDat01Prod.[DeleteOldestEntry]) AS OldID, [MFG #] INTO temp_Delete
FROM tblDat01Prod
GROUP BY tblDat01Prod.[MFG #]
HAVING (((Count(tblDat01Prod.[MFG #]))>1));

Then Ran the delete Statement

DELETE DISTINCTROW tblDat01Prod.*
FROM tblDat01Prod INNER JOIN temp_Delete ON tblDat01Prod.DeleteOldestEntry = temp_Delete.OldID;

Thank you so much again!