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

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

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

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

Why don't you simply use the DSum() function ?

Access: DSum Function
DSum Function [Access 2003 VBA Language Reference]

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

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 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.

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