Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strange query calculations (2003 SP2)

    I am getting some very strange results to a query simple calculation. I have a database used, among other things, to store and calculate end of course results. The calculation is simple (Internal marks + exam marks) the results are bizarre! <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    I have stripped the database down to try to identify the problem without success. I now have 1 table with 3 fields... a student ID (text field, 5 chars), internal mark (number field, single) and exam mark (number field, single). The stripped down database is attached in the zip file.


    The marks have been entered and no mark has more than 1 decimal place however when the 2 marks are added the result, in many cases, has 9 decimal places. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Attached Files Attached Files
    Cheryl =^..^=

    IS Support, Adelaide <img src=/S/granny.gif border=0 alt=granny width=20 height=20>

    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Strange query calculations (2003 SP2)

    Go into the query and change the format properties of the Final result to Standard with 1 dec place

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

    Re: Strange query calculations (2003 SP2)

    Patt has already provided a solution.

    The results you saw are due to rounding errors. Access internally stores numbers in binary format (consisting of 0 and 1) with a limited precision. Most decimal numbers cannot be represented exactly in binary format, so the numbers are necessarily rounded off. When Access adds these numbers, the rounding errors may reinforce each other or cancel out.

    You used Single size. This number type stores a relatively small number of digits, so rounding errors become apparent quite soon. If you had used Double, there would still have been rounding errors, but they would have been so small that you wouldn't have seen them (Double takes up twices as much space, and is twice as accurate as Single).

    Another possibility would have been to use Currency as field type, and to set the Format for both the fields and the total to Fixed with 1 decimal. Currency can perform accurate calculations with numbers that have 4 decimals or less.

  4. #4
    Star Lounger
    Join Date
    Sep 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange query calculations (2003 SP2)

    Thank you both... that worked. I really thought I was going crazy. I hadn't heard there was any problem with using the Single format, I certainly wont be using it again!
    Cheryl =^..^=

    IS Support, Adelaide <img src=/S/granny.gif border=0 alt=granny width=20 height=20>

    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Strange query calculations (2003 SP2)

    I generally prefer Double over Single because of the increased accuracy, but there is no objection to using the Single format, as long as you are aware of its limitations.

    These rounding errors occur whenever you work with decimal numbers on a computer. See for example the threads starting at <post:=617,479>post 617,479</post:>, <post:=573,156>post 573,156</post:>, and <post:=436,341>post 436,341</post:> in the Excel forum. Excel uses Double format for all its calculations, so the problem is rarely visible, but it does occur.

Posting Permissions

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