Results 1 to 4 of 4

Thread: Query Problem

  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Smile Unanswered: Query Problem

    Hi everyone,
    I have the following problem:
    I have a database containing products and ingredients that are used to produce those products.
    The ingredients used for one product are put in one field (just one - don't ask me why;-), separated by spaces like this:
    -----------------------------------------------------------------------
    Field_Ingredients {Ingredient1 Ingredient2 Ingredient3 Ingredient4 ...}
    -----------------------------------------------------------------------
    My task is to query for a product that contains particular ingrediants.

    To find every product that contains ALL desired ingrediants I use a form /for example Form1/ with a few unbound fields /Field1, Field2 .../ and i I set the following criteria in the field Ingrediants of the query:
    -----------------------------------------------
    Like "*" & [Forms]![Form1]![Field1] & "*" And Like "*" & [Forms]![Form1]![Field2] & "*" And Like "*" & [Forms]![Form1]![Field3] & "*"
    -----------------------------------------------
    To find every product that contains ANY of the desired ingrediants i use the same criteria, replacing AND with OR.

    In the real life though, there are rarely products that contain all the desired ingredients and on the other hand there are many products that contain one or two desired ingraqdients.
    So what I want to do is to find those products that contain as much as possible of the desired ingrediants (for example 4 of 5). I'd like to display the top 5 (or 10 or 15...) and ONLY IF IT'S POSSIBLE the degree of concurrence (4/5 or 85%)

    I'll appreciate any advice and help!
    (please excuse my poor english ;-)

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    Bonjour
    Juste une idée...
    Build your query so
    SELECT
    IIf(InStr(1,[Champ],"Test1")>0,1,0) AS Pos1, IIf(InStr(1,[Champ],"Test4")>0,1,0) AS Pos2, IIf(InStr(1,[Champ],"Test2")>0,1,0) AS Pos3, ...
    [Pos1]+[Pos2]+[Pos3]... AS Expr1, Table1.Champ
    FROM Table1 Where Champ Like etc...
    Expr1 give the number of found words. The problem, you cannot sort out directly this request by Expr1.
    It is necessary to build an other query allowing to sort out the result of the first

  3. #3
    Join Date
    Sep 2004
    Posts
    3

    One more question

    THANK YOU Jepi
    your query works just fine ... but I have one more question (If I'm not bothering too much ;-)
    I build a query just like you suggested and it displays all the records of my DB with field Expr1 counting the number of desired ingredients that are present. Then I made second query that limits the results to my desire, sorts them etc.
    My question is: If I have over 80000 records in my DB isn't it "bad" to run every time a query that displays all the records? And if the answer is yes, is there a way to limit the results of the first query? (When I tried to set an additional criteria for Expr1 in the first query it stops working!)

  4. #4
    Join Date
    Sep 2004
    Posts
    161
    The query proposed poses indeed the problem of the responsetimes. You can test:
    1 query Clause WHERE
    2 query count the words
    3 query sort
    what will be can be faster, but not miracle....
    On the other hand which is the problem with the additionel criteria ?

Posting Permissions

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