I am working on a query that needs to identify any records in a table that contain certain words defined by a reference table. In this case, it's a list of country names. The fields being searched are open text fields that contain more than just the country name, so i cannot create a join.



Additonally, after identifying the data, the query needs to also write a value in the results for which country triggered the match.

I have the criteria working based on the reference table with it looking for "Like *[table].[Countries]*", but for every match, it duplicates the row for each and every country appearing on my list.

Is there a way to get the proper value to be written to my final results instead of all the rows with the incorrect country value?