Delete Duplicates - Problem

[AccessNub]

Hi Guys,

I wanted to delete some duplicate records off of a table.

I went through the steps of copying the structure of a table to a new table then setting the Primary Key as what I wanted to be unduplicated. In my case, I have the Primary Key setup as the Account SSN and the Persons ID. This way it would be OK to have more than 1 SSN as long as I dont have more than 1 SSN and Person ID together.

I have 430 records on my table, when I run the find duplicate query, I see 22 lines (11 duplicates). I went through the table and confirmed that this is correct.

When I paste the original table to the PK table, it copies 392 records. Basically, it didn't copy 27 records it should have. I hunted down the 27 and they were not duplicates, they only had 1 SSN + Person ID.

So to make it short, the easy way to kill duplicates for some reason doesnt work for me.

Is there another way to do this? I was thinking of using the Find Duplicates Query and somehow turning it into a Delete Query, but I would only want it to Delete 1 of each it finds, not all of them.

Once again, thanks for the help.

 

[Jon K]

You can first run a Totals Query, using Group By for the SSN and Person ID fields and First for the other fields.

If the correct number of records are returned by the query, you can then change it into a Make-table query to create a new table.
.

 

[AccessNub]

You can first run a Totals Query, using Group By for the SSN and Person ID fields and First for the other fields.

If the correct number of records are returned by the query, you can then change it into a Make-table query to create a new table.
.

I failed to mention, I did try to use the First function, it chopped 1 record off, but left the others.

Thanks for the advise.