If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query tables using multiple listed values from another table
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???? |
#2
|
|||
|
|||
query tables using multiple listed values from another table
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???? |
Thread Tools | |
Display Modes | |
|
|