How to find words in a memo field with microsoft access

Question

Hi

This is more complex than I thought as I need to do it with VBA. So here is my problem:-

I have two tables in an MS Access DB, content and adjectives.

The content table has over 5,000 text snippet rows in a memo field (content) and a blank text field (adjective). The adjectives table has 120 adjectives with the field (adjective).

I need to check if the text snippet contains any of the adjectives and if so, add the adjective to the adjective field. If there is more than one adjective just add the first one.

Here's an example of the data:-

CONTENT

"The N8 camera is pretty amazing, will it bring the N8 full on sales success though alone? It's the only "wow" factor of the handset but other handsets have 720p that are also very good such as the Sony Vivaz which a family member has and the videos are pretty amazing from it too."

ADJECTIVES

afraid
agreeable
amused
ancient
amazing
angry
annoyed
anxious
arrogant
ashamed
average
awful
bad
beautiful
etc

How would I go about doing this is an update query or in VBA?

Thanks in advance for your help.

Jonathan

Answer 1

Perhaps:

SELECT a.Adjectives, b.Content
FROM A, B
WHERE b.Content Like "*" & a.[adjectives] & "*"

To update a third table:

INSERT INTO C (ContentID, ContentAdjective) 
SELECT b.ID, a.Adjectives
FROM A, B
WHERE b.Content Like "*" & a.[adjectives] & "*"