Most efficient way to do this?

[mscertified]

I have a table of keywords with 3 columns T1, T2 and T3. The table has approx
250 rows.

I need to check the contents of a memo field to list all keywords found in
the text.
I only need to check complete words (bounded by spaces or punctuation
characters). I could do an INSTR 750 times which is likely to be quite slow.
Or maybe if I loaded every word in the memo field to a temp table and did
some kind of join?
Any ideas?

 

[John W. Vinson]

I have a table of keywords with 3 columns T1, T2 and T3. The table has approx
250 rows.

I need to check the contents of a memo field to list all keywords found in
the text.
I only need to check complete words (bounded by spaces or punctuation
characters). I could do an INSTR 750 times which is likely to be quite slow.
Or maybe if I loaded every word in the memo field to a temp table and did
some kind of join?
Any ideas?

It depends to some extent on the way in which data is entered into the memo
field. If a "word" is defined as a text string preceded and followed by a
blank, or at the beginning of the field and followed by a blank, or at the end
of the field and preceded by a blank, then a query

SELECT yourtable.memofield, <other fields>
FROM yourtable, Keywords
WHERE yourtable.memofield LIKE "* " & Keywords.T1 & " *"
OR yourtable.memofield LIKE Keywords.T1 & " *"
OR yourtable.memofield LIKE "* " & Keywords.T1
OR yourtable.memofield LIKE "* " & Keywords.T2 & " *"
OR yourtable.memofield LIKE Keywords.T2 & " *"
OR yourtable.memofield LIKE "* " & Keywords.T2
OR yourtable.memofield LIKE "* " & Keywords.T3 & " *"
OR yourtable.memofield LIKE Keywords.T3 & " *"
OR yourtable.memofield LIKE "* " & Keywords.T3

If words can be delimited by punctuation, new-line characters, tabs, etc. then
your query gets that much more complex!

John W. Vinson [MVP]

 

[missinglinq via AccessMonster.com]

Which, of course, is why Memo fields should be used as designed, to hold
memos/notes, and never used to store data which you may have to manipulate!