Deleting Duplicates

[paulreed]

I have a table, linked to another application whose format I cannot modify, which contains 5 fields containing data. Occasionally, data is recorded which is identical in fields 2, 3 & 4 (not bothered about 1 & 5).
I would like to create a process where the duplicates are deleted, whilst leaving 1 of the entries intact (doesn't matter which one).

I have searched the forum, and this has been asked a number of times - but the replies have been messy - such as creating another table & deleting the original, but surely there must be a better solution as this will need to be automated, such as through a macro etc. to facilitate user's abilities.

- easy for me to say!

 

[WayneRyan]

Paul,

You don't specify by what means you receive the data. In
order to know how to handle it, one must know where it
comes from and in what context you receive it.

Need more info ...

Wayne

 

[paulreed]

Wayne

We have a visual basic application which stores its data in a mdb file on a server, the mdb file has 5 tables within.
As an administrator, I access that data file via an admin mdb file, which has 5 tables 'linked' to the data file above, so in effect, I am working directly on the data held on the server and can delete or amend entries accordingly.
The table where I wish to remove duplicate entries, is not in a direct relationship with the other 4 tables, therefore deleting data is not a problem.
My first attempt was to create a 'find duplicates' query, which worked fine, but I then had to manually go through the query and delete the entries, ensuring that I leave at least one of them intact, which is time consuming (and leads to errors!)

Hope you can assist

 

[Lanason]

I have the same probelm

Yes - I have the same problem and used the same approach I.e. duplicates query and manual delete. To make it worse I have multiple duplicates - reason being it is based on a download from another source where I cannot control the data.

I tried to number the records so I could say keep all the number 1's deleteing > than 1, but could get this numbering to work. Thus I'm interestred to know the answer as well.

Maybe thats a solution - put a field in the curent table with say "1", import the data and then using a duplicates delete all those without the 1. Having done that all new records that arnt duplicate need the 1 added. Might even be a better idea to do it the other way round. sound possible ???

 

[paulreed]

Unfortunatley, Adrian. I cannot add any fields to the table, or change any of it's characteristics, just analyse, amend or delete the entries. I can of course add a query, macro, etc to act upon the data held in that table.
You and I are not alone in trying to find a way of dealing with this issue, just check previous entries, but none of the posts have been answered with a good solution.

I am suprised that the expertise within this forum have not found a solution, but perhaps one day...

 

[paulreed]

Pat, I'm sure that you are right, this should have been addressed at design stage, and no doubt at some stage will be, but by someone with more confidence in their programming abilities than me (I don't do visual basic!)

But at this moment in time, I am stuck with the bandaid, and if the expertise in this forum can rise to the challenge, I would be endebted.

 

[paulreed]

Pat, forgive my ignorance, but I don't know how to acheive the second solution.
I have attached the table, and as you will see, the field Incidentnum contains 2 records where fields 2, 3 & 4 are the same (inc 1224), how will you solution work?

 

[Mitch_____W]

I have attached your .mdb file with a form and a query added to it. Copy the Form and Query into your DB and then open the form and click the "Delete Duplicates" button. The code should delete the duplicates you are wanting to delete. Most likely you will need to go to "Tools" and then "Options" and then on the "Edit/Find" tab uncheck the "Record Changes" checkbox under "Confirm", or you will have to confirm each deletion...

Hope this works for ya!

 

[paulreed]

Mitch

That works brilliant!

I am not quite sure how you have acheived it, and will spend some time this evening looking through your code. But none the less it works a treat, and I would like to thank you for your patience and help.

 

[Lanason]

I'll try that

Pat - good idea - thats so obvious isnt it -

funny how the easy answer seems to slip by....... I'll have a go at that.

Thanks