Doing a "begins with" type query with 2 tables

[mdgibbs88]

Hello all,

I have 2 tables. Table A contains a list of employee names from an outside source. Table B contains a list of names from HR. I would like to compare the list of names from Table A to the list in Table B, but I would like to match on a "fuzzy" lookup. Example:

Table A LastName = Smith Table B LastName = Smith (MATCH by joining)
Table A LastName = Smith Table B LastName = Smith Jr (no MATCH using joins)

Is there a way to do a "like" against 2 tables? I tried the attached and of course it did not work.

Thanks in advance!

 

[namliam]

Well a like would need a wild card.... like "*" & Yourfield &"*"
also you need to add the table to the query....

 

[plog]

What about all the Bobs, Betsys, Daves and Shellys of the world? And those are just the false negatives. The false positives include Louann, Edwina, Rutherford, Paula and any name where the first two letters are 'Jo'.

My point is, what you want to achieve is not only not 100% possible with computers, but very tricky even for the portion you can accomplish with them. One of my side gigs is cleaning up mailing lists and often that includes just this issue. And to achieve it I create what I call 'match-fragments' and then run a series of queries to identify possible matches.

For example, let's say I have William Anderson at 1234 Dr. Martin Luther King Jr. Blvd in Memphis, TN, 38117. I would create a query on each list I want to match, then create calculated fields that take parts of each piece of data and merge them with another piece:

Name_Frag (first 4 characters of first and last names): WillAnde
Addr_Frag (first 6 characters of street address and 3 of zip): 1234 Dr381
LastCity_Frag (first 4 characters of last name and 5 of city): AndeMemph

etc.

Then, I take those 2 queries and build another query, matching them on various fragments. Again, not 100% effective and requires me to verify, but it makes my life simpler and matching more effective.