How to query for descrepancies with two tables?

[ga1peach]

Question about using a select query to view data

If I have two tables (table 1 is MyCustomers, table 2 YourCustomers). I want the query to show MyCustomers and YourCustomers records with descrepancies. For example, MyCustomer shows Billy Smith but YourCustomer shows Billie Smith. I need to see both names. How do I do this.

 

[johnherman]

If you do not have a common key, what you are describing is an Artificial Intelligence type process which many vendors sell for big bucks.

If you have a common key (phone number?), you can simply do a string compare of the two customer names. You may have other key values such as SSN or EIN.

Otherwise, you are in the situation of buying or building software which uses "Fuzzy Logic".

Essentially, the software will try to match customers based on a point system with automatic acceptance beyond a specified (moveable) threshold value.

For instance, we have a customer named Bill Smith and another named J. William Smith and another named William J. Smith.

Process routines award (say) 10 points for the last name match. Next, a smart routine awards, say, 5 points because it recognizes that Bill and William are the same and that some people go by their middle name not their first name.

Next, it may award an additional 10 points for a zip code match, 15 points for a zip+4 match, 2 points for a state match, 8 points for a city match, 5 points for a street number match, 10 points for a street address match, etc.

The automatic threshold could be set as low as 25 points or as high as 50 points. Somewhere below the automatic match is the "gray area" where a skilled worker needs to examine the two (or more) records.

Anyway, you get the idea. And as I mentioned, several companies sell products which do this kind of stuff. Group 1 Software, Ascential's Quality Manager, Trillium, etc.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright