Summing number wrong????

  • Thread starter Thread starter Russ via AccessMonster.com
  • Start date Start date
R

Russ via AccessMonster.com

Ok, I really need some help on this. What am I doing wrong?
My problem is difference between sum of numbers, Using access Or using excel
/ calculator or why?
Field = Sales Tax, Data type = Double format = General number
I have my order details table with the following sales tax information
Access Excel or calculator
2.53 2.53
0.94 0.94
3.75 3.75
2.53 2.53
1.25 1.25
3.75 3.75
1.69 1.69
1.25 1.25
3.75 3.75
1.56 1.56
2.81 2.81
2.19 2.19
2.53 2.53
1.25 1.25
4.69 4.69
2.53 2.53
1.25 1.25
3.75 3.75
1.69 1.69
10.63 10.63
43.59 43.59
1.25 1.25
2.19 2.19
3.13 3.13
Sums..........................
106.47 106.48
 
Russ said:
Ok, I really need some help on this. What am I doing wrong?
My problem is difference between sum of numbers, Using access Or
using excel / calculator or why?
Field = Sales Tax, Data type = Double format = General number
Click to expand...
[snip]

The two number types Single and Double are both "Imprecise" and suffer from
rounding flaws. This is true in all computer programs that use them.

Try switching to a Currency DataType.
 
Had it set to currency in the first place, when that did not work tried all
number formats with no luck yet.
Wonder why I can not get this to work????

Rick said:
Ok, I really need some help on this. What am I doing wrong?
My problem is difference between sum of numbers, Using access Or
using excel / calculator or why?
Field = Sales Tax, Data type = Double format = General number
Click to expand...
[snip]

The two number types Single and Double are both "Imprecise" and suffer from
rounding flaws. This is true in all computer programs that use them.

Try switching to a Currency DataType.
Click to expand...
 
Russ said:
Had it set to currency in the first place, when that did not work
tried all number formats with no luck yet.
Wonder why I can not get this to work????

Rick said:
Ok, I really need some help on this. What am I doing wrong?
My problem is difference between sum of numbers, Using access Or
using excel / calculator or why?
Field = Sales Tax, Data type = Double format = General number
Click to expand...
[snip]

The two number types Single and Double are both "Imprecise" and
suffer from rounding flaws. This is true in all computer programs
that use them.

Try switching to a Currency DataType.
Click to expand...
Click to expand...

Well currency has no problems due to imprecision, but it does still hold four
decimal places internally. If you do the same calculation in Excel with only
two decimal places then you will see differences.
 
Do you think it may have to with the data type of the field SalesTax?
the formula is LineTotal*SalesTax
SalesTax is a number field "field size single" format "Percent"

Rick said:
Had it set to currency in the first place, when that did not work
tried all number formats with no luck yet.
Click to expand...
[quoted text clipped - 11 lines]
Well currency has no problems due to imprecision, but it does still hold four
decimal places internally. If you do the same calculation in Excel with only
two decimal places then you will see differences.
Click to expand...
 
Russ said:
Do you think it may have to with the data type of the field SalesTax?
the formula is LineTotal*SalesTax
SalesTax is a number field "field size single" format "Percent"

Rick said:
Had it set to currency in the first place, when that did not work
tried all number formats with no luck yet.
Click to expand...
[quoted text clipped - 11 lines]
Try switching to a Currency DataType.
Click to expand...

Well currency has no problems due to imprecision, but it does still
hold four decimal places internally. If you do the same
calculation in Excel with only two decimal places then you will see
differences.
Click to expand...
Click to expand...

What matters is the type and number of decimal places in the final result.
Unless your formula is rounding individual results to two decimal places then
the sum of a column of those values is almost never going to agree with the sum
of values where that are rounded to two decimal places. Remember that
formatting the value in Access to two decimal places does not actually round the
*real* value. It only affects what you see.
 
Ok, thanks

Rick said:
Do you think it may have to with the data type of the field SalesTax?
the formula is LineTotal*SalesTax
Click to expand...
[quoted text clipped - 10 lines]
What matters is the type and number of decimal places in the final result.
Unless your formula is rounding individual results to two decimal places then
the sum of a column of those values is almost never going to agree with the sum
of values where that are rounded to two decimal places. Remember that
formatting the value in Access to two decimal places does not actually round the
*real* value. It only affects what you see.
Click to expand...
 
If LineTotal is still Currency, you could try:

LineTotal*CCur(SalesTax)

and see whether it makes any difference.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Russ via AccessMonster.com said:
Do you think it may have to with the data type of the field SalesTax?
the formula is LineTotal*SalesTax
SalesTax is a number field "field size single" format "Percent"

Rick said:
Had it set to currency in the first place, when that did not work
tried all number formats with no luck yet.
Click to expand...
[quoted text clipped - 11 lines]
Try switching to a Currency DataType.
Click to expand...

Well currency has no problems due to imprecision, but it does still hold
four
decimal places internally. If you do the same calculation in Excel with
only
two decimal places then you will see differences.
Click to expand...
Click to expand...
 
If I remember correctly, multiplying CCUR by a Single type will get you a single
(or perhaps double).

Try

[YourCCURField] * CCur([YourSingleTypeField])

That should force the arithmetic to be correct and should type the result
correctly. You do realize that this will still give you amounts that use the
third and fourth places of the decimal portion. You may have to Round the
results of the individual like calculations.

Also, your sum would have to use that same technique.

SUM(Round([YourCCURField] * CCur([YourSingleTypeField]),2))

That is not the same as
Round(SUM(...))



Russ via AccessMonster.com said:
Do you think it may have to with the data type of the field SalesTax?
the formula is LineTotal*SalesTax
SalesTax is a number field "field size single" format "Percent"

Rick said:
Had it set to currency in the first place, when that did not work
tried all number formats with no luck yet.
Click to expand...
[quoted text clipped - 11 lines]
Try switching to a Currency DataType.
Click to expand...

Well currency has no problems due to imprecision, but it does still hold four
decimal places internally. If you do the same calculation in Excel with only
two decimal places then you will see differences.
Click to expand...
Click to expand...
 
Back
Top