search text using a list of key words

[wdb294]

hi all
i need help, i am a fairly armature access user.
i need to find a way to get a query to search through a text field in a table
that has thousands of entries looking for keywords that are listed in a
separate table.
example:

table1: DELAY
DESCRIPTION DURATION
the DCL failed to operate 2
door cleaner jammed 10
FSP leaking 4
detected fumes 8

table2: KEY_WORDS
ID WORD GROUP
1 DCL DCL
2 door cleaner DCL
3 FSP FSP
4 fumes FSP

the query needs to produce the following result

DESCRIPTION DURATION GROUP
the DCL failed to operate 2 DCL
door cleaner jammed 10 DCL
FSP leaking 4 FSP
detected fumes 8 FSP

i achieved this result using an IIF statement and writing in all of the key
words into the code but i need to be able to let the user change the key
words hence the separate table.

 

[KARL DEWEY]

Try this --
SELECT DESCRIPTION, DURATION
FROM DELAY, KEY_WORDS
WHERE DESCRIPTION Like "*" & [WORD] & "*" OR DESCRIPTION Like "*" & [GROUP]
& "*" ;

 

[Marshall Barton]

i need to find a way to get a query to search through a text field in a table
that has thousands of entries looking for keywords that are listed in a
separate table.
example:

table1: DELAY
DESCRIPTION DURATION
the DCL failed to operate 2
door cleaner jammed 10
FSP leaking 4
detected fumes 8

table2: KEY_WORDS
ID WORD GROUP
1 DCL DCL
2 door cleaner DCL
3 FSP FSP
4 fumes FSP

the query needs to produce the following result

DESCRIPTION DURATION GROUP
the DCL failed to operate 2 DCL
door cleaner jammed 10 DCL
FSP leaking 4 FSP
detected fumes 8 FSP

i achieved this result using an IIF statement and writing in all of the key
words into the code but i need to be able to let the user change the key
words hence the separate table.

This kind of Join can not be done in query design view so
you have to work in SQL view:

SELECT table1.[Description], table1.Duration, Table2.[Group]
FROM table1 INNER JOIN table2
ON table1.Description Like "*" & Table2.Word & "*"

Note that you use of common words as field names may be a
problem, especially "Group", which is an SQL keyword and
"Description", which is a table property.