I have attached a sample db in hopes that somebody can help me create a query.

You will see in the db, two recipes. (molitov cocktail and martini)
Each recipe has (4) ingredients (as shown in tblRecipeIngredients)
(I know, I know... that's not how you make either one, but just go with it)
You will also see in tblIngredients, a field called OnHand. (If a particular ingredient is set to true, that means I have it available in my bar.

Now, what I need is a query that will only return recipe names of which all (4) ingredients are on hand.

My system wont open your file for security reasons, can you zip and attach?

Brian

My system wont open your file for security reasons, can you zip and attach?

Brian

Sorry about that. I updated the link in my original post. Give it another shot.

Thanks, I was about to post and say that I have solved the problem, I had to Unblock the DB from the properties tab, also had to convert to 2002 format, what Access are you on?

Brian

hehehe... '97

I think the following will give you what you want.

Brian

SELECT tblRecipes.Name, Sum(tblIngredients.OnHand) AS SumOfOnHand
FROM tblIngredients INNER JOIN (tblRecipes INNER JOIN tblRecipeIngredients ON tblRecipes.ID = tblRecipeIngredients.Recipe) ON tblIngredients.ID = tblRecipeIngredients.Ingredient
GROUP BY tblRecipes.Name
HAVING (((Sum(tblIngredients.OnHand))=-4))
ORDER BY tblRecipes.Name;

Awesome.
But suppose that I had many recipes of which the number of ingredients could be anywhere from 2 to 10,

The query you gave would only work for recipes that have 4 ingredients on hand, how could we make it more dynamic?

Working on that but 1srt attempt failed as Access would not let ne have an aggregate function in a where clause.
You need to count the ingredients and sum them if the 2 calcs are = then bingo, mines a stright Macallen, no ice

Brian

Yeah, that's exactly what I was trying, but I keep getting a data type mismatch

Ok simple solution change the query above to also include Count of on hand then run

SELECT qryRecipesbjw1.Name
FROM qryRecipesbjw1
WHERE ((([SumOfOnHand]*-1)=[CountOfOnHand]));

Note I suddenly remembered the Sm was negative :D
also note query name ;)

Brian

Ahh, that's great! I'll keep the name exactly how it is!

And I think this works using a subquery ie just run this.

SELECT qryRecipesbjw1.Name
FROM (
SELECT tblRecipes.Name, Sum(tblIngredients.OnHand) AS SumOfOnHand, Count(tblIngredients.OnHand) AS CountOfOnHand
FROM tblIngredients INNER JOIN (tblRecipes INNER JOIN tblRecipeIngredients ON tblRecipes.ID = tblRecipeIngredients.Recipe) ON tblIngredients.ID = tblRecipeIngredients.Ingredient
Group by tblRecipes.Name)
as qryRecipesbjw1
WHERE ((([SumOfOnHand]*-1)=[CountOfOnHand]))
GROUP BY qryRecipesbjw1.Name
ORDER BY qryRecipesbjw1.Name;

Never know if subqueries are better, being a simple soul I usually use cascading queries but I know that subqueries appear to be loved by the more techie guys.

Brian

PS That was fun.

I have a solution not using Counts which first runs a query to find the cocktails which have a missing ingredient and then uses the unmatched query wizard to list the cocktails which are not in the Missing ingredient list.

1st Query

SELECT DISTINCT tblRecipes.Name
FROM tblIngredients INNER JOIN (tblRecipes INNER JOIN tblRecipeIngredients ON tblRecipes.ID = tblRecipeIngredients.Recipe) ON tblIngredients.ID = tblRecipeIngredients.Ingredient
WHERE (((tblIngredients.OnHand)=False))
ORDER BY tblRecipes.Name;

Second Query

SELECT tblRecipes.Name
FROM tblRecipes LEFT JOIN qryRecipes ON tblRecipes.Name = qryRecipes.Name
WHERE (((qryRecipes.Name) Is Null));


Hope this helps

Yep, more than one way to skin a cat. I wonder what a psychiatrist would make of our different approaches?

It's enough to drive a man to drink. :D

Brian

You guys are great. Is it just the nature of Access/sql that prevents us from doing this with just one query?

Yep, more than one way to skin a cat. I wonder what a psychiatrist would make of our different approaches? Probably the result of ou different careers:)

It's enough to drive a man to drink. :D

BrianI'll have one for you this evening :D

You guys are great. Is it just the nature of Access/sql that prevents us from doing this with just one query?

In short yes, tho' you could argue that the subquery approach is 1 query I suppose.

Brian

I'll have one for you this evening :D

Thanks, I'm on the wagon at the moment due to a chest infection. :mad:

Brian

I have a solution not using Counts which first runs a query to find the cocktails which have a missing ingredient and then uses the unmatched query wizard to list the cocktails which are not in the Missing ingredient list.

1st Query

SELECT DISTINCT tblRecipes.Name
FROM tblIngredients INNER JOIN (tblRecipes INNER JOIN tblRecipeIngredients ON tblRecipes.ID = tblRecipeIngredients.Recipe) ON tblIngredients.ID = tblRecipeIngredients.Ingredient
WHERE (((tblIngredients.OnHand)=False))
ORDER BY tblRecipes.Name;

Second Query

SELECT tblRecipes.Name
FROM tblRecipes LEFT JOIN qryRecipes ON tblRecipes.Name = qryRecipes.Name
WHERE (((qryRecipes.Name) Is Null));


Hope this helps

Well, I think I'm going with Rabbie's method. This way, I can call the first query "qryRecipesOffHand" and I can call the second query "qryRecipesOnHand". That way, I'll have two meaningful and useful queries that I can use individually or combined.

Thanks, I'm on the wagon at the moment due to a chest infection. :mad:

BrianSorry to hear that. I had a dram to drink to your speedy recovery:)

Well, I think I'm going with Rabbie's method. This way, I can call the first query "qryRecipesOffHand" and I can call the second query "qryRecipesOnHand". That way, I'll have two meaningful and useful queries that I can use individually or combined.

I don't blame you, I may well have gone down that route except that your first request was easily solved using the =4 technique and then my thoughts were in that channel, Rabbie's solutions are basically more in line with your data design and thinking.

Brian

Sorry to hear that. I had a dram to drink to your speedy recovery:)

I had a good nights sleep last night so keep up the good work please. :)

Brian