Problem with the aggregate Sum function

[rmatos95]

Hi all! I am trying to have a query return the sum of hours in a certain field. My problem is that when I use the Total Sum, the sum does not add up to the real sum. I can't seem to find out why. I know the query is pulling data from a numeric field and that when I don't Sum it return the correct records. Another peice of information is that some of the numbers are negative, but for some records it sums right and for others it does not, but it does not give me any error massages. I am using Access 2000. Here is the SQL:

SELECT [9A FML hours].ID, [9A FML hours].[DeptID Short], [9A FML hours].TRC, Sum([9A FML hours].Quantity) AS SumOfQuantity, [9A FML hours].[Hrs Yr], [FLSA status].[Sal Plan]
FROM [9A FML hours] LEFT JOIN [FLSA status] ON [9A FML hours].ID = [FLSA status].ID
GROUP BY [9A FML hours].ID, [9A FML hours].[DeptID Short], [9A FML hours].TRC, [9A FML hours].[Hrs Yr], [FLSA status].[Sal Plan]
HAVING ((([9A FML hours].TRC)="FEX") AND ((Sum([9A FML hours].Quantity))>0))
ORDER BY [9A FML hours].[Hrs Yr];

Thank you for your help!!!!!

[Stewart Ross]

Hi, and welcome to Bytes!

It isn't at all clear from what you've posted above whether the groupings in your query are correct for the totals you require - you don't really tell us anything at all about the data or what you mean when you say the totals aren't correct.

The totals calculated will apply to the smallest grouping of distinct rows in your query which satisfy your criteria in the HAVING clause. As you will see from the SQL, the grouping you are using is by ID, [DeptID Short], TRC, [Hrs Yr], and [Sal Plan] (whatever they are). Without knowing more about your data we cannot really advise you further.

It would help to see a sample of your data, and a sample of what you expect to see from such a total. That way we can judge whether your query is suitable or not for doing so.

-Stewart

[rmatos95]

I am not sure if this will give more information to my problem, but here are a couple rows of my data:

. ID    TRC  Quantity  Hrs Yr
103187  FEX     -8      2007
103187  FEX      8      2007
600508  FEX      5      2007
600508  FEX      6      2007
601729  FEX     -8      2007
601729  FEX      8      2007

But when I do Sum for the quantity field, the sums are not always correct. Here is what it shows after I do Sum:

. ID    TRC  SumOfQuantity  Hrs Yr
103187  FEX       16        2007
600508  FEX       16        2007
601729  FEX        0        2007

Is this easier to understand my problem?

Thank you

[Stewart Ross]

Ok, this does at least give some indication of your data; but there is no mention of the Sal Plan field which is part of your Group By clause. Are you really posting the data that your query is operating on? I'll explain why I am not at all sure you are below.

For ID 103187 there are only two rows listed and these would sum to 0 unless there is something else we are not seeing - and your sum shows the value 16, which cannot be obtained by summing the two rows listed. Don't assume that Sum is wrong - it is correctly summing what it has been told to sum. Since this does not match the data posted, what you have posted is clearly not what is being summed. In other words, the data actually being totalled is not the same as you think it is - and you are not going to resolve this until you look at the actual rows the totals query is operating on. It is also a sample of these rows which you should post here to help us help you.

It is possible that the underlying query may have incorrect joins somewhere, either in the current query or in any underlying one that we can't see, leading to multiple rows being returned for each ID. If this happens your totals are bound to be incorrect. To test this I would save a copy of your query under a new name, turn totalling off altogether, remove the computed total field, then run the query and check the actual rows it is operating upon - as these are the ones which will be summed when you turn totalling back on.

The skill in resolving this kind of problem is not to make incorrect assumptions about your data without fully testing your assumptions for validity. You need to look at the actual data the query is operating on, not a sample from an underlying table or another query. Incorrect assumptions leads to incorrect diagnoses leads to no solution to your problem.

-Stewart

[rmatos95]

Wow! It worked! I still don't know what it is summing, but now I understand that it is totalling more records than what my query returns. I was able to create another query of my original query and total only the results I want. Thanks, that answers my question.
p.s. I took a couple fields off because I thought they were unecessary.
Thank you again!