Deleting duplicates

[gavin]

I have a large(ish) one table database of names and addresses and there is a
large number of duplicates - I know because I ran the find duplicates
wizard. My question is, how can I delete the duplicates easily without
having to do it manually?

A record is considered to be a duplicate if the FirstName, LastName and
Street fields are identical. It doesn't matter which of the records is
deleted as long as one remains in the table!


Many thanks for any help.



Gavin

 

[Eric Blitzer]

Copy the table (structure only)
Add an index base on the FirstName, LastName and Street fields.
Make it unique.
append the records from the old table to the new one
Duplicates will be ignored.

 

[gavin]

Thanks very much, Eric - how do I copy the structure only?

 

[John Vinson]

I have a large(ish) one table database of names and addresses and there is a
large number of duplicates - I know because I ran the find duplicates
wizard. My question is, how can I delete the duplicates easily without
having to do it manually?

A record is considered to be a duplicate if the FirstName, LastName and
Street fields are identical. It doesn't matter which of the records is
deleted as long as one remains in the table!

So you're willing to discard Jim Smith Jr. if he happens to live with
his dad Jim Smith Sr.? <g>

The simplest way is to create a new table; put a unique Index on the
combination of fields, and Append the data from your current table
into the new one.

John W. Vinson[MVP]

 

[gavin]

So you're willing to discard Jim Smith Jr. if he happens to live with
his dad Jim Smith Sr.? <g>

The simplest way is to create a new table; put a unique Index on the
combination of fields, and Append the data from your current table
into the new one.

John W. Vinson[MVP]

Thanks for that, John. I know a small number of "anomalies" might slip
through the net but it isn't that critical



Regards,


Gavin

 

[Chris Reveille]

To copy the structure only.
highlight table
press cntl C
press cntl V
give table name
check structure only
click OK

 

[gavin]

I have a large(ish) one table database of names and addresses and there is a
large number of duplicates - I know because I ran the find duplicates
wizard. My question is, how can I delete the duplicates easily without
having to do it manually?

A record is considered to be a duplicate if the FirstName, LastName and
Street fields are identical. It doesn't matter which of the records is
deleted as long as one remains in the table!


Many thanks for any help.



Gavin

Thanks for the replies, everyone. I managed to copy the structure of the
table and then I added indexes to the FirstName, LastName and Street fields
setting the Unique property to Yes. I ran an append query but got the
following error message:

Microsoft Office Access set 0 field(s) to Null due to a type conversion
failure, and it didn't add 11510 record(s) to the table due to lock
violations, and 0 record(s) due to validation rule violations.

What have I done wrong? What is a "lock violation"?



Thanks,



Gavin

 

[Eric Blitzer]

You should only have one index which is a combination of FirstName, LastName
and Street fields.

 

[gavin]

I'm not sure what it's called what I did. I opened the indexes dialogue box
and in 3 separate rows entered index names and field names, setting the
Unique property of each to Yes. Is this not right? If not, I would be very
grateful if you could tell me how to do it.



Regards,




Gavin