Count rows, convert text phrase to array, construct SQL query using array

[dougmcc1]

I'm trying to write a module in Access '97 and I need help with the following:

1. Counting the number of rows in a table and assigning the value to a variable. The macro would loop this many times.
2. This table only has one column and each row in this column contains a text phrase. I need to put each word into an array such as array("text", "phrase").
3. Using the array, I need to construct a query that uses "And Like" to search another table for all phrases which contain all the words in the array.

Any help is very much appreciated. Thanks in advance.

 

[Jon K]

I think it is easier to do it in a query.

In the attached database, I used the query:-

SELECT DISTINCT tblPhrases.ID, tblPhrases.Phrase
FROM tblPhrases, tblWords
WHERE InStr([Phrase],[Word])=True;
.

 

[dougmcc1]

Thanks for that, Jon. What you've provided isn't exactly what I'm looking for so let me provide some more specifics.

I'm trying to match phrases in two tables. I have one table which contains thousands of phrases and I have another table which contains a hundred or so new phrases. The new phrases might exist in both tables but the words in them may be in a different order. I don't want multiple phrases that contain the same words in a different order in my main phrases table.

For example, in the main phrase table I might have the phrase "bahamas real estate" and in the new phrase table I might have the phrase "real estate bahamas". I don't want to add the new phrase to the main phrase table because it's already there. But being that the words are in a different order, it makes it hard to design a query that knows the phrases are the same.

Thanks again.