Perform Search

[sebell]

Hello,
I am trying to create a trouble shooting database where if someone types in a sentence, the database can pick up on key words and bring back all criteria with that or those key words.
In other words, I have a text box txtQuestion which the user types in the question. When the user clicks on the command button, how do I search based on every word used - in any order - and maybe put this into however many answers I get back.

Thank you in advance for you help and I hope this makes sense to you.

 

[wemeier]

One way you could do it is to parse the question into words, then build a union query that searches the answers using the Like operator on each word. This may be time-consuming depending on how many words are in the question and how many answers are in the database. For example, if the user enters a question such as "trouble connecting to remote desktop" you would (programatically) build a union query like:

SELECT Answer FROM tblAnswers
       WHERE Answer Like "*trouble*"
UNION SELECT Answer FROM tblAnswers
       WHERE Answer Like "*connecting*"
UNION SELECT Answer FROM tblAnswers
       WHERE Answer Like "*to*"
UNION SELECT Answer FROM tblAnswers
       WHERE Answer Like "*remote*"
UNION SELECT Answer FROM tblAnswers
       WHERE Answer Like "*desktop*";

Or, you can put an AutoID field on each answer in your database, then parse each word in the answer into a separate database table that holds each word and the AutoID of the answer that contains the word. You could then parse your question into a temporary table, then do an INNER JOIN on the question words table against the answer words table and return the AutoIDs and the answers that they point to. A by-product of this would be the ability for you to make the query an aggregate query. This would not only return the answers but would also count the number of question words that each answer contains.

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[willir]

search based on every word used - in any order - and maybe put this into however many answers I get back

In addition to using Union queries, you also have to "parse" the input, perhaps with VBA coding, and search each "word".

Also, be aware that the Union query is limited in that it only works on mutiple tables provided the retrieved fields are of similar data types.

Richard