Using Like in query

[Craig]

Hello. I'm using Access 2k.

I have a table that contains a field with single parts of a company name
that I would like to compare to another table's field values that contain
full company names.

For example: the first table has the word "Interiors". Table 2 has a field
that contains many company names that contain this word, like "Al's
Interiors", "ACME Interiors and Design, Inc.", "Alpha Interiors and
decoration", etc.

Can you do a "like" join on two tables, or is this something I'm going to
have to do in VBA??

Thanks.

 

[Allen Browne]

1. Create a query using Table1 and Table2, joined on the 2 fields.

2. Switch your query to SQL View (View menu.)

3. Edit the FROM clause, changing = to Like and plus wildcards.

You will end up with something like this:
SELECT Table2.*
FROM Table2 INNER JOIN Table1
ON Table2.Company Like "*" & Table1.KeyWord & "*";
Access won't be able to show you this query in design view again, and you
will not be able to edit the results.

Another alternative is to create the query with the 2 tables, but without
any join between them. In the Criteria row under CompanName, enter:
Like "*" & [Table1].[Keyword] & "*"

Yet another alternative would be to base the query only on Table1, and use a
subquery in the WHERE clause. If that's new, see:
http://allenbrowne.com/subquery-01.html

 

[Craig]

Thanks, Allen
--
Craig

1. Create a query using Table1 and Table2, joined on the 2 fields.

2. Switch your query to SQL View (View menu.)

3. Edit the FROM clause, changing = to Like and plus wildcards.

You will end up with something like this:
SELECT Table2.*
FROM Table2 INNER JOIN Table1
ON Table2.Company Like "*" & Table1.KeyWord & "*";
Access won't be able to show you this query in design view again, and you
will not be able to edit the results.

Another alternative is to create the query with the 2 tables, but without
any join between them. In the Criteria row under CompanName, enter:
Like "*" & [Table1].[Keyword] & "*"

Yet another alternative would be to base the query only on Table1, and use a
subquery in the WHERE clause. If that's new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello. I'm using Access 2k.

I have a table that contains a field with single parts of a company name
that I would like to compare to another table's field values that contain
full company names.

For example: the first table has the word "Interiors". Table 2 has a
field
that contains many company names that contain this word, like "Al's
Interiors", "ACME Interiors and Design, Inc.", "Alpha Interiors and
decoration", etc.

Can you do a "like" join on two tables, or is this something I'm going to
have to do in VBA??

Thanks.