Grand total of field on a subreport

[degras]

How is it possible to calculate and show a grand total of a totals field from a subreport?

e.g. looking something like the following

Record 1
Subreport Record 1 ... Total: 7
Subreport Record 2 ... Total: 10
Subreport total: 17



Record 2
Subreport Record 3 ... Total: 12
Subreport Record 4 ... Total: 8
Subreport total: 20

Report Grand Total: 37

I don't know how to reference the totals field (on the subreport) from the main report.
I tried Sum(Me!ItemsSubreport.Report!Totals) but the editor put brackets around each word and then I was asked for the value in a dialogue when I ran the report.

[pbaldy]

The syntax is the same as forms:

Forms Refer to Form and Subform properties and controls

substituting Reports/Report for Forms/Form. Me is only valid in VBA, so would not work in a control source. You can also right click in the textbox and choose Build. Navigating to the subreport will build the correct syntax.

[degras]

The syntax is the same as forms:

Forms Refer to Form and Subform properties and controls

substituting Reports/Report for Forms/Form. Me is only valid in VBA, so would not work in a control source. You can also right click in the textbox and choose Build. Navigating to the subreport will build the correct syntax.

I don't see how that will work. Nothing seems to work as a control source with Sum(x), Access asumes that x is a field or a parameter. Anything you put in place of x and Access usually puts square brackets around it, turning it into a report parameter.

I could not find Sum in the expression builder.

[pbaldy]

You would just refer to the subreport textbox that contains the value you want.

[degras]

You would just refer to the subreport textbox that contains the value you want.

I don't know what you mean, because the sum (grand total) would need to be calculated over all subreports, not just one subreport.

[pbaldy]

Well, you said "a subreport", but it really doesn't matter. The Sum() needs to happen in the subreport. In the main report instead of =Sum(...) you just refer to the textbox with the grand total in the subreport.

[degras]

You are getting the grand total and the total confused. Please look again at my example. The grand total is not in the subreport, it needs to be on the main report as it is the sum of the totals over all subreports.

I am having problems referring to controls from within the Sum function, Access interprets the control as a parameter.

If I add a group footer with a total then Access only allows sums of a field in the table in the main report not the subreport. I could use this method if I could store the total from the subreport as a field on the main table. The problem would be: how would I get this field updated?

I don't think I can use DSum either, as the company name (that is the grouping for the grand total) is not stored on the subreports table. In any case the filter for DSum would have to come from the a reported field and I am not sure this is possible.

Or is there some other way of doing this grand total?
...

I solved this, in the end I used a totals field on the main table and updated it with the value of a Sum control on the subreport. I had to use Me.Recalc to make sure the Sum was recalculated.

[pbaldy]

As you've found, you can't refer to a control in a Sum() formula. I expected you would have =Sum(...) in the subreport's report footer section in addition to the group footer. It's that textbox in the report footer you would refer to from the main report.

[degras]

Thanks for your help.

[pbaldy]

No problem; sorry for the confusion.