query tables using multiple listed values from another table

[TomC]

We are trying to set up a query where we have a table with static data that
is all located in the same column and we want to use this data as a search
criteria for another table. We are NOT linking the tables at this point.

The goal is to be able to build a query that will use the static data from
table 1, which is listed in column format, and compare that data to a
specific field within another table. We want the query to result any record
that has the data from table 1 anywhere within the field being queried from
table 2.

here is what we have done so far:

SELECT *
FROM maintable
WHERE (((maintable.name) In (Select name from lookupfrom where name Like
"*"&maintable.name&"*")));

This will bring back exact matches where value 1 from table 1 matches value
1 in table 2 exactly space for space, word for word.

We are trying to get a result where value 1 from table 1 matches any part of
the field from table 2 and result in our query.

Lookupfrom table Maintable query result
cat cat cat (our
query works for this)
dog cat and dog cat and dog (will
not work for this?)
horse

Please Help????

 

[John Spencer]

Try the following

SELECT *
FROM maintable INNER JOIN LookupFrom
ON MainTable.Name Like "*" & LookupFrom.Name & "*"


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================