Eliminating Duplicate Records in Access 2002

[John]

I have a database of customer information containing 50K
records

Fields Include:
Code, First name, Last Name, Title, Company Name, Address
1, Address 2, City, State, Zip, Phone1, Phone2, Fax, E-
mail

I wish to eliminate all duplicate records having matching
fax numbers, e-mail addresses and phone numbers.

However, during the elimination process, I wish to retain
at least one each of the originals so that I can use that
information.

 

[John Vinson]

I have a database of customer information containing 50K
records

Fields Include:
Code, First name, Last Name, Title, Company Name, Address
1, Address 2, City, State, Zip, Phone1, Phone2, Fax, E-
mail

I wish to eliminate all duplicate records having matching
fax numbers, e-mail addresses and phone numbers.

However, during the elimination process, I wish to retain
at least one each of the originals so that I can use that
information.

Probably the simplest way - especially if you don't care which of the
duplicates should be retained - is to make a copy of your table,
design only. Create a unique Index on the three fields which
constitute a duplicate (Phone1, EMail and Fax), and run an Append
query appending from the old table to this one. Duplicate records will
be removed.

Records where Phone1 in one record matches Phone2 in another can be
found by creating a self-join query on those fields - you'll probably
want to delete these manually.

 

[Tom Wickerath]

Hi John,

Use the Find Duplicates Query Wizard. Select the Queries object in the database window.
Click on the New button. Select the "Find Duplicates Query Wizard". In the second
screen, which reads "Which fields might contain duplicate information", select your fax
number, e-mail addresses and phone numbers fields. In the next screen, which reads "Do
you want the query to show fields in addition to those with duplicate values", you might
as well select all fields. This will help you to determine which record you wish to
retain for a given customer and which records to delete.

I don't recommend attempting to automate the process of deleting records using VBA code.
It's much better for you to examine each group of duplicate records and decide for
yourself which to keep and which to delete. Also, I recommend DESELECTING "Cascade
Delete", because you don't want to accidentally delete related records. In that case,
you'll need to manually update the foreign key field in the related table before deleting
a duplicate record in the primary table.

Tom
____________________________________


I have a database of customer information containing 50K
records

Fields Include:
Code, First name, Last Name, Title, Company Name, Address
1, Address 2, City, State, Zip, Phone1, Phone2, Fax, E-
mail

I wish to eliminate all duplicate records having matching
fax numbers, e-mail addresses and phone numbers.

However, during the elimination process, I wish to retain
at least one each of the originals so that I can use that
information.