Search Table2 for String from Table1?

[susor]

This SEEMS simple, and I know it can be done with TSQL in ASP because I have the working program in front of me. But ASP uses a strquotereplace function and that doesn't exist in MS Access SQL. Anyway, here is what I need to do ...

Table1 has a field called LastName. Table2 has a field called Name, such that:

Table1/LastName
Atkins
Benjamin
Carlson
Dawson

Table2/Name
Adams, Thomas
Benjamin, Mary
Christopher, John
Donaldson, Benjamin

What I need to do is SELECT records in Table2 where any part of the Name string matches to LastName in Table1.

In the example above, I would want two hits ... LastName "Benjamin" and Name "Benjamin, Mary" ... and LastName "Benjamin" and Name "Donaldson, Benjamin"

I've tried various combinations of = and LIKE and % and nothing will give me the two hits I want. Most give me zero hits.

Can anybody tell me how to code this seemingly simple statement to make it work properly? Thanks!

Scott

 

[dcx693]

Use something like:

SELECT Table2.Name FROM Table2 WHERE Table2.Name Like "*" & Table1.Name & "*";

 

[susor]

dcx693 -- That did it! Thank you so much! Sometimes its amazing how somebody else can look at something and see how it needs to be done instantly. Great job! Thanks again!

Scott

 

[dcx693]

No problem. I suggest you get yourself a good Access book like my favorite, Access 2000 Developer's Handbook. More than 1,500 pages of good stuff!

 

[AncientOne]

dcx's fix will work to extract any part of the search name from the Full name string. So, for example, it will return "Smith, "Sixsmith", "Naismith, "Harrington-Smith", "Smithson" for a search string of "Smith". It will also return a record if the first name matches the search string, eg if the search string is "Gordon", it will return "Gordon, John" and Smith, Gordon".

If this is what you want, I'll shut up, but post again if you need to have the search refined and one of us will make suggestions for implementing it.

 

[susor]

AncientOne -- Thank you for the addition. Actually I did need the different variations and from dcx's code I was able to expand my searches to what I needed. I guess I really just had a brain lock on getting the basic code snippet from my head (English) to the box (SQL).

Scott