Most efficient way to do this?

M

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?
 
J

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]
 
M

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top