Results 1 to 10 of 10
Thread: numbers rounding problem

20020323, 18:29 #1
 Join Date
 Feb 2001
 Location
 Minnesota
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
numbers rounding problem
I am getting 2 different results in Grade Point Averages (GPA) on a GPA Report and on the Transcript.
I have 2 tables involved: tblGradeValues with a field GradeValue (a number field, field size single, format standard, decimals 2) and tblTranscripts with a field Units ( a number field, field size single, format standard, decimals 2). Access 97
When I run the GPA Report my results for Students: David is 3.23 GPA, Norbert is 4.0, Barbara is 3.91 and Sam is 4.50.
When I run the Transcripts: I show grades for each of 8 semesters with a semester GPA and a Cummulative GPA after each semester. Results: David is 3.20 GPA, Norbert is 3.97, Barbara is 3.90 and Sam is 4.50.
So my problem is that each semester is rounded (to some point  I know not where). Then as each semester is added for the cumulative GPA the rounding effects the final cumulative GPA.
I can't have this discrepency how do I tell Access to round each of these reports at a point that will make the result consistent?
I have check my report fields and they are all : standard 2 dec. By the way I only want to show 2 decimals.
<img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

20020323, 22:34 #2
 Join Date
 Aug 2001
 Location
 Evergreen, CO, USA
 Posts
 6,653
 Thanks
 3
 Thanked 65 Times in 64 Posts
Re: numbers rounding problem
The problem may be more subtle than that actually  Does each student take the same number of hours each semester? If not, you can't take an average of the grade point averages for each semester. There is also a potential rounding problem in that the numeric field size you are using is a floating point number with about 6 places of accuracy. But when you start doing repeated calculations on those kind of numbers, the accuracy quickly diminishes. Are you storing the calculated result in a temporary table? If so the number will be rounded to 2 places if you tell it to. Otherwise it will be used with full accuracy. You could try using the double precision data type, which gives you 15 to 16 decimals of accuracy. But the bottom line is the only way to accurately calculate the GPA is to all up all the hours, and add up all the calculated points, and then divide the sum of the points by the hours. Hope this helps.
Wendell

20020323, 22:47 #3
 Join Date
 Feb 2001
 Location
 Minnesota
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: numbers rounding problem
Each student does take the same number of hours each semester. But, the units are added up for each student each semester on the transcript so that shouldn't make any difference. I don't think I know how to store the results in a temporary table I will try that. I don't know what double precision data type is I will try to look that up. Thanks You stirred my brain. Pat

20020324, 02:48 #4
 Join Date
 Jan 2001
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 632
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: numbers rounding problem
A couple points:
 the number of decimals in the field defintion has no effect on the precision with which the data is *stored*  it affects only the number of decimals with which it is displayed. See Help for additional information.
 floating point calculations are not exact, although you would usually expect to see differences in "lesser significant" figures. You might want to consider doing your calculations with Integers and scaling the results.
 your answer to Wendell makes me wonder whether you both mean the same thing when you say: "Each student does take the same number of hours each semester". You could mean that 1) *all students* have the same hours in each semester or 2) all hours for *each student* is the same for all all students. this is not just semantics  it makes a significant difference on the calculation of averages.
Consider the following for a single student in two semesters:
2
2
2
3
3
Clearly, the average of semester 1 is "2" and the average of semester 2 is "3". But what is the overall average? Is it (2+3)/2 = 2.5 or is it (2+2+2+3+3)/5 = 12/5 = 2.4
I think that was the point that Wendell was making. The only way to correctly calculate the overall average is to add all the points and divide by the total number of hours. I would tend to caluculate the average manually in the report by summing the components, rather than by using an Avg function. That way, I would be certain what the calculation entailed, plus I would have the intermediate results for checking...
Hope this helps.
Jack MacDonald
Vancouver, Canada

20020326, 15:01 #5
 Join Date
 Feb 2001
 Location
 Minnesota
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: numbers rounding problem
I think I understand this and I found making a temporary table is the way to do it. However, I still wonder what is the best number design to use: double, single, etc. ?
Thank you for your help.
Pat

20020326, 16:24 #6
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,474
 Thanks
 1
 Thanked 41 Times in 40 Posts
Re: numbers rounding problem
Rounding is a problem everyone has bumped up against. You have to understand there is a difference between the # of decimal positions you are displaying, and the actual # of positions in your data.
Most math creates a double floatingpoint decimal for the result. A result of that you display as 1.23 may actually be 1.2344444444! The best way to handle this is to actually round the intermediate results to 2 decimals; so that 1.23444444 becomes 1.23!Mark Liquorman
See my website for Tips & Downloads and for my Liquorman Utilities.

20020326, 18:14 #7
 Join Date
 Feb 2001
 Location
 Minnesota
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: numbers rounding problem
Thank you for the explaination. How do I actually round in the temporary table? You don't mean I hope change the field to text and edit it.
Pat

20020326, 18:35 #8
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,474
 Thanks
 1
 Thanked 41 Times in 40 Posts
Re: numbers rounding problem
No, you don't have to "edit" the numbers! First of all, make sure your numbers are declared as currency. Then, any time you have a calculation (especially if they use multiplication and/or division), always round the result. For example, now you may have this expression:
GPA = ...some calculation...
Instead, do this: GPA = Roundit(...some calculation)
Your Roundit function will look like this:
Public Function Roundit (byref SomeNumber) as Currency
Roundit = CCur( CCur(SomeNumber/100) *100)
End Function
This function will only round to 2 places. You can find a more flexible rounding function at my website (follow link below).Mark Liquorman
See my website for Tips & Downloads and for my Liquorman Utilities.

20020326, 20:03 #9
 Join Date
 Feb 2001
 Location
 Minnesota
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: numbers rounding problem
Thank you I think I have it now. I don't know much SQL so I am slow at this.
Pat

20020327, 02:50 #10
 Join Date
 Dec 2000
 Location
 Sacramento, California, USA
 Posts
 16,775
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: numbers rounding problem
Since you managed to escape posting your version of Access, I'll throw in the point that Access 2000 has a built in Round() function, although it doesn't work the same way that Excel's rounding function does and surprises those who expect it to be the same.
Charlotte