Delete duplicates from table based on Find duplicates query (1 Viewer)

jaryszek

Registered User.
Local time
Yesterday, 22:15
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i created access built-in find duplicates query but i do not know how to delete from my primary table based on that table.

Because in Find duplicates query are 2 records but i only should delete 1 of them.

Can anybody help?

To precise:
I have main table with primary key.
But in this table i have duplicates- the same rows.

Data is imported from CSV file without PK.

I am creating find duplicates but i have to only keep one record from Find Duplicates Main Table query.
Please help with writing SQL for this.

Jacek
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 05:15
Joined
Jan 14, 2017
Messages
18,186
Its the same idea as covered in my synchronise data article that we've discussed before
http://www.mendipdatasystems.co.uk/synchronise-data-1/4594514001

The easiest way is to create a new empty table with the same structure but with a composite index added to prevent dupes. Now import your data to the new table. Only one of each will be imported. Delete the original table.

If it matters which duplicate is removed then create a new temp table containing e.g. the last of each dupe. Next create a query linking that to the main table and change to a delete query. You will need to repeat if you had more than two of any dupes until none are left
As above, after deleting the dupes, create a composite index.
 

jaryszek

Registered User.
Local time
Yesterday, 22:15
Joined
Aug 25, 2016
Messages
756
Thank you Colin,

yes i am using your method to avoide duplicates but before i didnt so i have duplicates already.

ok but problem with this approach is that i have a lot of fields and index composite key can noe be applied for more than 10 fields yes?

Jacek
 

isladogs

MVP / VIP
Local time
Today, 05:15
Joined
Jan 14, 2017
Messages
18,186
10 is the limit according to the specifications.
AFAIK that limit can't be bypassed.

So if you can't prevent dupes, I would setup a procedure like my second suggestion to loop through and remove them. Then run it on a regular basis after each import.

Or if possible change your table design so its not like a spreadsheet in structure.
 

jaryszek

Registered User.
Local time
Yesterday, 22:15
Joined
Aug 25, 2016
Messages
756
ok thank you very much.

I tried to add one additional string columns with MD5 function:
https://www.di-mgt.com.au/src/basMD5.bas.html

but i can not use having count >1 in find duplicates query...



If it matters which duplicate is removed then create a new temp table containing e.g. the last of each dupe. Next create a query linking that to the main table and change to a delete query. You will need to repeat if you had more than two of any dupes until none are left

this is your second menthod yes?
Ok how can i create the last of each duplicate? How can i do this? Can you help with SQL becuase i have no idea where to start?
This method seems to be very good.

Best,
Jacek
 

Attachments

  • Screenshot_35.png
    Screenshot_35.png
    75.4 KB · Views: 243

isladogs

MVP / VIP
Local time
Today, 05:15
Joined
Jan 14, 2017
Messages
18,186
Hi
I've never worked with MD5 and haven't got time to study that code so I can't help with the error message. If the MD5 hash created is a memo field you can't use it in any search field in your query.

I'm busy all day today but can possibly do a simple example of method 2 this evening. However, I remember describing that approach in detail in more than one thread some time ago. An advanced search for delete duplicates and my user name produced 20hits. For example, see post #3 in https://www.access-programmers.co.uk/forums/showthread.php?t=294098&highlight=Delete+duplicates
The whole thread is worth reading in my opinion.

Also another method of preventing future dupes that you could consider. Its a bit of a botch but may do the job. Create a composite field of all your many fields and set to no duplicates. If you can do that in a short text field of 255 characters, that will work (but will be messy). If it needs to be a memo field its no use.
However you could create say three composite text fields concatenating data from say 6 fields each. Set each to no dupes. Then do a composite index of those three new fields.
Neither is good programming practice but may fix your issue
 

isladogs

MVP / VIP
Local time
Today, 05:15
Joined
Jan 14, 2017
Messages
18,186
Jacek
You may have already solved your issue but as you haven't yet replied to my last post I'll go ahead with an example which I prepared earlier (rather than reinvent the wheel) ;)

The attached simple example was written in March in a response to another forum member.
I've forgotten who it was now and which thread it came from so I hope they don't mind me posting their data here....

The attached contains a table TC51 Faults with 37 records of which 28 are duplicates (see qryDuplicateTC51Faults)
In this case, the objective was to keep the first of each dupe (qryFirstDuplicateTC51Faults) of which there are 10 and delete the rest (18 of them).

This requires running a query qryMakeTableDupesToDelete to make a temp table Dupe51FaultsToDelete

The delete query qryDeleteAllExceptFirstDupe links the original & temp tables. If a record is in both tables it will be deleted

The temp table can then be deleted (or emptied if the process needs to be repeated in the future)

NOTE:
1. The temp table is needed as a delete query cannot be based on another query such as qryFirstDuplicateTC51Faults.
Trying to do so would result in an error as the process would affect the records on which the query was based.
Not sure I've explained that at all well. Hope that makes sense to you...
2. If doing this process repeatedly, I would create a procedure to run each query (or execute SQL statements) in turn

Hope that helps
 

Attachments

  • DeleteDupesExampleDB_v2_CR.accdb
    452 KB · Views: 128

jaryszek

Registered User.
Local time
Yesterday, 22:15
Joined
Aug 25, 2016
Messages
756
hi Colin,

thank you vey much! And sorry for late answer, i am on road last time.
I will investigate solution, is very helpful.

Jacek
 

Users who are viewing this thread

Top Bottom