Comparing fields in two tables using "Like" clause

[zirbj2724]

I want to compare and return selected rows from one table
in a select or make table query based on values in another
table using the "Like" clause. Example: First table
contains records where one field contains names like "XYZ
Financial Bancorp LTD" or variations or this name. In the
other table I have a list of different names
including "XYZ Financial". I want to use the "LIKE"
clause to find these records in the first table based on
this information. Please HELP.

 

[Brian Camire]

For starters, you might try a query whose SQL looks something like this:

SELECT DISTINCT
[Your Table].*
FROM
[Your Table],
[Your Other Table]
WHERE
[Your Table].[Your Field] LIKE "*" & [Your Other Table].[Your Field] & "*"
OR
[Your Other Table].[Your Field] LIKE "*" & [Your Table].[Your Field] & "*"

This will return records from "Your Table" where the "Your Field" in "Your
Table" contains the value of "Your Field" of any record in "Your Other
Table", or vice versa. For example, it will return the record like the one
in your example, since "XYZ Financial Bancorp LTD" contains "XYZ Financial".

However, it will not match records that you might want it to, like where the
value in "Your Field" in one table is "Financial Bancorp LTD" and in the
other is "Financial Bancorp Limited", since neither value is completely
contained within the other.

Hope this helps.