Cross Reference Records in a Table?

[misscrf]

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,

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Darrylles]

Miss,

Can you be clearer please? Customers have 'other' customer records?


ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[misscrf]

We received customer records from another source. Here is what it looks like:

id Name Add
1 Jane Doe 1 Main St
2 Jan Smith 1 Main St
3 John Black 123 3rd Ave


Jane Doe was given a new record when her name changed, but she is really the same person. We don't want to send her communications twice. A person could have a new record, with nothing different in the customer table, except the unique ID, which is needed for the orders that they tie too.

There is no "master" record. Any one person could have three customer records. On the main entry form, if a user looks someone up, I need a way for them to be able to see what other records that one ties to. I also need them to be able to pick a customer record, and tie it to another. Tying any customer record to another must tie all related records to each other.

i.e. If record 1 is to get tied to 5, and 5 is already tied to 6 and 7, 1 is them also tied to 6 and 7. Going to any of records 1, 5, 6, or 7 should show the list of the other three being tied to its record.

Does that make sense?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

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!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[lameid]

My best suggestion: get better data. Hopefully you have some sort of relationship with whoever provides the data and you can get them to give it to you in a more useful format.

Ultimately you are in trouble when you have to identify similar duplicates.

Is Jane Doe at 123 Main Street the same Jane Doe at 1 Seasme Street way? Maybe but maybe not. Jane Doe may just be a really common name.

What you would do if you could identify duplicates is add the same table twice to a query. Then compare the values between the two however you want to identify duplicates. Then just append the key values to your tblRelatedCustomers. I would use criteria where the key between the two 'tables' is not equal to each other to keep records with the same key out as this only leads to uncessary records. If your query is good enough, you could use it instead of making a table.

 

[misscrf]

That is not an option. I wouldn't be asking the question if it was.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Darrylles]

Hi Miss,

Then populate the tables manually - that is your ONLY other option.


Regards,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[misscrf]

My boss helped me figure it out. Thank you for your responses. He created a query that gave the min number and grouped on the customer name and address1.

Then in a new query, he linked that query to the custoemr table to give all the relations.

I was able to append that to the related table and now I am all set!

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot