Sum() function returning garbage numbers...

[Scott Price]

Hello Gurus of the Access world...

This one has me stumped. I am developing a meal planning database for large functions (1000 people +- for up to 4 days). I have stored base recipes, as well as stored historic data from past years. To generate a shopping list report I am using this sql query:

SELECT tblIngredient.ItemName, Sum(tblRecipeIngredient.Quantity) AS TotQuantity, tblMeasureType.MeasureDesc, tblIngredient.RecipeMeasureID, tblConvInformation.ConventionName, tblIngredient.ShopMeasureID, tblHistory.ConvID, tblHistory.Year, Sum(tblRecipe.BaseNbrOfServings) AS SumOfBaseNbrOfServings, Sum(tblHistory.PersonCountEst) AS SumOfPersonCountEst, tblIngredient.IngredientID
FROM (tblConvInformation INNER JOIN (((tblMeal INNER JOIN tblHistory ON tblMeal.MealID = tblHistory.MealID) INNER JOIN tblMealStatus ON tblHistory.MealStatusID = tblMealStatus.MealStatusID) INNER JOIN tblRecipe ON tblHistory.RecipeID = tblRecipe.RecipeID) ON tblConvInformation.ConvID = tblHistory.ConvID) INNER JOIN ((tblMeasureType INNER JOIN tblIngredient ON tblMeasureType.MeasureID = tblIngredient.RecipeMeasureID) INNER JOIN tblRecipeIngredient ON tblIngredient.IngredientID = tblRecipeIngredient.IngredientID) ON tblRecipe.RecipeID = tblRecipeIngredient.RecipeID
WHERE (((tblMealStatus.IncludeOnShopList)=True))
GROUP BY tblIngredient.ItemName, tblMeasureType.MeasureDesc, tblIngredient.RecipeMeasureID, tblConvInformation.ConventionName, tblIngredient.ShopMeasureID, tblHistory.ConvID, tblHistory.Year, tblIngredient.IngredientID, tblIngredient.ItemCategoryID
HAVING (((tblHistory.ConvID)=[forms]![frmConvInformation].[ConvID]) AND ((tblHistory.Year)=Format(Date(),"yyyy")))
ORDER BY tblIngredient.ItemName, tblIngredient.ItemCategoryID;

The query is working fine... That is, it returns numbers without giving any error messages. The problem lies in the two Sum() fields BaseNbrOfServings and PersonCountEst. When I look at the numbers returned from these two fields, and then compare them with a manual calculation of the base tables they refer to, the numbers end up wildly off!

One quick example to illustrate is: Eggs are referred to in 3 recipes; For the year and convention chosen one of these recipes is referred to twice, and the other two once each; The corresponding numbers SHOULD be then BaseNbrOfServings = 575 (twice 250 + 50 + 25) & PersonCountEst = 900 (300 + 300 + 250 + 50) However, the numbers returned are SumOfBaseNbrOfServings 1075, SumOfPersonCountEst 1450.

The total eggs required should come out to be 60 dz * (900/575) = 94 dz... Using the returned numbers from the query results in a shopping list requirement of 81 dz...

I've been losing hair, brain cells and sleep over this one :-) Needless to say, I can't trust any of the numbers returned by this query!

Any help would be appreciated greatly.

Regards,
Scott

[missinglinq]

Scott, I'm going to leave this question for some of our more SQL savvy members, but when posting SQL code, especially as complicated as this, please tag it specifically as SQL code. It's really simple; after hi-liting the code and clicking on the # icon, go to the opening code tag and change code to code=sql. Just like that, with no spaces between code = or sql.

Doing this allows the editor to bold the various key words in the SQL statement, making it much easier for everyone to read! I've gone ahead and re-tagged this one for you.

Thanks!

Linq ;0)>

[Scott Price]

Scott, I'm going to leave this question for some of our more SQL savvy members, but when posting SQL code, especially as complicated as this, to tag it specifically as SQL code. It's really simple; after hi-liting the code and clicking on the # icon, go to the opening code tag and change code to code=sql. Just like that, with no spaces between code = or sql.

Doing this allows the editor to bold the various key words in the SQL statement, making it much easier for everyone to read!

Thanks!

Linq ;0)>

Thanks, Linq... I saw that you (or someone) had done that for me. I'm aware of doing that (at least for vb code :-) but in the heat of the moment it escaped me.

Thanks again,
Regards,
Scott

[missinglinq]

Great! As I said, just put sql where you'd put the vb for VBA code.

Linq ;0)>

[JKing]

Two thoughts off the top of my head.

Is it possible for a convention to be held twice in one year?

Check that your sum is not equal to the total for the itemcategory rather than the specific item.

[Scott Price]

Thanks, Jared, for looking at this one...

As for:

Is it possible for a convention to be held twice in one year?

No, only one convention will be held per year.

As for:

Check that your sum is not equal to the total for the itemcategory rather than the specific item.

I don't think so... I just did a quick analysis on that, and came up with 4425 or something like that, looking at the personcountest for each recipe that requires the itemcategory Dairy/Egg...

I'm working on a workaround at the moment, because I'm finding that this query still wouldn't come up with the correct amounts even if the Sum() fields DID come out accurately!!

Again, thanks for looking... I'll post back here if/when I get the right #'s.

Regards,
Scott

I WOULD still like to know why it's pulling the wrong numbers in, though...

[JKing]

Have a look at this join:

tblMeasureType.MeasureID = tblIngredient.RecipeMeasureID

Different names here. Have you just named the foreign key different or is this an incorrect field? Or does RecipeMeasureID the foreign key for tblRecipe?

[Scott Price]

Have a look at this join:

tblMeasureType.MeasureID = tblIngredient.RecipeMeasureID

Different names here. Have you just named the foreign key different or is this an incorrect field? Or does RecipeMeasureID the foreign key for tblRecipe?

I'll have to investigate that one a little further, but at the moment, I THINK it's just the foreign key named differently. (the different naming is because later I want to implement the ability to convert from the measurement used in recipes to the measurement used in shopping... at this point, though, that isn't implemented).

Regards,
Scott

[Scott Price]

I'll have to investigate that one a little further, but at the moment, I THINK it's just the foreign key named differently. (the different naming is because later I want to implement the ability to convert from the measurement used in recipes to the measurement used in shopping... at this point, though, that isn't implemented).

Regards,
Scott

The full workaround I managed is too complex to post here, and not really applicable to the original query, but I did manage to finally get the right numbers outputted to the shopping report.

Basically it involved abandoning the query above and starting over from scratch, doing the math of calculating the quantity of each ingredient per recipe BEFORE plugging that into the final shopping list query, then finally summing each already calculated ingredient, passing each revised quantity through a function to convert decimals to fractions (if anyone wants to see the function to convert decimals to fractions, let me know... it's pretty cool, actually) and only then passing the results over to the shopping report.

Thanks, Jared for trying to help on this rat's nest query!

Regards,
Scott

Now off to clean up the redundant and no longer needed queries that I've created and copied in order to try to figure this thing out...