sum is incorrect (slightly off)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a simple sum query to sum sales. No need to pay attention to my
criteria expression as it is just narrowing down the selection to one record.
When I take grouping off (no sum), the sales figure is correct. Here is the
SQL for the query:

SELECT T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material, T10_Sls_Detail.Sales
FROM T10_Sls_Detail
WHERE (((T10_Sls_Detail.Month)="09") AND ((T10_Sls_Detail.Base_Year)="2007")
AND ((T10_Sls_Detail.Base_Month)="02") AND
((T10_Sls_Detail.Material)="59021467"));

This gives me the correct sales figure, but I need to sum it.

Here is the SQL for the query that sums the sales figure:

SELECT T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material, Sum(T10_Sls_Detail.Sales)
AS SumOfSales
FROM T10_Sls_Detail
GROUP BY T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material
HAVING (((T10_Sls_Detail.Month)="09") AND
((T10_Sls_Detail.Base_Year)="2007") AND ((T10_Sls_Detail.Base_Month)="02")
AND ((T10_Sls_Detail.Material)="59021467"));

This adds .125 to the sales figure. The query doesn't just add to the sales
figure, however. When I select other material numbers (txt field), it could
be correct or even subtract from the figure. The maximum deviation is .50.
Any help you could provide would be greatly appreciated.

Adam
 
Is it possible you formatted the SUM to display integers, which can thus
round up, or down, the DISPLAYED value.


Also, you can try:


SELECT T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material,
Sum(T10_Sls_Detail.Sales)
AS SumOfSales

FROM T10_Sls_Detail

WHERE (((T10_Sls_Detail.Month)="09") AND
((T10_Sls_Detail.Base_Year)="2007") AND ((T10_Sls_Detail.Base_Month)="02")
AND ((T10_Sls_Detail.Material)="59021467"))

GROUP BY T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material



Fact is that WHERE occur BEFORE the SUM and the GROUP, while HAVING occurs
after the groups have been made, and the SUM done. You can also, doing so,
remove the fields you don't need anymore in the SELECT clause, and in the
GROUP BY clause, since the WHERE clause would have removed anything that is
not (T10_Sls_Detail.Base_Year = "2007"); it is thus useless to GROUP BY on
T10_Sls_Detail.Base_Year, since it contains only "2007".



Hop[ing it may help,
Vanderghast, Access MVP
 
Michel,

Thank you for your prompt response. The table is formatted as a single, and
there is no formatting in the query. I tried your SQL below along with this:

SELECT Sum(T10_Sls_Detail.Sales) AS SumOfSales
FROM T10_Sls_Detail
WHERE (((T10_Sls_Detail.Month)="09") AND ((T10_Sls_Detail.Base_Year)="2007")
AND ((T10_Sls_Detail.Base_Month)="02") AND
((T10_Sls_Detail.Material)="59021467"));

I am still getting the same result. Any clues?

Adam



Michel Walsh said:
Is it possible you formatted the SUM to display integers, which can thus
round up, or down, the DISPLAYED value.


Also, you can try:


SELECT T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material,
Sum(T10_Sls_Detail.Sales)
AS SumOfSales

FROM T10_Sls_Detail

WHERE (((T10_Sls_Detail.Month)="09") AND
((T10_Sls_Detail.Base_Year)="2007") AND ((T10_Sls_Detail.Base_Month)="02")
AND ((T10_Sls_Detail.Material)="59021467"))

GROUP BY T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material



Fact is that WHERE occur BEFORE the SUM and the GROUP, while HAVING occurs
after the groups have been made, and the SUM done. You can also, doing so,
remove the fields you don't need anymore in the SELECT clause, and in the
GROUP BY clause, since the WHERE clause would have removed anything that is
not (T10_Sls_Detail.Base_Year = "2007"); it is thus useless to GROUP BY on
T10_Sls_Detail.Base_Year, since it contains only "2007".



Hop[ing it may help,
Vanderghast, Access MVP


Adam said:
I have a simple sum query to sum sales. No need to pay attention to my
criteria expression as it is just narrowing down the selection to one
record.
When I take grouping off (no sum), the sales figure is correct. Here is
the
SQL for the query:

SELECT T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material, T10_Sls_Detail.Sales
FROM T10_Sls_Detail
WHERE (((T10_Sls_Detail.Month)="09") AND
((T10_Sls_Detail.Base_Year)="2007")
AND ((T10_Sls_Detail.Base_Month)="02") AND
((T10_Sls_Detail.Material)="59021467"));

This gives me the correct sales figure, but I need to sum it.

Here is the SQL for the query that sums the sales figure:

SELECT T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material,
Sum(T10_Sls_Detail.Sales)
AS SumOfSales
FROM T10_Sls_Detail
GROUP BY T10_Sls_Detail.Month, T10_Sls_Detail.Base_Year,
T10_Sls_Detail.Base_Month, T10_Sls_Detail.Material
HAVING (((T10_Sls_Detail.Month)="09") AND
((T10_Sls_Detail.Base_Year)="2007") AND ((T10_Sls_Detail.Base_Month)="02")
AND ((T10_Sls_Detail.Material)="59021467"));

This adds .125 to the sales figure. The query doesn't just add to the
sales
figure, however. When I select other material numbers (txt field), it
could
be correct or even subtract from the figure. The maximum deviation is
.50.
Any help you could provide would be greatly appreciated.

Adam
Click to expand...
Click to expand...
 
Adam

If you are using a double or single numeric data type, be aware that Access
stores the underlying values as binary equivalents. This will result in
rounding errors.

If you won't need more than 4 decimal places (you did say "sales", right?!),
you can use the Currency data type. This stores exactly the up-to-4 decimal
values, so no rounding error occurs.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

It is sales, yes. Thank you for the suggestion. I have the sales column
formatted as single because I translate between currencies (USD to EUR,
etc.). The rounding issue perplexes me because the sales figure I was
speaking of was 1,000,285.00

Why would this have been rounded?

Thanks,

Adam
 
That may be a limitation of the SINGLE datatype (which is 7-8 decimal). Try
a double, or, currency, or, if you can't change the table design:

SUM( CCur( T10_Sls_Detail.Sales ) )



Hoping it may help,
Vanderghast, Access MVP
 
It is sales, yes. Thank you for the suggestion. I have the sales column
formatted as single because I translate between currencies (USD to EUR,
etc.). The rounding issue perplexes me because the sales figure I was
speaking of was 1,000,285.00

Why would this have been rounded?
Click to expand...

Because a Single stores 24 binary bits of precision - about seven decimal
places precision. You are showing nine (seven before, two after the decimal).
A Single datatype cannot accurately represent the value 1,000,285.00 - it will
be off in the ones or tenths place.

Use a Decimal number type with enough precision, or Currency.

John W. Vinson [MVP]
 
Michel,

I tried switching the table to double and using CCur, neither worked. Any
other thoughts?

Adam
 
the decimal worked...Thanks!!

John W. Vinson said:
Because a Single stores 24 binary bits of precision - about seven decimal
places precision. You are showing nine (seven before, two after the decimal).
A Single datatype cannot accurately represent the value 1,000,285.00 - it will
be off in the ones or tenths place.

Use a Decimal number type with enough precision, or Currency.

John W. Vinson [MVP]
Click to expand...
 
Back
Top