Results 1 to 10 of 10
  1. #1
    New Lounger
    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>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 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

  3. #3
    New Lounger
    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

  4. #4
    5 Star Lounger
    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

  5. #5
    New Lounger
    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

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 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 floating-point 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.

  7. #7
    New Lounger
    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

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 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.

  9. #9
    New Lounger
    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

  10. #10
    Plutonium Lounger
    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

Posting Permissions

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