I have an MS Access table which I would like to be able to search. The results should include exact matches for all of the search criteria AND ALSO exact matches for any of the search criteria as long as at least 3 or more match.
If I search for
: A : B : C : D : E :
: 1 : 2 : 3 : 4 : 5 :
The results returned will include all of the above table EXCEPT for
: 1 : - : - : 4 : - :
because it doesn't match on 3 or more search criteria.
My SQL query (which is dynamically created via ASP depending on whether a column is searched upon or not) currently looks like this:
SELECT * FROM Table1 WHERE (Table1.A = 1) OR (Table1.B = 2) OR (Table1.C = 3) OR (Table1.D = 4) OR (Table1.E = 5)
This of course returns every result in the above table. If I use AND (instead of OR), only exact matches are returned. How can I modify it so that at least 3 of the search criteria matches the records?
+ iif(E=5,1,0) >= 3
the fact that you generate the sql with asp based on how many fields are being searched means that you can alter the value 3 to whatever you wish, including situations where the user only enters 1 search value