Cross Reference Records in a Table?

[Guest]

I have an application deployed to some users. It is a shell that we
use over and over again for projects. We are running into a situation
where the data we receive contains duplicate records. There is a
unique identifier to each one, but they are for the same "customer".
We are told that any time a customer has a change to their account
they got a new record. We need to keep all the records, because their
orders are tied to their unique id, but would like to be able to see
from any one record, what other customer records they have.

How would I construct this, and how would the end users be able to
manage this on the front end? I imagine a cross reference table, but
have some confusion with it.

Then on the entry form, I could make it an extra tab for them, but how
would they look up the similar ones, and match them? Pretty much the
names are the same, so I would like to automate it once it is set up
(run something to go through and cross reference as many as possible,
by name).

Any thoughts or ideas?

I appreciate any help!

Thank you,

 

[Guest]

OK. I have started to put something together. I think it will
represent on the form well, but I would like to populate as many of
the relationships as I can, so that the users don't have to do it all
manually. I am hoping someone can help me with how to do this.

I have a table called tblcustomers and now I have a second table
called tblrelatedcustomers

tblcustomers

CiD - pk
CNum - Customer number tied to orders
CName - Customer name

tblrelatedcustomers

RiD - pk
RMasterC - the min CNum in a related series (will explain rules for
relating in a minute) - lookup to CiD
RRelatedC - a CNum related to the RMasterC or master customer record.
- lookup to CiD

The related records have the same Customer name. What I would like to
do is create a query or function of some sort that will find the
duplicates and then populate this tblrelatedcustomers with the minimum
customer record and each customer record it is related to.

For Example:

tblCustomers
CiD CNum CName
1 123 Jane Doe
2 124 Jane Doe
3 125 Jane Doe
4 126 John Doe
5 127 John Doe

What I want to do is populate tblRelatedCustomers as shown below.

tblRelatedCustomers
RiD RMasterC RRelatedC
1 1 1
2 1 2
3 1 3
4 4 4
5 4 5


Does this make sense? Can anyone help?

Thank you!