Record "merge"

[Steve Voorhees]

I have a database that has 3 different tables that I have linked via
relationships. They are: Customers, Complaints, and Call Info.
In the Customers table I have a unique field named CustomersID. In the
Complaints table, I have a field called CustomersID, set as a number, and a
unique field in called ComplaintID. In the Call Info table, I have the field
ComplaintID as a number.
The various tables are linked via relationships.
What I have found is that there are numerous customer records, that have
associated records in the other 2 tables.
What I would like to be able to do is "merge" the duplicate records into a
single record, and have any associated records reflect the change.
Any suggestions?

 

[Jeff Boyce]

Steve

Backup, backup, backup.

Just so I'm clear, you are saying that "Joe Blow" is in your Customer table
repeatedly, with multiple CustomerIDs?

If so, pick the one you like best, find all the rest, and in your Complaints
table, change the foreign key of "the rest" (Joe's other IDs) to the one you
like best. This will leave you with all Complaints related to Joe all
pointing back to a single Joe Blow Customer recordID.

Since your Call table is only related to your Complaint table, you should
need to make any changes there (but just to be clear again, you are saying
that your Call table does NOT have CustomerID in it, right?).

 

[Steve Voorhees]

OK. I was just kind of hoping that a "automated" process could be created,
rather than manually going through all the records.
I have a query already that finds duplicates based on Address or phone
number. I'll just play around with it a little I guess.

 

[Jeff Boyce]

Steve

I suspect you are dealing with a situation that requires "USB" (using
someone's brain). If Joe Blow is in your Customer table repeatedly, AND if
his name (address, phone, etc.) is IDENTICAL, you can use the Access query
wizard to find duplicates. However, even though you and I would probably
consider the following as "duplicates" (rightly or wrongly):

Joe Blow 12345 Elm St
Joe Blow 12345 Elm St SW
Joe Blow 12354 Elm St
Joe Blow 12345 Elm
Joseph Blow 12345 Elm St

Access will NOT call these matches unless you define every possible way in
which they might be "close matches". This is where "USB" comes in.

(and by the way, these five are NOT the same person -- we have different
street names/numbers/directionals in my town, and Joe has a son named
Joseph... <G>!)

Good luck

Jeff Boyce
<Access MVP>