PC Review


Reply
Thread Tools Rate Thread

Access 2003 summation error

 
 
DanG
Guest
Posts: n/a
 
      23rd Aug 2005
Hi all

I'm getting a weird anomaly when I use the summation function of
Access. For example, when I sum the following numbers:

274.28
68.57
34.71
1022.4
25.38
-1425.34

I should get 0 (zero). Instead, I get 2.27373675443232E-13.

If I change the 274.38 to 274.36, I should get -.02, but instead get
-0.019999999. which is almost -.02. Makes me think of the old Intel
processor errors.

The data originally came from a Firebird database, which was being
summed and printed on a Crystal report via ODBC. The value should have
been zero, and apparently is really, really close to zero, but isn't
quite. A zero prints, though I have zero-suppress on, and that was my
clue. So even Crystal is not summing to exactly zero.

That's when I imported (not linked) the data into Access 2003, and ran
the summation there, confirming that the numbers are not summing
correctly.

Rounding to two decimals does not fix the error.

So I question my math processor. I have a Compaq nx7000.

Thoughts and suggestions are welcome.

Cheers
Dan

On a clear disk, you can seek forever.

 
Reply With Quote
 
 
 
 
Garret
Guest
Posts: n/a
 
      23rd Aug 2005
I think that your variable you store your answer in is out of the scope
of your calculations (decimals or boolean value). Try changing these
to Doubles and try again.

DanG wrote:
> Hi all
>
> I'm getting a weird anomaly when I use the summation function of
> Access. For example, when I sum the following numbers:
>
> 274.28
> 68.57
> 34.71
> 1022.4
> 25.38
> -1425.34
>
> I should get 0 (zero). Instead, I get 2.27373675443232E-13.
>
> If I change the 274.38 to 274.36, I should get -.02, but instead get
> -0.019999999. which is almost -.02. Makes me think of the old Intel
> processor errors.
>
> The data originally came from a Firebird database, which was being
> summed and printed on a Crystal report via ODBC. The value should have
> been zero, and apparently is really, really close to zero, but isn't
> quite. A zero prints, though I have zero-suppress on, and that was my
> clue. So even Crystal is not summing to exactly zero.
>
> That's when I imported (not linked) the data into Access 2003, and ran
> the summation there, confirming that the numbers are not summing
> correctly.
>
> Rounding to two decimals does not fix the error.
>
> So I question my math processor. I have a Compaq nx7000.
>
> Thoughts and suggestions are welcome.
>
> Cheers
> Dan
>
> On a clear disk, you can seek forever.


 
Reply With Quote
 
 
 
 
DanG
Guest
Posts: n/a
 
      24th Aug 2005
Thanks for the reply, Garret.

Within Access, I'm only running a SELECT id, sum(amount) GROUP BY id.
There isn't any variable. I did try changing the table column from
number to currency, and that made Access generate the zero.

Crystal is another matter. The data is coming from a database used
within a package, so I can't go changing the schema. I tried changing
the report field format to Currency, but that didn't help.

I tried to change Crystal's query statement to do a CONVERT, like SQL
Server allows. The documentation says it has a Convert function, but
when I execute the query, it says CONVERT is an unknown function.

Maybe it's time to hit the Crystal forum.

 
Reply With Quote
 
Dirk Goldgar
Guest
Posts: n/a
 
      24th Aug 2005
"DanG" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> Hi all
>
> I'm getting a weird anomaly when I use the summation function of
> Access. For example, when I sum the following numbers:
>
> 274.28
> 68.57
> 34.71
> 1022.4
> 25.38
> -1425.34
>
> I should get 0 (zero). Instead, I get 2.27373675443232E-13.
>
> If I change the 274.38 to 274.36, I should get -.02, but instead get
> -0.019999999. which is almost -.02. Makes me think of the old Intel
> processor errors.
>
> The data originally came from a Firebird database, which was being
> summed and printed on a Crystal report via ODBC. The value should
> have been zero, and apparently is really, really close to zero, but
> isn't quite. A zero prints, though I have zero-suppress on, and that
> was my clue. So even Crystal is not summing to exactly zero.
>
> That's when I imported (not linked) the data into Access 2003, and ran
> the summation there, confirming that the numbers are not summing
> correctly.
>
> Rounding to two decimals does not fix the error.
>
> So I question my math processor. I have a Compaq nx7000.
>
> Thoughts and suggestions are welcome.


What type of field are these numbers stored in? Floating point formats
are inherently imprecise. If your field type is Number/Single, you
might try changing it to Number/Double to get better precision, but it
will still be imprecise. Or, if your numbers will never have more than
four decimal places, you might try changing the field type -- not its
format -- to Currency.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


 
Reply With Quote
 
New Member
Join Date: May 2012
Posts: 2
 
      10th May 2012
Hi all i am having the similar problem.I already designed table field with
tblnaname:receiptdet
fieldname: transferquantity

field size: double
Format :Fixed
Decimal Places:2

i have 90 roes and all ahving value of 51.20
i am getting sum as 4607.99999999999 instaead of 4608.
i want the result in 4608.00
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Query Output to report Summation problems Enrique Rojas Microsoft Access Queries 2 19th Mar 2007 02:51 PM
summation in access database =?Utf-8?B?cGhpbCBhY2Nlc3M=?= Microsoft Access Queries 1 15th Jul 2005 05:07 PM
summation in access =?Utf-8?B?cGhpbCBhY2Nlc3M=?= Microsoft Access Queries 1 15th Jul 2005 02:48 AM
Microsoft Access should allow usage of summation or multiplicatio. =?Utf-8?B?a2VubnlrZWU=?= Microsoft Access Forms 2 23rd Mar 2005 03:00 PM
VBA code to perform summation and product summation 21MSU Microsoft Excel Programming 4 17th May 2004 07:19 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:14 AM.