Remove duplicate records MS Access SQL Microsoft
In Access, I can run a SELECT query that finds duplicate records that I need to delete, but cannot run a query that deletes them.
This is the query code that shows me the correct duplicate entries and groups them up. He shows me 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 to delete records, it wants to delete all records, event, although I 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));)));
Could you please advise how to fix it?
source to share
The request will not work as it is for several reasons. The main reason is that the number of items will change as it is removed, so the engine will not be able to keep track of what needs to be removed.
However, your request is meaningless. The last function does not have to be in the same column as Count. Otherwise, you are deleting every duplicate, not just the last one from every duplicate.
If you have a primary key, you can do this in two steps. If you don't, additional steps are required because you need a unique field:
An alternative step if you don't have a primary key: add an Autonumber field named RecordId. If you already have a primary key, use it instead of RecordId in the next steps.
First , create a temporary table with the IDs you want to drop. 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 the delete statement, which uses the temp_Delete table to restrict what you delete. You will attach to the RecordId field (caveat: you won't be able to do this in the visual editor because it will mess up your sql. You will have to write and run it manually.)
DELETE DISTINCTROW tblDat01Prod.*
FROM tblDat01Prod INNER JOIN temp_Delete
ON tblDat01Prod.RecordId = temp_Delete.LastId
Security step . If you are at all concerned about the loss of important data, I would suggest entering one more step before deleting. Add the boolean column IsDelete to tblDat01Prod and use the temp_Delete table to update the corresponding fields to True. Then compare which records should be deleted and which were not deleted. Then delete the entries where IsDelete is true, not step 2 above.
Finally , delete the temp_Delete table and any fields added to tblDat01Prod that you don't want to keep.
source to share
Thanks a lot for your help Don! I was able to tweak the code a bit to make sure the data I wanted was deleted, but you were a fantastic +1 help for you (if I had a reputation to do this).
Since I needed to delete the oldest record entered in the database, I sorted the table as a sheet by the date of entry, created a DeleteOldestEntry column, inserted the PK into excel and created an enlarged list of numbers, inserted those numbers back into the DeleteOldestEntry access. Then I changed the 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 delete expression
DELETE DISTINCTROW tblDat01Prod.*
FROM tblDat01Prod INNER JOIN temp_Delete ON tblDat01Prod.DeleteOldestEntry = temp_Delete.OldID;
Thanks again!
source to share