Joining tables based on Key words

[heather]

Hi - I am trying to find a way to return rows from Table B that contain
the key words anywhere in the column that I store in table A.

TableA (key words)
Jim
John
Zephyr

Table B (data to be returned)
M. Jim Jones
John Smith
Ted Zephyr

I thought of building a huge 'where' statement, but Access says its too
complex. Table A has more than 800 entries and is growing. It was
working just fine when I limited it to a dozen or so like below.

SELECT dbo_Contact.*, dbo_Contact.Company
FROM dbo_Contact
WHERE (((dbo_Contact.Company) Like "*JIM*" Or (dbo_Contact.Company)
Like "*john*"));

Can Anyone think of how to do this effectively and repeatedly? Is
there such thing as a JOIN LIKE or is there a way to build the where
query where access can handle the volumne?

Thanks

 

[Smartin]

Hi - I am trying to find a way to return rows from Table B that contain
the key words anywhere in the column that I store in table A.

TableA (key words)
Jim
John
Zephyr

Table B (data to be returned)
M. Jim Jones
John Smith
Ted Zephyr

I thought of building a huge 'where' statement, but Access says its too
complex. Table A has more than 800 entries and is growing. It was
working just fine when I limited it to a dozen or so like below.

SELECT dbo_Contact.*, dbo_Contact.Company
FROM dbo_Contact
WHERE (((dbo_Contact.Company) Like "*JIM*" Or (dbo_Contact.Company)
Like "*john*"));

Can Anyone think of how to do this effectively and repeatedly? Is
there such thing as a JOIN LIKE or is there a way to build the where
query where access can handle the volumne?

Thanks

Not sure how efficient this will be, but it should work. In the
following, Keywords = your TableA:

SELECT Contacts.*
FROM Keywords
INNER JOIN Contacts
ON Contacts.COMPANY LIKE "*" & Keywords.KEYWORD & "*";

 

[David Cox]

stick the two tables in the design window, unjoined. I used Name1 in one
table, searched by sel in another.

under criteria for name something like:

Like "*" & [sel] & "*"

SQL looks like:

SELECT Table1.name1
FROM Table1, T_sel
WHERE (((Table1.name1) Like "*" & [sel] & "*"));

 

[heather]

David thank you - your query worked well...thanks

smartin - I had an issue with syntax with your suggestion but
appreciate your suggestion. Heather