Finding like values from one table in another.

[strangeryet]

I am working with 2 tables:
The first is a table full of names.
The other is also table full of names that I pull from a query which extracts just the last name.
Is it possible to list the records from table 1 that have a like match two those records in the query?

For example: the query would have several names returned
Jones
White
Green
...
If any of these names exist in Table 1 the record would display. I want to use the Like funtion something simiular
to: Where table1.Name Like '*[Query.Name]*'

I have had no success.
I figure I could write something in VB where I can loop
through and using a Instr function find the matches, but
I was hoping I could do it in a query.

Table 1
Brown Joe
Doe John
Jane Doe
Jane Smith
John Jones
Dave Joneson
Pam Greenidge

So in this case using the above query result the records displayed from this new query would be:
John Jones
Dave Joneson
Pam Greenidge

Because of the Jones and Green matches from the query.
Thanks

 

[PHV]

Where table1.Name Like '*' & Query.Name & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[strangeryet]

I have tried this:

SELECT tblTab1.Name,qrySplitOutName.NamePartCheck
FROM qrySplitOutName, tblTab1
WHERE (((tblTab1.Name) Like '*' & [qrySplitOutName].[NamePartCheck] & '*'));

It came back with incorrect results.

 

[PHV]

with incorrect results
Any chance you could post some sample values explaining us what is incorrect ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[strangeryet]

I beleive a null value was coming back from the query which was causing all records to be displayed. I think removing that corrected it. Thanks

 

[PHV]

Like this ?
SELECT T.Name, Q.NamePartCheck
FROM qrySplitOutName As Q, tblTab1 As T
WHERE Q.NamePartCheck Is Not Null AND T.Name Like '*' & Q.NamePartCheck & '*';

Or this ?
SELECT T.Name, Q.NamePartCheck
FROM qrySplitOutName As Q, tblTab1 As T
WHERE T.Name Like '*' + Q.NamePartCheck + '*';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886