Dynamic List of data to search on?

[Dominic H via AccessMonster.com]

I have a table (called TABLE A) which looks like this:
WORD | onum | nnum |
SCHOOL | 55569 | 99999 |
ENGINEER | 77888 | 99887 |
etc
etc
BASKET | 66666 | 44444 |

And I have a another table (called TABLE B) (in the same database) that looks
like
SUPPNAME | PostCode| and | so forth|
St JOESPHS SCHOOL | SL1 8UU | OTHER | STUFF |
BREAk ENGINEER LTD | TN1 8II | | |

I want to create a query that will look for all the rows in table B that
contain the words in filed Word of Table A.

Table A will be dynamic and can have its contents changed. (ie more WORD will
be added)

Of course, this may not be possible. I just want to use this in a query -
but will do regularly. The list of words will grow to a few hundred (it's
currently 50), so I can't really use individual queries for each word.

Looking forward to your kind reponses!

Dom

 

[Michel Walsh]

Hi,



.... records from TableB where TableB.SuppName contains at least one word
from TableA


SELECT DISTINCT TableB.*
FROM tableB INNER JOIN tableA
ON tableB.SuppName LIKE "*' & tableA.Word & "*"


or


SELECT TableB.*
FROM TableB
WHERE EXISTS ( SELECT * FROM TableA
WHERE TableB.SuppName LIKE "*' & tableA.Word &
"*" )





Note that the word Ho is considered included in School

and so, some "wrong" matches may occur. To remove these wrong matches, if
required, you can try:

( " " & TableB.SuppName & " " ) LIKE "* " & tableA.Word & " *"




where there is now an extra space with the wildcard * . That assumes there
is no other delimiters. If there are SuppName with other delimiters, like a
dot or a coma, as in "Gates, Bill", then it is still doable:



( " " & TableB.SuppName & " " ) LIKE "*[ ,.]" & tableA.Word & "[ ,.]*"



but that is becoming more and more complicated (take a look at operator LIKE
in the help file, for more details).




Hoping it may help,
Vanderghast, Access MVP

 

[Dominic H via AccessMonster.com]

Michel

Many many thanks for this - now I've seen it - it's just so obvious!!!!

Works nicely for me.

Nice to learn something on a Monday morning...

Kind regards

Dom

Hi,

... records from TableB where TableB.SuppName contains at least one word
from TableA

SELECT DISTINCT TableB.*
FROM tableB INNER JOIN tableA
ON tableB.SuppName LIKE "*' & tableA.Word & "*"

or

SELECT TableB.*
FROM TableB
WHERE EXISTS ( SELECT * FROM TableA
WHERE TableB.SuppName LIKE "*' & tableA.Word &
"*" )

Note that the word Ho is considered included in School

and so, some "wrong" matches may occur. To remove these wrong matches, if
required, you can try:

( " " & TableB.SuppName & " " ) LIKE "* " & tableA.Word & " *"

where there is now an extra space with the wildcard * . That assumes there
is no other delimiters. If there are SuppName with other delimiters, like a
dot or a coma, as in "Gates, Bill", then it is still doable:

( " " & TableB.SuppName & " " ) LIKE "*[ ,.]" & tableA.Word & "[ ,.]*"

but that is becoming more and more complicated (take a look at operator LIKE
in the help file, for more details).

Hoping it may help,
Vanderghast, Access MVP

I have a table (called TABLE A) which looks like this:
WORD | onum | nnum |

[quoted text clipped - 25 lines]

Dom