Delete query help, example attached (1 Viewer)

IannWard · Mar 4, 2019

Hi All

In my example db I have created a query that looks for dups in a table and copies the results to a new table. Is what I am trying to do is delete all the dups except 1 from the main table. It was suggested to do it this way then create a delete query to delete all the entries in the main table based on the results from the second table and then create an append query to move the single results from the query table back to the main table, thus just having 1 entry of each.

I am struggling with how to create the delete query. I am no good at sql so any sql ideas will have to explain where I put the code etc.

Many thanks in advance.

Ian

theDBguy · Mar 4, 2019

Hmm, the usual suggestions I've seen involve creating a query to pull a unique set of records and then use a Make-Table query to put the result into a new table without duplicates.

June7 · Mar 4, 2019

DELETE query won't work because there is no unique identifier to pick 1 record for each group and purge the others. You would have to review each group identified as having duplicates and manually delete records from the primary table.

Review https://www.databasejournal.com/fea...lete-Duplicate-Records-From-Access-Tables.htm

Problem with the SELECT DISTINCT/MAKE TABLE approach is the ID field will be lost and new ID values generated. If there are dependent tables using this value as foreign key, relationships will be corrupted.

IannWard · Mar 4, 2019

Many thanks for the quick response. It doesn’t matter about the primary key because this table has no relationships, it is simply used to get stats for an excel report.

Regards

isladogs · Mar 4, 2019

Also have a look at this article on my website: Synchronising data.
As you have no primary key, the second page may be more useful to you.

As the title implies, its covering more than just deletion of duplicates

If none of those solve your issue, there is an interesting solution that MajP developed for a different purpose that may be adaptable to your needs.
See this lengthy thread https://www.access-programmers.co.uk/forums/showthread.php?t=303677&highlight=left+joins
You can probably skip to about post #42 onwards

Hope that helps

EDIT:
Just had a look at your file
Your query is a make table query & its only looking for dupes in the Returning Store field
The problem with that is that you have different values in the Asset Tag, Item Description, Serial Number & TSIE Found Fault fields for the same Returning Store values.

You need to be VERY CAREFUL that you identify exactly what makes a duplicate before deleting anything.
Once that is clarified, deleting duplicates won't be that difficult.
After that, you need to modify your table to prevent future duplicates occurring - using a composite index will fix that

IannWard · Mar 9, 2019

Hi

Thanks for the help. The query was supposed to return results based on all fields been the same, so every field duplicated. It may be that when someone submits an update to the original data in the main table that that particular field isn't copied to the new table, hence it just adds the same data again. That aside, how do I create the delete query to remove all the duplicates except 1.

Thanks again

isladogs · Mar 9, 2019

Impossible to advise as its not clear which of your records will be considered as duplicates. See comments in my previous reply.
Did you read the links I suggested?

IannWard · Mar 9, 2019

Hi Again

If you look in the TC51 Faults table I attached, you will see that devices have a different asset tag number, so in the table, there are several devices from the same returning store. See 4 lines below, these are just one of the dups, all is same except ID. I did try a composite index and yes that did work but each time the table refused a dup it came up with an error warning message, which is ok, but they are having to click through it all the time.

Back to the 4 lines below, how would I create the criteria in the delete query to delete them all, based on EVERY field been the same?

ID Store Date Asset Device Fault
20546 0295 27/02/2019 00114277 RFID SLED RFID Won't pair
20547 0295 27/02/2019 00114277 RFID SLED RFID Won't pair
20548 0295 27/02/2019 00114277 RFID SLED RFID Won't pair
20549 0295 27/02/2019 00114277 RFID SLED RFID Won't pair

Regards

isladogs · Mar 9, 2019

It would help if you were consistent in your field names
So ID, Store, Date, Asset, Device, Fault in your last query is I think
ID, Returning Store, Date Returned, Asset Tag, Item Description, TSIE Found Fault
That may be obvious to you but I had to work it out.....

This is one method of deleting all except the first duplicate in each group
1. qryDuplicateTC51Faults - I used the duplicates query wizard on those 5 fields (excluding ID) => 28 records
2. qryFirstDuplicateTC51Faults - select aggregate query to find the first ID in each duplicate group => 10 records
3. qryMakeTableDupesToDelete - creates table DupeTS51FaultsToDelete with all records from 1. not in 2. => 18 records
4. qryDeleteAllExceptFirstDupe - deletes all records from TS51Faults that are also in table created in step 3. => 18 records to delete

NOTE:
a) the process could be shortened but this is designed to ensure you can follow the steps.
b) Steps 2 & 3 only really need the ID field
c) CHECK the results are correct in case I've misunderstood or made a mistake. Test on a backup copy - as attached
d) Once the dupes are removed, you can then delete the table created in step 3.

