Help with a sum on a report footer using values from a subreport.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FatMan - 09 Mar 2007 13:36 GMT

Hi all:
I am trying to calculate a total on a report footer but am having no luck.
I want ot sum one of my fields from the detail section of the report that
uses values from a subreport.

Detail section of main report:
text box name: txtContainerSum
control source of txtContainerSum: =[qrWipMaterialSum
subreport].Report!txtContainerSum/[SumOfQtyManufactured]

I have tried.....
a) sum([txtContainerSum]) - resulted in the report displaying a "parameter"
prompt

b) sum(([qrWipMaterialSum
subreport].Report!txtContainerSum/[SumOfQtyManufactured])) - resulted in the
report displaying a "parameter" prompt

Can anyone please tell me what I am doing wrong? If it helps I am using
Access 2000.

Thanks,
FatMan

Reply to this Message

Allen Browne - 09 Mar 2007 14:23 GMT

If the subreport is not in the Detail section, then move txtContainerSum
into the same section as the subreport. Hopefully SumOfQtyManufactured is in
the same section in the end.

I take it you want to collect the progressive total of this text box. Do
that by adding another text box to the same section, and give it these
properties:
Name txtContainerSumRS
Runing Sum Over All
Visible No
This hidden text box collects the running sum across all instances of the
subreport.

Now set the Control Source of the text box you have in the report footer
section to:
=[txtContainerSumRS]

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.

> Hi all:
> I am trying to calculate a total on a report footer but am having no luck.
[quoted text clipped - 21 lines]
> Thanks,
> FatMan

Reply to this Message

FatMan - 12 Mar 2007 13:21 GMT

Allen:
Thanks for your reply.

The subreport is in the detail section. I didn't understand the rest of
your reply to be honest....I must be dumb.

On the subreport in the report footer I have a text box calculating the
totals and they work on the subreport. When I bring the fields forward to
the main report the values displayed are those of my last grouping and not
the report total.

The control source on my main report is: =[qrWipMaterialSum
subreport].Report!txtLabourDirSumTotal

What am I doing wrong? Why do I get the subtotal for my last group and not
the report total when it displays the correct total on the subreport?

Help please!

Thanks,
FatMan

> If the subreport is not in the Detail section, then move txtContainerSum
> into the same section as the subreport. Hopefully SumOfQtyManufactured is in
[quoted text clipped - 38 lines]
> > Thanks,
> > FatMan

Reply to this Message

Allen Browne - 12 Mar 2007 13:48 GMT

I'm not sure I have understood you correctly.

If you use a running sum in your subreport, you can get the behavior you
describe. Use a nomal sum there if possible.

The main report will look like this in design view:
,--------------------------------

| Detail section
| ,------------------------
[quoted text clipped - 10 lines]
| Report Footer section
| [T5]

'---------------------------------

T1 - T5 are 5 text boxes, with properies like this:
- T1:
Control Source: Container (or whatever your field is called.)

- T2:
Control Source: =Sum([Container])

- T3:
Control Source: =IIf(qrWipMaterialSum.Report.HasData,
Nz(qrWipMaterialSum.Report!txtContainerSum,0), 0)
Name: txtSubtotal

- T4:
Control Source: =[txtSubtotal]
Running Sum: Over All
Visible: No
Name: txtSubtotalRS

- T5:
Control Source: =[txtSubtotalRS]

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.

> Allen:
> Thanks for your reply.
[quoted text clipped - 66 lines]
>> > Thanks,
>> > FatMan

Reply to this Message

FatMan - 12 Mar 2007 15:31 GMT

Allen:
Thank you for your help. I understood your second reply and as usual you
were rignt on the money.

Thanks for the help, I greatly appreciated it.

Take care and god bless.
FatMan

> I'm not sure I have understood you correctly.
>
[quoted text clipped - 110 lines]
> >> > Thanks,
> >> > FatMan

Reply to this Message