Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Group by sum (Access 2000)

    These are the sales that I have for this customer:
    GPRNT EXTENDEDPR
    56325 -71.88
    56325 71.76
    56325 -95.88
    56325 -71.76
    56325 95.88
    56325 71.88

    If I add their sales up, I get zero.

    When I do a Group By Qry (Group by GPRNT and Sum EXTENDEDPR) I get this strange number.
    GPRNT SumOfEXTENDEDPR
    56325 -1.4210854715202E-14

    Why isn't it zero?

  2. #2
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group by sum (Access 2000)

    Hi,
    I created two tables (tblCustomer and tblSales) and populated them with your data. Then I created a GroupBy query with the following:

    SELECT DISTINCTROW tblCustomer.GPRNT, Sum(tblSales.EXTENDEDPR) AS [Sum Of EXTENDEDPR]
    FROM tblCustomer INNER JOIN tblSales ON tblCustomer.GPRNT = tblSales.GPRNT
    GROUP BY tblCustomer.GPRNT;

    This returns a total of zero as you expected it to. Does this help?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Group by sum (Access 2000)

    It's a matter of rounding. We use the decimal system to write down numbers; computers use the binary system for storing numbers and for calculations. A number such as 71.88 can't be represented exactly as a binary number with finite precision, so there is a tiny rounding error. And I suspect that there are extremeIn your case, the rounding errors add up to the extremely small number -1.4210854715202E-14, that is -1.421... times 10 to the power -14, or -0.00000000000001421... So there is nothing to worry about. You can format the result as Fixed or Standard or Currency, with 2 decimals. The infinitesimal rounding error will be invisible then.

    But, like wrksmrt, I can't reproduce your result. Are the sales figures the result of calculations, or have they been imported from another application?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •