Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    24

    Unanswered: Exact and almost exact queries

    Hi,

    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.

    My table (named Table1) has five columns:

    : A : B : C : D : E :
    : 1 : 2 : 3 : 4 : 5 :
    : 1 : 2 : 3 : 4 : - :
    : 1 : - : - : 4 : - :
    : 1 : - : 3 : 4 : 5 :

    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?

    Thanks for your help.

    Happy Holidays
    Alski

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    single pass of the table --

    select *
    from Table1
    where
    iif(A=1,1,0)
    + iif(B=2,1,0)
    + iif(C=3,1,0)
    + iif(D=4,1,0)
    + 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

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Dec 2002
    Posts
    24
    Thank you Rudy for that very simple bit of code.

    There was another reply from someone (which mysteriously disappeared), but yours was by far the easiest.

    The example I gave was oversimplified and my actual problem consisted of 35 search criteria - so a single pass of the table using the IIF function was neat - and faster.

    I've since used the code in a database I maintain.

    Cheers
    Alski

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    it was me. When I saw rudy's elegant solution, I didn't want to waste your time rading my.....

    jiri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •