sql - How to look for substring in string where substring is IN a seperate table on Access Query? -

first, here code:

select ssiinspectors.inspector, ssiinspectors.date_time_of_inspection ssiinspectors (((ssiinspectors.inspector) in (select [projectmanager].[lastname firstname] [projectmanager]))); 

essentially, trying find substring "lastname firstname" projectmanager table inside ssiinspector.inspector field.

for example, in [lastname first name] field of project manager table have following names

smith, jane kenny, mike 

and in ssiinspector.inspector field have:

"doe, johnsmith, janebob, billy"  

i want able pick "smith, jane" in query. tried using "like" function, not quite sure how use in particular query, or if should using wild cards?

you can try using following query:

select distinct t1.[lastname firstname] [projectmanager] t1 inner join [ssiinspectors] t2     on t2.[inspector] '*' & t1.[lastname firstname] & '*' 

however, data appears not normalized @ all. better have names on separate rows, , have separate columns first , last names.