cross matching two tables 1

[onressy]

Hi i have two table with the same column names, columns such as memberID, firstName, LastName, Zip, country, state

Table 1 has a list of information derived from clients signing up, list two is derived from another company. I need to count memberIDs that match in both lists. Because of those who sign themselves up, their firstName might be entered as Steve while the company list might be Steven. Another example might be Arthur J. compared to A. James
Because of the variations of first names and because last name might be hyphenated (because of marriage), hence our last name might be more updated thaen the company list. Now I do have zip/postal code which helps a lot.

Lately i've been matching based on first 3 letters of first name and first 3 of last name and first three of zip/postal code. I find cross matching with this critera results in less inaccurate matches. Any suggestion on how to do this better, or does anyone know of any cross matching software for sql or excel? Thanks

 

[AlexCuse]

Onressy - first of all, I feel your pain. This is one of the hardest things to do.

I think you should use first 5 for zip code. People are not very likely to get this piece wrong.

I have accomplisehd this before, needing to use a sequence of queries (with gradually lower matching criteria) to update data collected from a clients' internet form to the best of our ability. Because of passalongs, it was often very difficult to match back to where they came from (especially where our back end had slight differences from the user supplied company name or city).

Do you have individual name component fields (first, last, middle) or one full name?

Good Luck!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Catadmin]

If you have any third party tools, like Red Gate's SQL Data Compare, that might help you out.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[onressy]

Thanks to both of you!

AlexCuse "Do you have individual name component fields" - YES

Thanks for the trhird party tool suggestion Catadmin, i'll check that out

 

[AlexCuse]

Well that is good! It can be an even bigger nightmare with 'FullName' fields. Let us know how things work out for you!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gbaksh]

May be it is worth exploring Soundex and join it based on that?

For example, if you run the following in a query analyzer, you will see what I mean:

select soundex('steve'), soundex('steven')
select soundex('vic'), soundex('vik')

 

[AlexCuse]

I have found that Soundex also generates some false matches. I'm not sure what the names were, but I think they were surnames. Keep a close eye on it if you go this route.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[onressy]

Thanks again!

 

[onressy]

One more question, in the db there are some duplicates
Is there a way to use unique in the where statement or in a JOIN or for multiple coumn selections, i'm having probles trying it in all three place, thanks again

 

[Catadmin]

Use the DISTINCT keyword right after your SELECT statement. As long as every column you are pulling up matches in those dup records (meaning there isn't a column that has an A for one record and a B for the duplicate record), DISTINCT will weed out any duplicate columns.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[onressy]

soundex (difference firstname=4) isn't my best friend in this case, steve is also givin results of: Shadad, Saadet

I found more percision in:
Left(Member.FirstName,4) = Left(xMatch.[FName],4)

 

[onressy]

Thanks Catadmin!

 

[Catadmin]

You're welcome. @=)


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"