Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2019, 11:34 PM   #1
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Delete duplicates from table based on Find duplicates query

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 by jaryszek; 07-17-2019 at 11:45 PM.
jaryszek is offline   Reply With Quote
Old 07-17-2019, 11:53 PM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,248
Thanks: 115
Thanked 3,075 Times in 2,793 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Delete duplicates from table based on Find duplicates query

Its the same idea as covered in my synchronise data article that we've discussed before
http://www.mendipdatasystems.co.uk/s...a-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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jaryszek (07-17-2019)
Old 07-17-2019, 11:58 PM   #3
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Delete duplicates from table based on Find duplicates query

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

jaryszek is offline   Reply With Quote
Old 07-18-2019, 12:03 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,248
Thanks: 115
Thanked 3,075 Times in 2,793 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Delete duplicates from table based on Find duplicates query

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 07-18-2019, 12:29 AM   #5
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Delete duplicates from table based on Find duplicates query

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...



Quote:
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
Attached Images
File Type: png Screenshot_35.png (75.4 KB, 58 views)
jaryszek is offline   Reply With Quote
Old 07-18-2019, 02:12 AM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,248
Thanks: 115
Thanked 3,075 Times in 2,793 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Delete duplicates from table based on Find duplicates query

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...ete+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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 07-19-2019, 12:43 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,248
Thanks: 115
Thanked 3,075 Times in 2,793 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Delete duplicates from table based on Find duplicates query

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
Attached Files
File Type: accdb DeleteDupesExampleDB_v2_CR.accdb (452.0 KB, 8 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jaryszek (07-21-2019)
Old 07-21-2019, 08:41 PM   #8
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Delete duplicates from table based on Find duplicates query

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

jaryszek is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete Duplicates on a make table query irelisfigueroa Queries 1 09-24-2008 09:01 AM
Find duplicates based in a few characters mkelly Queries 5 09-23-2008 09:40 AM
Question Delete Duplicates in table when two or more fields are the same Steve_T General 4 08-05-2008 11:35 AM
Find duplicates and create table with dates of duplicates alix.grant Queries 6 02-13-2008 02:50 AM
Find Duplicates different than Duplicates deleted poet_will Tables 14 06-09-2006 11:03 AM




All times are GMT -8. The time now is 12:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World