Colin has attended:
Access Introduction course
Access Advanced course
Access Intermediate course
Excel VBA Intro Intermediate course
Inserting Totals in a Report ( Access )
Ok, need your help. I have taken a Text box for my Report Footer and inserted =Sum([exp]) in Control Source property...however am getting back #Error when I run the report.
Am I to put in another instruction in order to generate this .... ??
Help am really stuck,
Cheers,
Colin
RE: Inserting Totals in a Report ( Access )
Colin,
Often when creating reports with lists of transactions it can be useful to have a column which totals the transactions as the report progresses.
Using the Sum function gives the total for the whole group, not a running total.
Access provides an easy method of achieving a running total.
Click on the text box containing the data you wish to contain the running total.
Select the Data tab
Click in the Running sum option
Click the down arrow to view three options
No - This Item is not a running total
Over Group - This Item is a running total which will reset to zero with each new group value
Over All - This Item is a running total which will total over the whole report
Let me know if this fixes the problem.
Regards, Rich
RE: Inserting Totals in a Report ( Access )
Hello Rich,
Thank you for your support on this. Alas this still result in #Error , I tried Over Group as well as Over All ...same thing.
In the Control Source in the Data tab I inserted = Sum([Value of Waiver]) ... so I suspect that there maybe a missing element to all of this...any clues ?.
Cheers,
Colin
RE: Inserting Totals in a Report ( Access )
Hi Colin
Are you entering the actual field name in the formula.
ie: =sum([revenue])
unless you do this, access does not know what data to sum.
"Exp" means expression. It is the default placeholder for this formula
RE: Inserting Totals in a Report ( Access )
Hello Richard,
Thanks for your help. I made sure to be careful with the field name and found that if I put in Running Sum " Over Group " it puts in a total for each record ; whereas if I set it to " Over All " it would appear that it is pulling in figures from other fields ( I think ? ) to come up with numbers for each record that just do not tally .. if that makes any sense.
Ultimately I would like to put a Total in the footer of the report but think it is wise to try and sort this out first.
At least I do not get Error any more ...thanks very much.
Cheers,
Colin
RE: Inserting Totals in a Report ( Access )
Is the [exp] a field name from the report?
If not you need to replace the word exp with the name of the field to be calculated
eg:
To add all the [Quantity] values write it as
=Sum([Quantity])
RE: Inserting Totals in a Report ( Access )
Thank you Carlos,
Yes, I certainly did. What I am noticing is that it will give me totals but at each record, rather than just one total at the bottom of the page. I am assuming there is some grouping feature that I have to tick or activate to stop this ?.
RE: Inserting Totals in a Report ( Access )
Colin
If the Sum expression has been placed in the Report Footer area of the report it should produce only ONE result (Imediately after the last record).
However if you placed the Sum expression in the Group Footer area, it will place the total after every record unless you group the data.
To group data on a report that has already been created, make sure you have a group header section in the design view and move the value you want to group by into it.
To Edit a Grouping:
View/Sorting And Grouping OR
Click the Sorting And Grouping button on the toolbar
Carlos
