Keyword query across mutliple fields - please help?

[Bill D]

Hello All,

I am trying to implement a flexible query form by which the user can select
multiple fields or all fields to be searched and create a list of
values("keywords") to search on.
I already have my form with combo boxes that allow the user to select the
fields they want included in the search.
I also have a list box and text box by which the user can add values to the
list. This list is the list of values (keywords) to be look for in each
field when the query is run.

I was thinking initially to run a query on each field selected using some
thing like:

Select * from mytable where field1 IN (value1, value2, value 3 ,etc...)

I would dynamically built the SELECT using IN queries within a UNION query
to put the results all together.

There must be a better way to do this. I am fair with my SQL skills but
certainly no guru.

Any help would be greatly apperciated - article on keyword search
implementations, etc...

Thanks in advance.

Bill

 

[Michel Walsh]

Hi,



SELECT DISTINCT pk
FROM ( SELECT pk, field1 FROM myTable
UNION
SELECT pk, field2 FROM myTable
UNION
...
UNION
SELECT pk, fieldN FROM myTable ) As x
WHERE field1 IN( value1, value2, ..., valueM)



where field1 to fieldN are the "selected" fields, pk is the primary key.


You may have to store the inner UNION query ( ie. make a save query from
what is inside the parentheses, and use the query name immediately after the
FROM keyword, instead of the actual version).


You may use many LIKE instead of IN, if the values have "wildcard patterns"
rather than an exact match required.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


If you use MS SQL Server, try Full Text Search.


Hoping it may help,
Vanderghast, Access MVP