Text Field "Contains" Query - Using Another Table.

[andmunn]

Hello,

I have two tables:

1) Products Table
> txtProductName
> txtProductDescription

2) Hit Words Table
> txtHitWord

I want to query a field (txtProductDescription) in the Products Table to see if it contains any of the words in the"Hit Words Table" (txtHitWord).

How do i do this?

I can query the field (ProductDescription) manually using "[txtProductDescription] Like "*catfood*"" - but how do i get it to identify ALL the words in Hit Words table?

Thanks,
Andrew.

 

[VilaRestal]

Not necessarily the most efficient way to do it but this should work I think:

SELECT txtProductDescription FROM [Products Table] 
WHERE EXISTS (SELECT * FROM [Hit Words Table] 
             WHERE [Products Table].txtProductDescription 
             LIKE '*' & [Hit Words Table].txtHitWord & '*')

This might even work too (and would probably be more efficient if it does):

SELECT DISTINCT [Products Table].txtProductDescription FROM [Products Table] 
INNER JOIN [Hit Words Table] 
ON [Products Table].txtProductDescription LIKE '*' & [Hit Words Table].txtHitWord & '*'

To be honest I've never tried joining on a LIKE in Access but I think it can be done.

 

[andmunn]

Thanks so much for your help.

The first one did the trick - the second method, i got a "join not supported".

But again, thanks so much!

Andrew.

 

[VilaRestal]

OK, but I got a version of the second to work with the table names before the field names (I've edited it to include that). You might want to try that, I think it would be a lot quicker that way so if there's any chance of either table having a lot of records...

But either way, glad it worked.

 

[andmunn]

Thanks that one seemed to do the trick as well!

 

[casskrygier]

Hi - just joined to say thank you for this solution, it was also exactly something that I was looking for and did the trick nicely.

 

[MIAVAN]

This never worked for me unfortunately.

some of the txtproductdescription fields are continuous like
GOOGLE*ADWS82287718 485442*8690 05 MAY
and I can't separate the GOOGLE (as my hitword) from the description.

how do I get it to recognise the GOOGLE hitword in the sentence

and then if I have two of them with different dates: like so :
GOOGLE*ADWS82287718 485442*8690 05 MAY

Why won't it apply the hit word to both, only one?


Please help!