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

1. ## 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. ## 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")))));

3. ## 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. ## Re: AND criteria with one-to-many relationships (A

Here is another solution that can be built enitely using the query grid.

5. ## 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
•