access database training - inserting totals report

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Access Training and help » access database training - Inserting Totals in a Report ( Access )

access database training - Inserting Totals in a Report ( Access )

resolvedResolved · Low Priority · Version Standard

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

Edited on Wed 25 Apr 2007, 09:48

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

RE: Inserting Totals in a Report ( Access )

Thank you Carlos, happy to say it worked !!.

Really appreciated your patience and help.

Cheers,

Colin


Server loaded in 0.08 secs.