Filtering a table by using criteria in another table

[Gigi Other]

In an Access 2003 database, I have a table [ClientList] which lists
all of our clients. I have a second table [SearchTerms] which
consists of terms that I would like to filter out records from the
client list. I can't run a simple query because some of the search
terms may only contain one of the words in the client list (i.e. the
SearchTerm is "Bureau" and I want to filter out all clients with the
word "Bureau" in it.)

Is there a way to filter the ClientList table with where I want the
record to contain a term from the SearchTerms table?

 

[Bob Barrows]

In an Access 2003 database, I have a table [ClientList] which lists
all of our clients. I have a second table [SearchTerms] which
consists of terms that I would like to filter out records from the
client list. I can't run a simple query because some of the search
terms may only contain one of the words in the client list (i.e. the
SearchTerm is "Bureau" and I want to filter out all clients with the
word "Bureau" in it.)

Is there a way to filter the ClientList table with where I want the
record to contain a term from the SearchTerms table?

You could do something like this (it needs to be entered in SQL View -
Design View is not capable of displaying this type of join):

select c.* from clientlist as c join searchterms as s
on c.client like "*" & s.searchterm & "*"

The problem is if there is a possibility that a client could match multiple
search terms, you would get duplicate results. This can be avoided by using
"distinct":

select distinct c.* from clientlist as c join searchterms as s

on c.client like "*" & s.searchterm & "*"