Matching values from another table - access

Question

Hoping someone can help me on this. I have two tables where I am trying to grab a field from one table if it contains a value.

Table 1

Value      Level     
Officer    C-Level       
Exec       C-Level           

Table 2

Title              Level2     
Chief Executive    C-Level (desired output)      
Info Officer       C-Level (desired output)
Oper Officer       C-Level (desired output)

Essentially, in table 2, if the "Title" field contains a value from Table 1, then I would want the "Level" from table 1 to be populated in Table 2.

I am essentially looking for 'value' appearing anywhere in 'title'.

When trying to a Dlookup query, I see no results.

Level2: DLookup ("[Level]", "Table1", "[Title]" Like [Value])

I am missing something but not sure what.

Answer 1

As @June7 proposed, or:

SELECT *
FROM Tbl1 AS T1 inner join
Tbl2 AS T2
on T2.Title like '*' & T1.Value & '*'

But this query has a high chance of returning more than one Tbl1 row for any given Tbl2 row (hypothetical example: 'manager' would match both 'senior manager', and 'executive manager'), in these cases DLookup function returns the first occurrence.

For DLOOKUP you might use:

Level2: DLookUp("[Level]","Table1", "InStr('" & Table2.[Title] & "', [Value]) > 0") 

Or

Level2: DLookUp("[Level]","Table1","'" & Table2.[Title] & "' like '*'&[Value]&'*'")