Delete just the duplicates...

[BakerUSMC]

Hello to all,

I know there are several posts in regards to deleting duplicate records but they all show how to do it in SQL using 'Select' and all...

I am unfamiliar with this and would like to know is there an easier way or could someone briefly explain what steps are to be done to accomplish this...

I have about 32,000 records that have these dupilcated fields:

fname lname street city state

Thanks for all your help!!!

BakerUSMC

 

[jsteph]

You could use the Find Duplicates query wizard, then select those fields that you listed, run the query, and manually delete them, one by one.

Using a recordset is possible, but you never know if there are triplicates or quad or whatever, so it's not as simple as deleting every odd row, but it's still doable. You'd need a log more logic.

Why the aversion to sql?
--Jim

 

[BakerUSMC]

jsteph, thanks for your post... it will be a long process to manually delete every other record for 32,000 duplicates. That would mean I would be manually deleting 16,000 records. I don't really have that kinda time.

If this can be done easily using sql or something, please explain for me. I'm kinda clueless when it comes to that...

Thanks again...

 

[jsteph]

Baker,
You just need to add an autonumber field--for the sole purpose of quickly adding some unique identifier that doesn't need to mean anything to anyone. You could call this field 'recid'. Then use the below SQL:

DELETE a.recid
FROM mytable a
WHERE a.recid 
NOT IN (
    SELECT max(recid) 
    FROM mytable b 
    WHERE b.fname = a.fname    
    AND b.lname= a.lname
    AND b.street= a.street
    AND b.city= a.city
    AND b.state= a.state
)

--Jim

 

[BakerUSMC]

Thanks for the post again...

I put that code in the sql view and ran it... the database didn't do anything after 1 1/2 hrs. I don't know if its because there are 348,000 records and that would be normal for that amount or I'm still doing something wrong.

There is a unique ID for each field already and I substituted the mytable for the actual table name... then tried to run but nothing was happening....

Thanks......

 

[jsteph]

Go into the table design view, and click on the lightning-bolt icon or do View|Indexes.

Create a compoiste non-unique index on the fields in the WHERE clause. To do this, just enter an index name, then the first field in the FieldName column (probably Fname), then directly below that first field , enter the rest of the fields without any index_name, so only the first field in this new index has an entry in index_name.

Click save, it may take a while (do this on the console of the server if the .mdb is on a server and if that's possible).

Then run that query, and it should run significantly faster. Try to have the order of the fields in the new index you create in the same order as you list them in the WHERE clause of the delete query.
--Jim