Results 1 to 10 of 10
  1. #1
    bednarol is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2018
    Posts
    3

    Query for multiple criteria for 1 field

    I am a newbie to databases and this seems simple yet I cannot figure it out. I have a normalized database, something like this
    PRODUCT INGREDIENT
    Product1 Ingredient1
    Product1 Ingredient2
    Product1 Ingredient3
    Product1 Ingredient4
    Product2 Ingredient1
    Product2 Ingredient3
    Product2 Ingredient4
    Product3 Ingredient1
    Product3 Ingredient3



    I want a list of products that contain Ingredient 1, 3 and 4. So I should get Product 1 and 2 and not 3. If I setup a query where the criteria for ingredient is In("Ingredient1","Ingredient2","Ingredient3"), it will list all three products. What is the correct criteria so that the result is:
    Product1
    Product2

    I also do not need to see in the query result Product1 three times. I tried to group the product field but that did not do the trick. Maybe once I have the criteria for the ingredient correct, the grouping will work? Thanks for any help!

  2. #2
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    change the Total to "where" instead of "Group By" for INGREDIENT in your query

    should look something like -

    SELECT Table1.product
    FROM Table1
    WHERE (((Table1.INGREDIENT) In ("Ingredient1","Ingredient2","Ingredient3")))
    GROUP BY Table1.product;

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sck version will still return Product3.

    You want only the records that have all selected ingredients? Selection of record is dependent on multiple data in other records. This is not a simple query. Requires nested query or maybe even custom VBA function.

    One way is to count how many ingredients are required and compare that to the count of ingredients returned in nested query.

    SELECT DISTINCT Recipes.Product, Query1.CountOfIngredient
    FROM (SELECT Recipes.Product, Count(Recipes.Ingredient) AS CountOfIngredient
    FROM Recipes
    WHERE (((Recipes.Ingredient) In ("1","3","4")))
    GROUP BY Recipes.Product) AS Query1
    INNER JOIN Recipes ON Query1.Product = Recipes.Product
    WHERE (((Query1.CountOfIngredient)=3));
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    bednarol is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2018
    Posts
    3

    change format?

    Quote Originally Posted by June7 View Post
    Sck version will still return Product3.

    You want only the records that have all selected ingredients? Selection of record is dependent on multiple data in other records. This is not a simple query. Requires nested query or maybe even custom VBA function.

    One way is to count how many ingredients are required and compare that to the count of ingredients returned in nested query.

    SELECT DISTINCT Recipes.Product, Query1.CountOfIngredient
    FROM (SELECT Recipes.Product, Count(Recipes.Ingredient) AS CountOfIngredient
    FROM Recipes
    WHERE (((Recipes.Ingredient) In ("1","3","4")))
    GROUP BY Recipes.Product) AS Query1
    INNER JOIN Recipes ON Query1.Product = Recipes.Product
    WHERE (((Query1.CountOfIngredient)=3));
    Thanks for advice! Would I be better off to change format of the source data? For instance:

    Product Ingredient1 Ingredient2 Ingredient3 Ingredient4
    1 x x x x
    2 x x x
    3 x x

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    No, I would NOT recommend that. Surely recipes can have more than 4 ingredients?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    Create another table called ingredient_requirements. Enter the three ingredients you require a particular product to have ALL OF:

    ingredient
    Ingredient1
    Ingredient3
    Ingredient4

    This SQL will return just Product1 and Product2: (the table with product and ingredients is called [products ingredients] and has 9 rows)

    SELECT [products ingredients].product
    FROM [products ingredients] INNER JOIN ingredient_requirements ON [products ingredients].ingredient=[ingredient_requirements].ingredient
    GROUP BY [products ingredients].product
    HAVING count([products ingredients].[product]) =(SELECT Count(ingredient_requirements.ingredient) AS [ingredient count] FROM ingredient_requirements);

    The last bit of SQL code returns the number 3 since you require 3 ingredients, and the part after HAVING only returns products with a row count of 3 from the join query preceding it.

  7. #7
    bednarol is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2018
    Posts
    3
    Quote Originally Posted by JSR View Post
    Create another table called ingredient_requirements. Enter the three ingredients you require a particular product to have ALL OF:

    ingredient
    Ingredient1
    Ingredient3
    Ingredient4

    This SQL will return just Product1 and Product2: (the table with product and ingredients is called [products ingredients] and has 9 rows)

    SELECT [products ingredients].product
    FROM [products ingredients] INNER JOIN ingredient_requirements ON [products ingredients].ingredient=[ingredient_requirements].ingredient
    GROUP BY [products ingredients].product
    HAVING count([products ingredients].[product]) =(SELECT Count(ingredient_requirements.ingredient) AS [ingredient count] FROM ingredient_requirements);

    The last bit of SQL code returns the number 3 since you require 3 ingredients, and the part after HAVING only returns products with a row count of 3 from the join query preceding it.
    After being derailed to other projects, I finally got back to this. Thank you so much for the advice. It worked after I changed "[ingredient count]" to "CountOfIngredient".

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Late to the party, but another table isn't the way to go AFAIC. You could self join the one table you have
    Code:
    SELECT tblProdIngredients.Product, tblProdIngredients.Ingredient, tblProdIngredients_1.Ingredient, tblProdIngredients_2.Ingredient
    FROM (tblProdIngredients INNER JOIN tblProdIngredients AS tblProdIngredients_1 ON tblProdIngredients.Product = tblProdIngredients_1.Product) INNER JOIN tblProdIngredients AS tblProdIngredients_2 ON tblProdIngredients_1.Product = tblProdIngredients_2.Product
    WHERE (((tblProdIngredients.Ingredient)="1") AND ((tblProdIngredients_1.Ingredient)="3") AND ((tblProdIngredients_2.Ingredient)="4"));
    It may not be a slick as june7's though, which I suppose isn't entirely dynamic either since the IN portion of the clause would have to be modified for variations, same as having to add another instance of a table. The table for the above looks like

    Product Ingredient
    Product1 1
    Product1 2
    Product1 3
    Product1 4
    Product2 1
    Product2 3
    Product2 4
    Product3 1
    Product3 3
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    But @Micron, Product3 should not be returned.

    Someday, going to want to require more or less than 3 ingredients. The temp table (table is permanent but records are temporary) approach is probably only non-VBA way to have query object with multiple and variable number of dynamic parameters for same field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @june7
    It's not. That's the table, not the query result.
    Product tblProdIngredients.Ingredient tblProdIngredients_1.Ingredient tblProdIngredients_2.Ingredient
    Product1 1 3 4
    Product2 1 3 4

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-30-2018, 01:16 PM
  2. Replies: 2
    Last Post: 05-17-2017, 06:17 AM
  3. Replies: 2
    Last Post: 07-12-2015, 08:48 AM
  4. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  5. Query multiple field & criteria
    By fua in forum Access
    Replies: 2
    Last Post: 11-04-2009, 08:22 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums