Merging two customer datasets where it may have the same customer but with differe...

[machumpion]

I have two tables, each with customer sales data where the same customer may exist in both data but under a different name (i.e. McDonalds #194 -USA vs #194 McDonalds,USA)

Is there a simple, straightfoward way to merge records that belong to the same customer into a table?

I'm baffled

 

[sneuberg]

Shouldn't be a problem is you can state the rules that determine if the customer is the same customer. If you have definite rules, e.g. same addresses, let us know what they are and I'll propose a solution.

If there are no definite rules I wrote some code that compares fields for the number of words that match which might help you set up something to do this manually. If you're interested let me know.

 

[plog]

No.

You might be able to find some code to do a fuzzy match to show that "McDonalds #194 -USA" is a 87% match to "#194 McDonalds,USA", but that same code is going to show that "McDonalds #194 -USA" is a 95% match to "McDonalds #591 -USA".

Here's my advice, let's call your lists A and B. You should add an autonumber primary key field to called [lid] to A and a numeric field to B called [dupeid]. Whenever you find a record in B that is a duplicate of one in A, you will add the [lid] value to the [dupeid] field in B, thus marking it as a match. To achieve this you should try and find (or write) code that makes those fuzzy matches I talked about in the first paragraph. That query would identify possible dupes, it would be up to an actual human to review all the possibilities to manually mark them as such though.

 

[machumpion]

thanks everyone for their input, i haven't had time to tackle this, i'm going to research the ideas pitched here and try to implement them before asking for more help. Thanks!

 

[machumpion]

Hi everyone,

My attempt is a complete failure. Please help. Right now, I have both lists in Excel, but I think I can put them into Access to implement whichever a solution is designed for.

TABLE 1
In Customer table 1, I have the [Customer] column(excel: A), it is the name, i.e. McDonalds 99

I also have an [Invoice to] column (excel: B). It consists of the name and address in the same cell, i.e. McDonalds 99 123 Fake Street, Fakeland, ON 55555

TABLE 2
StoreName (excel: J) i.e. McDonalds 99 US
Address (excel:K) 123 fake st
City (excel:L) ON
ZIP Postal (excel:N) 55555

Anyone of the address fields for TABLE 2 can sometimes be blank for a store, but the store name will always be there and it has a similar store name from table 1 with a matching address that may be formatted slightly different (if address data exists).

I need to somehow link the same stores from the two tables together.
When store data from each source comes in each month, i'd like to easily be able to append the existing database with it. Table 2 data is from an access database with the name of the store as the PK.
Thanks!

 

[plog]

With each post you crack open the door a little wider and this thing reveals more and more complexity. I'm afraid what you are asking you to help you do isn't what you actually need to do. You've also genericized it so much its hard to reference each dataset.

With that said, since this will be a recurring task, you will ultimately need to make a matching table. You are going to have Table1 with its name ('Fake Store #1') and TAble2 with its name ('Fake Shop #1') which you need to line up. So you need a table that keeps track of what the matches are, something like so:

CustomerLinks
Table1Name, Table2Name
Fake Store #1, Fake Shop #1
McDonalds On Main, 10 Main Street McDonalds
Larry's Shrimp, Larry's Shrimp


You store the value from Table1 in one field, and then the matching name in the Table2 field. That way you can resuse your matches montlhy.

Again, though, your only letting us see glimpses of your entire process, there might be a better way than what you have envisinoed.

 

[jdraw]

What you re experiencing is a symptom of larger underlying issue.
It appears that you have happened upon a difference in Company Name that is causing you grief in a business sense --Who belongs to this invoice.. If you are not sure of which Customer is which, then you have a major issue. A viable business who sells/invoices and is paid by Customers will not remain viable indefinitely, if it is uncertain of whom to invoice.

The whole idea of your database is to support your business. And in this case it is to identify and invoice companies for services/products. You have to get unique identifiers on your Customer records, and use/maintain them accurately and consistently in all of your processes.

You and/or your colleagues/bosses/designers will have to identify WHAT exactly idenfies a Customer; then adjust your database/table(s) etc accordingly.

I have worked in orgs where they merged multiple, disparate systems involving companies, producer, fabricators, vendors, suppliers.... The approach was to look for common strings in name(legal, operating, alias), address street, city, postal code/zip, phone number.. The idea was to apply a weighting factor to each of the terms of comparison. If the final weight was above X, then these were accepted as same with x % confidence. If the results were less than X, they were considered not the same. In many cases companies were phoned to confirm information.

Once you get the unique identifiers assigned, it is critical to keep them current.

I don't know what steps will apply to your particular set up, but I wanted to highlight why getting customers uniquely identified in your business and operational databases was important.

Good luck with whatever strategy you choose.