Strange query calculations (2003 SP2)

Topic

New Reply

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!

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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 573,156, and post 436,341 in the Excel forum. Excel uses Double format for all its calculations, so the problem is rarely visible, but it does occur.

Reply | Quote