Search text fields for keywords given a priority list

[gotmitch87]

I have a table (TextTbl) containing several text fields, and a table containing a list of keywords (KeywordTbl), ordered by 'priority'. I would like to populate a TextTbl.Keyword field within each record to contain the highest-priority keyword found within its text fields.

I have written the following query:

UPDATE KeywordTbl, TextTbl 
SET TextTbl.Keyword = [KeywordTbl].[Keyword]
WHERE (TextTbl.TextField1 & TextTbl.TextField2 & TextTbl.TextField3 Like "*" & [Keyword].[Keyword] & "*")

Unfortunately, this method seems to simply populate the TextTbl.Keyword field with the first Keyword.Keyword that is found when reading through the text fields from left to right.

For example:
KeywordTbl
1 Score
2 Four
3 Seven

TextTbl
"Four score and seven..."

The above query will return 'Four' as TextTbl.Keyword . I would like it to return 'Score', because score is higher on the priority list.

Thank you in advance for your help and please let me know if I can better clarify my situation.

[ADezii]

Just subscribing for now...

[ADezii]

I feel as though the Basic Logic is flawed, since the combination of TextField1 and TextField2 could yield a High Priority Keyword Match on 'Score' when none exists (end of Field1 [s], beginning of Field2 [Core]):

TextField1                TextField2
The sun always sets       Core values are essential

The sun always sets-------------Core values are essential

For starters, the concatenation is OK, but the 3 Fields need to be Delimited in order to prevent the above from happening:

UPDATE KeywordTbl, TextTbl SET TextTbl.Keyword = KeywordTbl.Keyword
WHERE (TextTbl.TextField1 & "|" & TextTbl.TextField2 & "|" & TextTbl.TextField3 Like "*" & KeywordTbl.Keyword & "*");

[gotmitch87]

Good point. I'll be sure to implement that.

I'm still working to figure out the other problem of populating [TextTbl].[Keyword] with the keyword of highest priority. I'm beginning to think I may have to use VBA, but am unfamiliar with how to use it, so it's a bit overwhelming at the moment!

[Rabbit]

Set it up as a subquery so that you can order by the priority field.

[ADezii]

@gotmitch87: A VBA/SQL solution would be relatively simple to implement, but Rabbit's Sub-Query approach would be much more efficient. Sorry, SQL is not exactly my strong point, but I will continue to monitor this Thread until a solution (purely SQL) is arrived at.

[gotmitch87]

Thank you guys so much for the responses. It turns out I found a solution (albeit a bit convoluted) just before I received Rabbit's response.

I created an additional field in TextTbl called 'KeywordID' to store the priority of the keyword. Then I initialized that field to max(Keyword.ID) +1. The following query ensures that the TextTbl.Keyword field is populated with the keyword of highest priority (lowest KeywordID).

UPDATE KeywordTbl, TextTbl
SET TextTbl.Keyword = [KeywordTbl].[Keyword], TextTbl.KeywordID = [KeywordTbl].[ID]
WHERE (((InStr([TextTbl].[TextField1] & " " & [TextTbl].[TextField2] & " " & [TextTbl].[TextField3],[KeywordTbl].[Keyword]))>0) AND ([KeywordTbl].[ID]<[TextTbl].[KeywordID]));

The code works but is likely not as efficient as the sub-query approach Rabbit suggests. If I have time to go back and re-visit this later in the week I will work toward that solution and will be sure to update this thread with any progress.

Thanks for your help!

[Rabbit]

Basically, it would be something like this

UPDATE t
SET TextTbl.Keyword = (
   SELECT TOP 1 Keyword
   FROM KeywordTbl
   WHERE InStr(t.TextField1 & " " & t.TextField2 & " " & t.TextField3, Keyword) > 0
   ORDER BY ID
)
FROM TextTbl AS t