Prev: Ranking query
Next: how do i use input parameters with criteria
From: TomC on 3 Jul 2008 16:04
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????
From: John Spencer on 3 Jul 2008 19:14
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
'====================================================


TomC wrote:
> 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????
 | 
Pages: 1
Prev: Ranking query
Next: how do i use input parameters with criteria