Joining tables based on Key words

  • Thread starter Thread starter heather
  • Start date Start date
H

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
 
heather said:
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
Click to expand...

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 & "*";
 
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] & "*"));
 
David thank you - your query worked well...thanks

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