0

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.

rockies2
  • 1
  • 2
  • 1
    So you are looking for 'value' appearing in title (anywhere in title)? And by 'populated' do you mean query results, or an update? – tinazmu Mar 13 '22 at 02:25
  • And what if more than one record in table 2 has "officer"? Why not saving a primary key into table 1 instead of full descriptive text? Can run an UPDATE action SQL or VBA procedure. The UPDATE can use DLookup(). Or instead of actually saving into table 1, just calculate when needed. – June7 Mar 13 '22 at 02:46
  • @tinazmu Yes, I'm looking for 'value' appearing anywhere in the title. By populated I mean query results. – rockies2 Mar 14 '22 at 15:36
  • @June7 The expectation is that more than one record would have the values such as "officer". When trying to use DLookup(), I'm getting #Error as the result. – rockies2 Mar 14 '22 at 15:36
  • 1
    Table2 would have more than one record with "officer" in Title? And each could have different Level? And if there could be more than one "officer" record in Table2, which one should be matched to Table1? Should edit question to provide more representative sample data. If you don't want to actually populate field in Table1, then DLookup() would not be needed. Maybe this is what you need: `SELECT Table1.*, Table2.* FROM Table1, Table2 WHERE InStr([Title], [Value]) > 0;` – June7 Mar 14 '22 at 17:38

1 Answers1

0

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]&'*'")
tinazmu
  • 3,880
  • 2
  • 7
  • 20
  • Thanks @tinazmu , I see what you are saying. When running the query, I do see the results returning more than one Tbl row. Using the DLookup function may better to use. I've previously tried to create a DLookup function but kept getting a getting #Error as the result. – rockies2 Mar 15 '22 at 16:06
  • If you need assistance with dlookup itself, add your complete dlookup sentence to your question – tinazmu Mar 15 '22 at 20:51
  • I've updated the question to include my attempt for the DLookup query, hopefully this clears up my question. – rockies2 Mar 16 '22 at 14:39
  • Added dlookup suggestions – tinazmu Mar 16 '22 at 20:11
  • thanks @tinazmu i've created the queries and receive a "syntax error (missing operator)" message. This looks to be due to values have special characters (example = '/) within the title field in my data set. – rockies2 Mar 23 '22 at 16:52
  • How can we help without seeing your query? – tinazmu Mar 23 '22 at 20:04