Problem using query to retrieve info from multiple tables using the"like" operator

[mschutt]

I have two tables within the same Access database.



Table 1 contains a records of various parts our customer sends to us that are available to quote on. The field in this table we are interested in is called "Description".

Table 2 contains keywords we use to sort out the records shown in table 1 so we don't have to view each record. Each keyword in table 2 is its own record. The field that contains the keyword is called "Search Key".

Basically, the function we are looking for is simple. Using the field "Search Key" entered into Table 2, we want the query to show us the records in Table 1 were the field "Description" contains the data in field "Search Key" in Table 2. It is important that the search does not simply look for an exact match. For example. the term rod may be entered in Table 2 as "Search Key". But in table 1, the use of rod may be used in many variables (ground rod, connecting rod, etc.) and we wish for the query to discover all occurrences.

I can not seem to get anything but equal to work. I researched wild cards, but that does not seem to work in this case since I am referring to another field opposed to a string of characters in the query.

Although not in proper format, I want the following to occur in the query: I want to setup a query to search Table 1 "Description" field using Table 2 "Search Key" field and show any records in Table 1 that CONTAINS the search key entered in Table 2 (not equal to, but contains). I can not seem to get it to work.


Any suggestions would be greatly appreciated.

[June7]

Try this test:

SELECT Description FROM table1, table2 WHERE Description LIKE "*" & [Search Key] & "*";

[mschutt]

Try this test:

SELECT Description FROM table1, table2 WHERE Description LIKE "*" & [Search Key] & "*";

Access did not like the formula, but this helped get me in the right direction and I got it working. With the tables added to the query in access, I added the fields I wanted in my query. For the criteria of the Description field I added the following to make it work:

Like "*" & [Table2]![Search Key] & "*"

I tried other variants and Access would not let them work. The above line worked exactly as planned. Thank you very much for your help!

[June7]

Interesting, my suggested syntax worked for me, without the tablename qualifier. Glad you have a solution.