sqlvbams-accesssumdistinct

Access Distinct and Sum Query

In order to help me prepare for camping trips, I'm creating a DB where I can pick what recipes I want to make and print a list of ingredients I need to bring. I've got the following query and results:

Query Design

Data Sheet

The Access-generated SQL:

SELECT tblRecipeIngredients.IngredientID, tblIngredients.Cooler, tblIngredients.Ingredient, tblIngredients.UnitOfMeasure, tblRecipeIngredients.Quantity, tblRecipes.Planned
FROM tblIngredients INNER JOIN (tblRecipes INNER JOIN tblRecipeIngredients ON tblRecipes.RecipeID = tblRecipeIngredients.RecipeID) ON tblIngredients.IngredientID = tblRecipeIngredients.IngredientID
WHERE (((tblRecipes.Planned)=True))
ORDER BY tblIngredients.Cooler, tblIngredients.Ingredient;

So far, so good. However, since some ingredients are used in multiple recipes, there are multiple rows for them. For those, I would like to have one row with the total quantity for that ingredient. I have tried numerous combinations of DISTINCT, SUM, GROUP BY, etc. but no matter what I try I get an error saying that some column is not included as "part of an aggregate function".

Is there a way to make my query and/or VBA do what I want?

Solution

  • You will aggregate using SUM(tblIngredients.Quantity) to get a sum of quantity for the ingredient, and then toss every other column in your SELECT clause into the GROUP BY clause:

    SELECT tblRecipeIngredients.IngredientID, tblIngredients.Cooler, tblIngredients.Ingredient, tblIngredients.UnitOfMeasure, SUM(tblRecipeIngredients.Quantity) as Quantity, tblRecipes.Planned
FROM tblIngredients INNER JOIN (tblRecipes INNER JOIN tblRecipeIngredients ON tblRecipes.RecipeID = tblRecipeIngredients.RecipeID) ON tblIngredients.IngredientID = tblRecipeIngredients.IngredientID
WHERE (((tblRecipes.Planned)=True))
GROUP BY tblRecipeIngredients.IngredientID, tblIngredients.Cooler, tblIngredients.Ingredient, tblIngredients.UnitOfMeasure, tblRecipes.Planned
ORDER BY tblIngredients.Cooler, tblIngredients.Ingredient;