Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    Unanswered: Getting a Grandtotal from a Subtotal with Rounding issues

    This is my first post.

    I have an issue that should be simple but it is not coming to me.

    I have a report that is using a Aggregate Sum of an amount to populate a control for a EmployeeTotal. The field that it is summing has 3 decimal positions. The Employee total rounds to 2 positions.

    I also have a GrandTotal in the footer summing the same field but because of rounding it is one penny off if you add all of the EmployeeTotals together.

    What is the easy way to sum the EmployeeTotals. I can not use the Sum since it is not a database field. I guess.

    I thought about using a variable to calculate a running total but am unsure what events to use to make this happen easily.

    Or is there an SQL statement that could easily sum the sum so to speak and populate a grand total control on the report?

    Hope this makes sense.

    Thanks in advance.

    Steve

  2. #2
    Join Date
    Feb 2010
    Posts
    6
    Ok.

    I am using a global variable to hold a running total of my EmployeeTotal and then using a function to put it on the report. Setting the variable to zero on the Report On load event. Looks like it might work. If there is a better solution I would like to hear about it. Thanks. Steve

  3. #3
    Join Date
    Feb 2010
    Posts
    6
    Ok.

    As I am talking to myself here. I have the rounding issue resoved and the Grand total I am looking for but I have it in my Report Footer Section.

    I did have the GrandTotal in the Header Section of the report but I am guessing I was able to do this do to the fact I was using the Sum which is a from the database (which is available at the time the header is printed).

    Any ideas on getting my new GrandTotal from the footer to the Header?

    Thanks again. Steve

  4. #4
    Join Date
    Feb 2010
    Posts
    6
    Hmmm.

    Letting the report handle the record flow and handing the Rounding for the Employee Subtotal and then using a variable to total the Employee SubTotal for my Grandtotal works but that grandtotal is only available in the report footer.

    I guess I could write a function that loops through the records adding and rounding for the grandtotal and then call that function from the Header section would work but that sure seems like alot of work to correct a rounding error of a penny

    Oh well.

    Steve

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you simply use the DSum() function ?

    Access: DSum Function
    DSum Function [Access 2003 VBA Language Reference]
    Have a nice day!

  6. #6
    Join Date
    Feb 2010
    Posts
    6
    Sinndho,

    thanks for your reply.

    When using an aggregate function against the table I have rounding issues.

    Example:

    Table contains

    EmployeeId Hours(3 decimals) Rate(3 decimals) ExtendedAmount: Hours*Rate (3 decimals)

    I use the Sum function in the Footer section of the report grouping on Employee ID. I have to do some special Rounding on that Sum. The sum is 2 decimals.

    Now because I am handling the rounding at the Employee total level. I have to Sum the Employee totals for the report Grandtotal. Otherwise due to the rounding the report is off a penny or so at times.

    Hope that makes sense. Steve

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    set the values in the report to ROUNDed values then sum thiose values

    if that doesn't workj then place some code behind the reports detail format to do the match as you expect
    bear in mind you will need to reset your values for each group change
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2010
    Posts
    6
    I did get that all to work. Now I was hoping to get the Grand Total in the Header section of the report, which seems to be a problem unless you can use an aggregate Sum function against a table. Calculating the Grandtotal using a variable only makes it available in the Footer of the report. Which they should be able to live with.


    Quote Originally Posted by healdem View Post
    set the values in the report to ROUNDed values then sum thiose values

    if that doesn't workj then place some code behind the reports detail format to do the match as you expect
    bear in mind you will need to reset your values for each group change

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so continue the same process.. declare a variable for the grand total, set it to 0 on report open, add ot it as required and then assign the value of that variable to a control in the report footer. the access report module is far more powerfull then many people realise, especially if they never venture far from the GUI layout and the report wizards. I find I tend to use a lot of custom designed reports using bits of VBA and queries to generate reports that are more to the customers liking
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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