duplicate records with extra data

[Guest]

I need to delete duplicate records but obtain extra info.

This is what I have
ID Fname Lname
Phone
2131 John Doe
306-373-2306
2132 Jane Doe
306-373-2306

what I want
ID Fname Lname
Phone
2131 John & Jane Doe
306-373-2306

I have about 12,000 records to go through and I dont really want to do it 1
by one. I really need some help!!! Thanx

 

[Allen Browne]

Have you thought about how to handle the variety of data that will exist in
your 12k records?

Consider cases such as:
- People with different surnames, but the same phone number.
- More than 2 people with the same phone number (same surnames).
- More than 2 people with the same phone number (different surnames).
- Individuals who have more than one phone number.
Then comes the question of which order the names should appear in. (That
could matter where the 2 names are father and son, for example.)

To get you started:
1. Create a query into this table.

2. Depress the Total icon on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the Phone field into the grid.
Accept Group By under this field in the Total row.

4. Drag the ID field into the grid.
Choose Count in the Total row.
In the Criteria row, enter:

1

5. Drag Fname into the grid.
In the Total row, choose Min

6. Drag Fname into the grid again.
In the Total row, choose Max

7. Drag the LName field into the grid.
Accept Group By (assuming you want to de-dupe on this field.)

This query shows the 2 names beside each other. You can combine them into
one text box on your report.

In the cases where CountOfID is more than 2, you still have missing names.
You could use something like this code to contatenate them if you really
wanted to:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

A more thorough answer would be to actually define the kinds of
relationships that exist between these people so you can treat the
individuals as clients when you need to, or the household as a client when
that is appropriate. Downloadable example:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

HTH.

 

[Guest]

Dear Cheesewizz76:

I had to help a customer with the same problem and they had even more
records! There are several ways to handle the problem of duplicate records,
but the most efficient way is through queries. The resulting dataset gives
you a clear view of your data and then you can review the data either
individually or in large sets, suprisingly quickly! You can, then, delete
large blocks of data at once.

However, the creation of the 'Duplicates Query' must show all data in each
record, not just the matching data, in order for you to determine which
records need to be deleted. You then create the 'Delete Query'. Once these
2 queries have effective formulas to create the sufficient datasets, you can
quickly 'clean' out your database and delete all of the duplicate records.

Contact me if you are still having this problem, before I get into this with
you as it requires more than a little brainpower and information to do it
correctly.

DO NOT just run the standard delete or duplicates query as they are not
sufficient in and of themselves to solve your problem. The delete query WILL
take out data you don't want taken out!

Let me know if you still need help with this.

Sincerely,

Ruth Allen
(e-mail address removed)
E-Business Consultant
Embracing Technology
embracingtech.com

/RA