Simple match query where T2 values also exist in T1

[skydivetom]

I need assistance with creating an "IN" query based on the following criteria:

- Need query output that matches string values that exist in Table_2 where values also exist in Table_1
- Table_2 values must be NOT case-sensitive
- Table_2 values must be based on whole word (e.g., Blue <> "bluebird")
- Table_2 values can be anywhere in the string of Table_1

Example:
- Proper matches are marked in green background color
- Incorrect matches are marked in pinkish background color



I'd welcome recommendations for setting up the SQL statement that outputs the following values:
1. black bear
2. blue whale
3. large red kangaroo
4. red fox
5. small silver fox

Thank you in advance,
EEH

[ranman256]

in a query, join the 2 tables on the field.
bring both fields down into the result grid. Only matches will show.

If you want to see what is IN table1 but NOT in table2,(or visa versa) then make and OUTER JOIN.
dbl-click the join line, select SHOW ALL in tbl1, SOME in tbl2. (or switch)

[skydivetom]

ranman256 -- based on the value set, I don't think the outer join will work.

For instance, T2 valueS "red blue white" AND "blue pen" do NOT equal "blue whale".

However, based on the T2's keyword "Red" OR "blue" OR "white", "blue" does exist in "blue whale"; therefore, I want to output that value. Same principle applies to "Blue Jacket".

Ultimately, I need to display 1 match (blue whale) for "Red blue white" AND "Blue Jacket".

Similarly, there are additional findings for "Red blue white" based on "red". It also should pick up "large red kangaroo" and "red fox".

So, again, the outer query will not accomplish this match. I you think otherwise, I'd welcome additional thoughts.

[orange]

Tom:

I put the distinct colors from table2 into an array. Then compare the (SPLIT) fragments in table1 to the colors.

Sub breakout()
          Dim v As Variant, s As String
          Dim colors(6) As String, i As Integer, j As Integer

10        colors(0) = "black"
20        colors(1) = "blue"
30        colors(2) = "green"
40        colors(3) = "red"
50        colors(4) = "silver"
60        colors(5) = "white"
70        colors(6) = "yellow"


          Dim rs As DAO.Recordset
80        Set rs = CurrentDb.OpenRecordset("Table_1")
90        Do While Not rs.EOF
100           v = Split(rs!keyword_animal, " ")
110           For j = LBound(v) To UBound(v)
120               For i = lbound(Colors) to ubound(Colors)
130                   If v(j) = colors(i) Then
140                       Debug.Print "Matches: " & v(j) & " and " & colors(i) & vbTab & " in " & rs!keyword_animal
150                   End If
160               Next i
170           Next j
180           rs.MoveNext
190       Loop

End Sub

Result of test:

Matches: black and black in black bear
Matches: blue and blue in blue whale
Matches: red and red in large red kangaroo
Matches: red and red in red fox
Matches: silver and silver in small silver fox

[CJ_London]

here is a way to do it with a counter table and a couple of small functions

The table (assumes you won't have more than 10 words, add more numbers if required)

num
0
1
2
3
4
5
6
7
8
9

the functions (in a general module)

Function ttlElements(s As String) As Integer


    ttlElements = UBound(Split(s, " "))


End Function


Function getElement(s As String, i As Integer) As String


    getElement = Split(s, " ")(i)
    
End Function

the query

SELECT DISTINCT Table_1.KEYWORD_ANIMAL
FROM Table_1, tblCounter, Table_2
WHERE (((tblCounter.num)<=ttlelements([keyword_animal])) AND ((Eval("'" & getelement([keyword_animal],[num]) & "' IN ('" & Replace([KEYWORD_COLOR]," ","','") & "')"))=True));

the result

KEYWORD_ANIMAL
black bear
blue whale
large red kangaroo
red fox
small silver fox

[skydivetom]

orange -- thank you for the VBA code. I apologize... I should have been providing more specific information. The data set is merely for illustration purposes. I merely wanted some example objects (e.g., blue ball pen) and illustrate that a string element (blue) needs to be found in the other data set. That said, hard coding colors (or all T2 terms) is not the desired option. In other words, Table 2 could include many string elements as, e.g., "injuries", "jobs", etc. You get the idea... I just want to be able to extract records from T1 where elements exist in T2.

//

CJ_London:

I love the dynamic approach!!! Very nice approach. Unfortunately, when I ran the query, I only got 2 matches. See attached JPG and ACCDB. Did I miss anything?

[CJ_London]

Did I miss anything?

yes - a 0 in tblcounter. Just add it in, order doesn't matter

[skydivetom]

CJ_London -- BRILLIANT!! Many thanks for this wonderful solution.