Taking sum of a field from sub report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 13 Sep 2007 07:18 GMT

Hi

On a sub report I have a field datetot which is the sum of two fields those
two fields are in turn coming from two further sub reports of the sub
report. How can I take the sum of datetot field to the main report?

Thanks

Regards

Reply to this Message

Allen Browne - 13 Sep 2007 07:39 GMT

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> On a sub report I have a field datetot which is the sum of two fields
> those two fields are in turn coming from two further sub reports of the
> sub report. How can I take the sum of datetot field to the main report?

Reply to this Message

John - 13 Sep 2007 08:00 GMT

Thanks for that. My problem has one more level in that the field 'Amount' on
sub report is defined as =[SumA] + [SumB] where SumA and SumB are themselves
coming from sub reports on the sub report. Hopefully the diagram below
explains it;

Report

|-------------Sub Report

| Amount =[SumA] + [SumB]
|
| SumAmount = sum([Amount]) gives #Error
|
|--------- Sub Sub Report A
| SumA = Sum([A])
|
|
|--------- Sub Sub Report B
SumB = Sum([B])

When on sub report I try to do =Sum[Amount] I get #error. Any solution to
that?

Thanks

Regards

> See:
> Bring the total from a subreport onto a main report
[quoted text clipped - 4 lines]
>> those two fields are in turn coming from two further sub reports of the
>> sub report. How can I take the sum of datetot field to the main report?

Reply to this Message

Allen Browne - 13 Sep 2007 08:07 GMT

So you have the Amount field working in the main subreport, but when you try
to sum this Amount field in the Report Footer section of the main subreport,
it doesn't work?

Use a running sum text box to collect the total.
It's properties might look like this:
Control Source =[Amount]
Running Sum Over All
Format Currency
Name txtAmountRS
Visible No

Now in the Report Footer section of the main subreport, add another text box
with Control Source of:
=[txtAmountRS]

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks for that. My problem has one more level in that the field 'Amount'
> on sub report is defined as =[SumA] + [SumB] where SumA and SumB are
[quoted text clipped - 29 lines]
>>> those two fields are in turn coming from two further sub reports of the
>>> sub report. How can I take the sum of datetot field to the main report?

Reply to this Message

John - 13 Sep 2007 15:16 GMT

Thanks. The running total works when sub form is run on its own but not as
part of the main report.

Regards

> So you have the Amount field working in the main subreport, but when you
> try to sum this Amount field in the Report Footer section of the main
[quoted text clipped - 45 lines]
>>>> those two fields are in turn coming from two further sub reports of the
>>>> sub report. How can I take the sum of datetot field to the main report?

Reply to this Message

Allen Browne - 13 Sep 2007 15:47 GMT

If the main report has records of its own, the subreport "exists" in
multiple separate instances, each indepenent of the others. You will not be
able to accumulate the subreports totals across its multiple instances.

If that's the problem you are seeing, you will need to pass back the
accumulated total from the subreport onto the main report. You can then use
another text box to accumulate the subtotals into a grand total on the main
report.

(Hope this approach works for you: there can be timing issues between when
the main report reads the subreport's subtotal and when the subreport has
completed accumulating the values.)

An alternative approach might be to just use a DSum() expression in the main
report's Report Footer section, though this becomes more difficult if the
report could be filtered (especially since Access doesn't maintain its
FilterOn property reliably.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks. The running total works when sub form is run on its own but not as
> part of the main report.
[quoted text clipped - 51 lines]
>>>>> the sub report. How can I take the sum of datetot field to the main
>>>>> report?

Reply to this Message