Delete similar records

[pmccue]

How can I delete similar records in Access?
There's the info:
In a large table are many occurances of two or more records where the LastName, FirstName, and DOB fields all match, ie, these are not duplicate records where all fields match.
I want to delete similar records, leaving one unique record in the table (unique LastName, FirstName, DOB fields).
Would need an sql example for syntax as all my attempts using sql have so far failed.

Thanks,
Phil....

 

[IT4EVR]

something like this:
assuming you have some increment field like an autonumber...

DELETE *
FROM Table1 AS a
WHERE a.ID NOT IN(SELECT MIN(b.ID) FROM Table1 AS b WHERE a.LastName = b.LastName AND a.FirstName = b.FirstName AND a.DOB=b.DOB);

 

[pmccue]

IT4EVR

Thanks for the quick repsonse!

I used your script, modified as required. But Access hangs! Is there maybe a more straight forward but less elegant way to script this?

Phil...

 

[IT4EVR]

So you are not getting any error messages? The screen freezes up? How many records are in the table?

Could you post the query?

thanks...

 

[pmccue]

Below is your script, modified for my table (Table1 = Crim_Table_rev & DOB = DateOfBirth). All other syntax was left unchanged.

Other info:
1. No error messages, script seems to run but app simply hangs..yup screen freezes. I can kill the app in Task Mgr though and recover to desktop without having to restart PC
2. ~160K records in table X 40 fields
3. 512MB RAM installed in PC
4. O/S running is XP Prof, with SP2
5. I added an AutoNumber field as 1st field as there was no existing field for ID you used in script.

DELETE *
FROM Crim_table_rev AS a
WHERE a.ID NOT IN(SELECT MIN(b.ID) FROM Crim_Table_rev AS b WHERE a.LastName = b.LastName AND a.FirstName = b.FirstName AND a.DateOfBirth=b.DateOfBirth);

Gracias,
Phil....

 

[IT4EVR]

ok, you probably would have gotten better performance if you had put an index on autonumber...primary key...make a copy of this table and run it again and see what happens.

also, you say you want to get rid of duplicates, but what about this case:

LastName   FirstName   DOB   State
Smith      John        1/1/65   VA
Smith      John        1/1/65    DE

Do you want to delete one of these records? Or keep them.

 

[IT4EVR]

ok, i see you have started a new thread, so go ahead and go with those guys...they seem to have you in the right direction.

 

[pmccue]

IT4EVR,

In you above posts, I'd keep 1st instance of record and delete all others.

I tried indexing the table based on ID field, which is autonumber format, but app still hangs.