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)
the functions (in a general module)
Code:
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
Code:
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 |