Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AND criteria with one-to-many relationships (Access 2000)

    Okay, this shouldn't be too hard, but my brain has siezed up trying to figure this out. I have two tables related many-to-many via an intermediate "link" table. For the sake of example, consider Table 1 as a list of recipes (the cookbook) and Table 2 as a list of possible ingredients (the pantry). The link table provides the list of which ingredients go with which recipe. Here's the question: How do I design a query that returns a list of the recipes that contains both ingredient A and ingredient B? For example, I don't want all the recipes that contain "Green Eggs" and I don't wan't all the recipes that contain "Ham", but I do want all the recipes that contain both "Green Eggs" and "Ham" (with all due respects to the Dr.). My gut feel is that this should be a no -brainer, but I'm stuck... Any help would be greatly appreciated.

    Regards,

    Sam, uh, make that...

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: AND criteria with one-to-many relationships (A

    Here is one solution, using a double subquery.

    In my test db, this successfully returned recipes containing both Milk and Butter.

    SELECT tblRecipes.*
    FROM tblRecipes
    WHERE (((tblRecipes.RecipeID) In (SELECT tblRecipeIngredients.RecipeID
    FROM tblIngredients INNER JOIN tblRecipeIngredients ON tblIngredients.IngredID = tblRecipeIngredients.IngredID
    WHERE (((tblIngredients.IngredName)="Butter"))))
    AND ((tblRecipes.RecipeID) In (SELECT tblRecipeIngredients.RecipeID
    FROM tblIngredients INNER JOIN tblRecipeIngredients ON tblIngredients.IngredID = tblRecipeIngredients.IngredID
    WHERE (((tblIngredients.IngredName)="Milk")))));
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AND criteria with one-to-many relationships (Access 2000)

    John's solution has the advantage that it uses a single query. Since it contains subqueries, it can be difficult to debug. It is also possible to get the same result by storing the subqueries as separate queries and combining these with the recipes table. Less elegant, but easier to debug. It depends on the situation which approach is best.

    1. Query to select all RecipeID's that use "Green Eggs". Save as qryGreenEggs.

    SELECT tblRecipeIngredients.RecipeID, tblIngredients.Ingredient
    FROM tblIngredients INNER JOIN tblRecipeIngredients ON tblIngredients.IngredientID = tblRecipeIngredients.IngredientID
    WHERE (((tblIngredients.IngredientName)="Green Eggs"));

    2. Query to select all RecipeID's that use "Ham". Save as qryHam.

    SELECT tblRecipeIngredients.RecipeID, tblIngredients.Ingredient
    FROM tblIngredients INNER JOIN tblRecipeIngredients ON tblIngredients.IngredientID = tblRecipeIngredients.IngredientID
    WHERE (((tblIngredients.IngredientName)="Ham"));

    3. Query to select all recipes that use both. This is the one you asked for.

    SELECT tblRecipes.*
    FROM (tblRecipes INNER JOIN qryGreenEggs ON tblRecipes.RecipeID = qryGreenEggs.RecipeID) INNER JOIN qryHam ON tblRecipes.RecipeID = qryHam.RecipeID;

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: AND criteria with one-to-many relationships (A

    Here is another solution that can be built enitely using the query grid.
    Attached Images Attached Images
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AND criteria with one-to-many relationships (A

    Thanks Hans & John. I'll work with your suggestions. Should get me to where I want to go. I'm actually setting up a user-defined query where the user specifies which "ingredients" they want to look for; for a given ingredient they can specify whether the recipe has to contain that ingredient or does not contain that ingredient, and whether the logic between each ingredient is "OR" or "AND". Based on these specifications, I build the WHERE clause in VBA code and tack it on to "constant" SELECT, FROM, and ORDER BY clauses to create the Row Source for the list box to show the recipes the user has selected. So, the task is a little more complex than the example I provided above, but I think that was the only remaining buggaboo.

    Thanks again.

Posting Permissions

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