delete query, delete duplicates but keep one

[isladogs]

jdraw method is the simpliest.

When i post vba against sql some wise guy react. now sonebidy post vba, approved without thinking?! Who are you?

Both jdraw's method and mine work well.
The only reason I prefer mine is that there is no need to create a temp table.

As for the second sentence arnelgp, the recent comments were about you using VBA functions unnecessarily when a simpler solution is available.
Nobody objects to the use of VBA when that is the best solution.

And what are you on about here....

...approved without thinking?! Who are you?

 

[arnelgp]

As for the second sentence arnelgp, the recent comments were about you using VBA functions unnecessarily when a simpler solution is available.
Nobody objects to the use of VBA when that is the best solution.

Look at my Sql post, is that not a simple solution without unnecessary vba?

 

[isladogs]

If your sql code works that's great.

I haven't tested it myself as I have no duplicates needing removal.
However, S_Preston gave a reason (max locks) for not using it.

Whilst that reason is solvable, you haven't explained what your comment was on about ....

 

[isladogs]

@arnelgp
I've just tested your generic duplicates delete query on a small data sample I made specially for testing and can confirm it worked perfectly.
The first record of each duplicate set is retained, all others deleted

I see no reason why it shouldn't work for large datafiles as well though I haven't tested it

 

[Pat Hartman]

The simplest solution for eliminating duplicates is:
1. Create a new empty table.
2. Add the necessary unique indexes to PREVENT duplicates.
3. Create an append query to select the old data and append it to the new table. Sort this query to get the record you want to save to sort first.

At the end of the append, you will get an error message telling you how many duplicates were ignored.

 

[Mark_]

@ Preston,

Can you shed a little light on your issue, namely how you came to have 3 million records with enough duplicate data to warrant this? Helping fix the data after the fact leaves you with the issue of how duplicate entries were put in originally.