Code:

DoCmd.DeleteObject acTable, "DupeTS51FaultsToDelete"

e) Finally create a composite index on the 5 fields used in the dupes query to prevent more duplicates in future

EDIT: Please use the attachment in post #12 instead of this version

MajP · Mar 11, 2019

The approach presented by Isladogs is recommended. This is an alternative approach I use sometimes, but there is no chance to validate what is going to be deleted. So you need to be confident in the code and may want to back up your table first

Find the matches by joining the correct fields (qryMatches). (If there are null values in those fields and you want to match than that will require extra work, and has been a popular discussion recently.)

Code:

SELECT A.id AS A_ID, 
       B.id AS B_ID 
FROM   [tc51 faults] AS A 
       INNER JOIN [tc51 faults] AS B 
               ON ( A.[tsie found fault] = B.[tsie found fault] ) 
                  AND ( A.[item description] = B.[item description] ) 
                  AND ( A.[asset tag] = B.[asset tag] ) 
                  AND ( A.[date returned] = B.[date returned] ) 
                  AND ( A.[returning store] = B.[returning store] ) 
WHERE  (( ( B.id ) <> [A].[id] )) 
ORDER  BY A.id, 
          B.id;

Loop each discrete ID and return a recordset of its matches (excluding itself). Loop the matches and delete them.

Code:

Public Sub DeleteDupes()
  Dim rs As DAO.Recordset
  Dim rsDupes As DAO.Recordset
  Dim strSql As String
  'Read each ID
  strSql = "Select Distinct A_ID from qryMatches"
  Set rs = CurrentDb.OpenRecordset(strSql)
  
  Do While Not rs.EOF
    'For each ID find its matches and delete
    strSql = "Select B_ID from qryMatches where A_ID = " & rs!A_ID
    Set rsDupes = CurrentDb.OpenRecordset(strSql)
    Do While Not rsDupes.EOF
      strSql = "Delete * from [tc51 faults] where ID = " & rsDupes!B_ID
      CurrentDb.Execute strSql
      rsDupes.MoveNext
    Loop
    rsDupes.Close
    rs.MoveNext
  Loop
End Sub

IannWard · Mar 11, 2019

Hi Colin

That is fantastic, you have saved me a huge amount of time, it would have taken me an age to figure that out, I am getting better at queries but not the complicated ones like these.

However..

I ran the last query to delete the 18 records but I get the error message 'Could not delete from specified tables'.

Any ideas? we're almost there

Huge thanks

isladogs said:
It would help if you were consistent in your field names
So ID, Store, Date, Asset, Device, Fault in your last query is I think
ID, Returning Store, Date Returned, Asset Tag, Item Description, TSIE Found Fault
That may be obvious to you but I had to work it out.....

This is one method of deleting all except the first duplicate in each group
1. qryDuplicateTC51Faults - I used the duplicates query wizard on those 5 fields (excluding ID) => 28 records
2. qryFirstDuplicateTC51Faults - select aggregate query to find the first ID in each duplicate group => 10 records
3. qryMakeTableDupesToDelete - creates table DupeTS51FaultsToDelete with all records from 1. not in 2. => 18 records
4. qryDeleteAllExceptFirstDupe - deletes all records from TS51Faults that are also in table created in step 3. => 18 records to delete

NOTE:
a) the process could be shortened but this is designed to ensure you can follow the steps.
b) Steps 2 & 3 only really need the ID field
c) CHECK the results are correct in case I've misunderstood or made a mistake. Test on a backup copy - as attached
d) Once the dupes are removed, you can then delete the table created in step 3.

Code:

DoCmd.DeleteObject acTable, "DupeTS51FaultsToDelete"

e) Finally create a composite index on the 5 fields used in the dupes query to prevent more duplicates in future

isladogs · Mar 11, 2019

Oops - when deleting in a query involving two tables you have to set Unique Records =Yes.
In SQL view that's DELETE DISTINCTROW...…

Corrected version attached. Hopefully correct this time.

IannWard · Mar 11, 2019

Worked a treat.

Thank you very much indeed.

isladogs · Mar 11, 2019

You're welcome

Delete query help, example attached (1 Viewer)

IannWard

Registered User.

Attachments

theDBguy

I’m here to help

June7

AWF VIP

IannWard

Registered User.

isladogs

MVP / VIP

IannWard

Registered User.

isladogs

MVP / VIP

IannWard

Registered User.

isladogs

MVP / VIP

Attachments

MajP

You've got your good things, and you've got mine.

IannWard

Registered User.

isladogs

MVP / VIP

Attachments

IannWard

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread