Similar lookup

[jenniferspnc]

There is a table with over 500,000 records (tbl_client). I have a list of
100 clients I imported into another table and need to see if they exist in
tbl_client. Problem is the naming convention of my clients may not be exact
match to how they appear in tbl_client. Is there a way to lookup based on
similarity (perhaps lookup based on the first 3 or so letters of each client
in client list)?

Example: My client list has ABC Corp but it may only appear as ABC in
tbl_client

tbl_client
client_name
client_address1
client_address2
client_city

tbl_listofclients
client_name

 

[Jeff Boyce]

People being people, there are SO many ways folks can spell a name (or
commit typos, or ...).

Yes, you can probably find a routine that compares the first three
characters (see Left() function), but what happens when you have:

Smith
Smithers
Smitty
Smilenski
...

One approach to resolving this is to apply USB technology (using someone's
brain). Sort them and scroll through, looking for things that seem the
same.

(... and you'd still probably need to do this after coming up with a routine
to automate a portion of this task!)

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

If you only want to compare the first three letters that is easy--

SELECT [tbl_client].[client_name], [tbl_client].[client_address1],
[tbl_client].[client_address2], [tbl_client].[client_city]
FROM [tbl_client], [tbl_listofclients]
WHERE [tbl_client].[client_name] Like
LEFT([tbl_listofclients].[client_name], 3) & "*"
ORDER BY [tbl_client].[client_name];