Access 2003 summation error

Discussion in 'Microsoft Access' started by DanG, Aug 23, 2005.

  1. DanG

    DanG Guest

    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.
     
    DanG, Aug 23, 2005
    #1
    1. Advertisements

  2. DanG

    Garret Guest

    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.
     
    Garret, Aug 23, 2005
    #2
    1. Advertisements

  3. DanG

    DanG Guest

    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.
     
    DanG, Aug 24, 2005
    #3
  4. DanG

    Dirk Goldgar Guest

    "DanG" <> wrote in message
    news:
    > 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)
     
    Dirk Goldgar, Aug 24, 2005
    #4
  5. DanG

    renjith

    Joined:
    May 10, 2012
    Messages:
    2
    Likes Received:
    0
    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
     
    renjith, May 10, 2012
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Anthony Viscomi

    Summation Sign

    Anthony Viscomi, Oct 16, 2004, in forum: Microsoft Access
    Replies:
    1
    Views:
    345
    Anthony Viscomi
    Oct 16, 2004
  2. Guest

    Problems with Date Add & summation of values

    Guest, Jun 6, 2005, in forum: Microsoft Access
    Replies:
    3
    Views:
    166
    David Lloyd
    Jun 6, 2005
  3. Guest

    Summation in report

    Guest, Dec 15, 2005, in forum: Microsoft Access
    Replies:
    5
    Views:
    166
    James Atexide
    Feb 14, 2006
  4. Guest

    Summation Query and Join Function Help

    Guest, Feb 9, 2007, in forum: Microsoft Access
    Replies:
    0
    Views:
    150
    Guest
    Feb 9, 2007
  5. Prem Kumar

    Summation of columns-1

    Prem Kumar, Jul 2, 2008, in forum: Microsoft Access
    Replies:
    8
    Views:
    177
    a a r o n _ k e m p f
    Jul 5, 2008
Loading...

Share This Page