Using a table as query criteria

[Thomp]

I need to use the information in one table to filter data I have in
another table. The main problem I have is that names in the table need
to be identified using wildcards as their are a lot of varations of the
names and I can not control the data that is in this master table I
will be using. Does anyone know how to create a query using a table
and having each record looked up using these wildcards.

Example

Table 1 records...Jack..Susan...Bob..Jackson..
Table 2 records..Jack ..Bob

I want to query records in table one using data from table two. I need
all records where Jack is in the name. Thus the query should come back
with the results of Jack,Bob and Jackson..

thanks,
Bill

 

[Rick Brandt]

I need to use the information in one table to filter data I have in
another table. The main problem I have is that names in the table need
to be identified using wildcards as their are a lot of varations of the
names and I can not control the data that is in this master table I
will be using. Does anyone know how to create a query using a table
and having each record looked up using these wildcards.

Example

Table 1 records...Jack..Susan...Bob..Jackson..
Table 2 records..Jack ..Bob

I want to query records in table one using data from table two. I need
all records where Jack is in the name. Thus the query should come back
with the results of Jack,Bob and Jackson..

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.NameField Like "*" & Table2.NameField & "*"

You will not be able to build the above in the graphical query builder. You can
use that to build the desired query with a standard equi-join, but you will have
to switch to SQL view to make the change to the join as shown above.

 

[Thomp]

Thanks Ken..This seems to work great.

Bill:

Either of these should do it:

SELECT DISTINCT [Table 1].*
FROM [Table 1] INNER JOIN [Table 2]
ON [Table 1].[FirstName] LIKE "*" & [Table 2].[FirstName] & "*";

or this

SELECT DISTINCT [Table 1].*
FROM [Table 1], [Table 2]
WHERE [Table 1].[FirstName] LIKE "*" & [Table 2].[FirstName] & "*";

Ken Sheridan
Stafford, England

I need to use the information in one table to filter data I have in
another table. The main problem I have is that names in the table need
to be identified using wildcards as their are a lot of varations of the
names and I can not control the data that is in this master table I
will be using. Does anyone know how to create a query using a table
and having each record looked up using these wildcards.

Example

Table 1 records...Jack..Susan...Bob..Jackson..
Table 2 records..Jack ..Bob

I want to query records in table one using data from table two. I need
all records where Jack is in the name. Thus the query should come back
with the results of Jack,Bob and Jackson..

thanks,
Bill