Function to search Field for any word contained in a separate table 1

[Accel45]

The problem is due to a text field named "Description" which allows for non-standardized entries. (I have NO control over this)
This field "Description" is the basis for the record being placed in a category.
Most of the time "Description" will contain some variation of the words listed below but the list will likely grow.

I am using the following in a query to find words within "Description". I then use the DSearch result to look up the appropriate Category in a separate table.

DSearch: IIf([Description] Like "*Record*","Record",Null) & IIf([Description] Like "*Security*","Security",Null) & IIf([Description] Like "*Deposit*","Deposit",Null) & IIf([Description] Like "*Shred*","Shred",Null) & IIf([Description] Like "*Monumental*" Or [Description] Like "*Surcharge*","Surcharge",Null) & IIf([Description] Like "*Research*","Research",Null)

While this does work it has become apparent that the list of words being searched for will continue to grow. As a result, DSearch will need to be edited and may become too long and more important, other users will not be able modify the DSearch Code.

I have tried placing the words to search for in a table;

SELECT tblSearch.DSearch, MainList.Description
FROM MainList, tblSearch
WHERE (((MainList.Description) Like "*" & [tblSearch].[DSearch] & "*"));

In this query tblSearch contains the list of words to search for.

But this narrows the results of the query which I do not want to do, I just want DSearch to show what word was found in “Description”.

If I leave off the WHERE Clause then the query returns a number of rows that far exceeds the actual number of rows in the MainList Table.

I am open to any ideas or suggestions for an alternative method for:

DSearch: IIf([Description] Like "*Record*","Record",Null) & IIf([Description] Like "*Security*","Security",Null) & IIf([Description] Like "*Deposit*","Deposit",Null) & IIf([Description] Like "*Shred*","Shred",Null) & IIf([Description] Like "*Monumental*" Or [Description] Like "*Surcharge*","Surcharge",Null) & IIf([Description] Like "*Research*","Research",Null)

Thank you
Accel45

 

[MajP]

TblDescriptions

[tt]
ID	Description
1	There is a red dog
2	There is a blue fish
3	Blue and red lights
4	Ocean is green
5	Green, red, yellow, blue
[/tt]

tblLookup

[tt]
ID	Word
1	Red
2	Blue
3	Green
[/tt]

qryFindWord

SELECT 
  tblDescriptions.ID, 
  tblDescriptions.Description, 
  tblLookup.Word
FROM 
  tblDescriptions 
INNER JOIN 
  tblLookup 
ON 
 tblDescriptions.Description like "*" &  tblLookup.Word & "*"
Order By 1

[tt]
ID	Description	                Word
1	There is a red dog	         Red
2	There is a blue fish	         Blue
3	Blue and red lights	         Blue
3	Blue and red lights	         Red
4	Ocean is green	                 Green
5	Green, red, yellow, blue	 Green
5	Green, red, yellow, blue	 Blue
5	Green, red, yellow, blue	 Red
[/tt]

 

[dhookom]

You can use something like the following which searchs the ContactTitle field of Customers (in Northwind) for the [Title] field in a table of title [tblTitle]. It's only going to display the first (alphabetically).

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.ContactTitle, Customers.Address, 
(SELECT TOP 1 Title FROM tblTitles WHERE Customers.ContactTitle like "*" & title & "*" ORDER BY TITLE) AS FirstTitle
FROM Customers;

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I like MajP's solution but I would make it a LEFT JOIN so every record is displayed as well as multiple "Word" values.

SELECT 
  tblDescriptions.ID, 
  tblDescriptions.Description, 
  tblLookup.Word
FROM 
  tblDescriptions 
LEFT JOIN 
  tblLookup 
ON 
 tblDescriptions.Description like "*" &  tblLookup.Word & "*"
Order By 1

Duane
Hook'D on Access
MS Access MVP

 

[Accel45]

Thank you dhookom, that worked great.