Counting and totaling from a subreport

[Tony Williams]

I have a report which has two sub reports which appear in the Detail
section. All the reports are based on different tables.
The main report has a header and footer for "Company"
Subreport 1 has a control called "VRM". In the Company footer I want to show
the value of the count of the control VRM on the sub report. How do I
reference that is it? I have tried
=Sum([Reports]![Subreport]![subreport1]![VRM]) but that doesn't work.
Similarly Subreport2 has a control called "balance". In the Company footer I
want to show the value of the sum (or Total) of the value of the sum of all
the Balances. so the report looks like this

Company Header
Company Name
Detail
Subreport showing VRM Subreport showing Balance
Company footer
Count of VRM Sum of Balance

I can get the detail section to show the correct information for each
company but cant show the totals in the Company footer.

Can anyone point me in the right direction.
Thanks
Tony

 

[Marshall Barton]

I have a report which has two sub reports which appear in the Detail
section. All the reports are based on different tables.
The main report has a header and footer for "Company"
Subreport 1 has a control called "VRM". In the Company footer I want to show
the value of the count of the control VRM on the sub report. How do I
reference that is it? I have tried
=Sum([Reports]![Subreport]![subreport1]![VRM]) but that doesn't work.
Similarly Subreport2 has a control called "balance". In the Company footer I
want to show the value of the sum (or Total) of the value of the sum of all
the Balances. so the report looks like this

Company Header
Company Name
Detail
Subreport showing VRM Subreport showing Balance
Company footer
Count of VRM Sum of Balance

I can get the detail section to show the correct information for each
company but cant show the totals in the Company footer.

Add another text box to the detail section next to the VRM
text box. Set its control source expression to refer to the
VRM count text box and set its RunningSum property to Over
Group. Then set the company group footer text box to refer
to the running sum text box. After you get that to work
make the running sum text box invisible.

 

[Tony Williams]

Sorry Marsh you lost me!
"Add another text box to the detail section next to the VRM
text box."
Presumably this is on the subreport?

"Set its control source expression to refer to the
VRM count text box"
Where is this? Do you mean the count box in the Company header of my main
report or is this on the subreport? If so how do I do that and what would be
the control source of the VRM count text box??


"Then set the company group footer text box to refer
to the running sum text box."

Which running sum text box is this?

Sorry if I appear dim I just couldn't follow your explanation (I normally
do!!!You've helped me a lot in the past.)

Thanks
Tony

I have a report which has two sub reports which appear in the Detail
section. All the reports are based on different tables.
The main report has a header and footer for "Company"
Subreport 1 has a control called "VRM". In the Company footer I want to
show
the value of the count of the control VRM on the sub report. How do I
reference that is it? I have tried
=Sum([Reports]![Subreport]![subreport1]![VRM]) but that doesn't work.
Similarly Subreport2 has a control called "balance". In the Company footer
I
want to show the value of the sum (or Total) of the value of the sum of
all
the Balances. so the report looks like this

Company Header
Company Name
Detail
Subreport showing VRM Subreport showing Balance
Company footer
Count of VRM Sum of Balance

I can get the detail section to show the correct information for each
company but cant show the totals in the Company footer.

Add another text box to the detail section next to the VRM
text box. Set its control source expression to refer to the
VRM count text box and set its RunningSum property to Over
Group. Then set the company group footer text box to refer
to the running sum text box. After you get that to work
make the running sum text box invisible.

 

[Marshall Barton]

Did I misunderstand your description? I thought you said
the main report's detail section contained a subreport that
calculates a total in it's footer and a main report detail
text box that displays the subreport total. It was this
latter text box that I was using as the basis for the main
report grand total calculation.

On rereading your question, I could also interpret what you
posted as the subreport total was only displayed in the
subreport's footer and that the total does not appear on the
main report. If this is what you have, then create a text
box named txtRunVRM in the main report detail section, set
the text box's expression to this kind of reference:
=subreportcontrol.Report.CountVRM
and set it's RunningSum property to Over Group

Then the company footer can use a text box with the
expression:
=CountVRM
to display the company grand total.

I think we could avoid a lot of confusion if you would post
the name of relevant subreport control and text box
controls in the main report and the pertinate subreport text
box names and their control source. Then we could talk
about specific items instead of trying to use verbose
descriptions of the items.
--
Marsh
MVP [MS Access]

Sorry Marsh you lost me!
"Add another text box to the detail section next to the VRM
text box."
Presumably this is on the subreport?

"Set its control source expression to refer to the
VRM count text box"
Where is this? Do you mean the count box in the Company header of my main
report or is this on the subreport? If so how do I do that and what would be
the control source of the VRM count text box??


"Then set the company group footer text box to refer
to the running sum text box."

Which running sum text box is this?

Sorry if I appear dim I just couldn't follow your explanation (I normally
do!!!You've helped me a lot in the past.)

I have a report which has two sub reports which appear in the Detail
section. All the reports are based on different tables.
The main report has a header and footer for "Company"
Subreport 1 has a control called "VRM". In the Company footer I want to
show
the value of the count of the control VRM on the sub report. How do I
reference that is it? I have tried
=Sum([Reports]![Subreport]![subreport1]![VRM]) but that doesn't work.
Similarly Subreport2 has a control called "balance". In the Company footer
I
want to show the value of the sum (or Total) of the value of the sum of
all
the Balances. so the report looks like this

Company Header
Company Name
Detail
Subreport showing VRM Subreport showing Balance
Company footer
Count of VRM Sum of Balance

I can get the detail section to show the correct information for each
company but cant show the totals in the Company footer.

"Marshall Barton" wrote
Add another text box to the detail section next to the VRM
text box. Set its control source expression to refer to the
VRM count text box and set its RunningSum property to Over
Group. Then set the company group footer text box to refer
to the running sum text box. After you get that to work
make the running sum text box invisible.

 

[Tony Williams]

Thanks Marsh sorry for the confusion. It's 19.55, enough for today. I'll get
my brain around this in the morning using the info from your second post and
if I don't, I'll post back tomorrow. Thanks a lot.
Tony

Did I misunderstand your description? I thought you said
the main report's detail section contained a subreport that
calculates a total in it's footer and a main report detail
text box that displays the subreport total. It was this
latter text box that I was using as the basis for the main
report grand total calculation.

On rereading your question, I could also interpret what you
posted as the subreport total was only displayed in the
subreport's footer and that the total does not appear on the
main report. If this is what you have, then create a text
box named txtRunVRM in the main report detail section, set
the text box's expression to this kind of reference:
=subreportcontrol.Report.CountVRM
and set it's RunningSum property to Over Group

Then the company footer can use a text box with the
expression:
=CountVRM
to display the company grand total.

I think we could avoid a lot of confusion if you would post
the name of relevant subreport control and text box
controls in the main report and the pertinate subreport text
box names and their control source. Then we could talk
about specific items instead of trying to use verbose
descriptions of the items.
--
Marsh
MVP [MS Access]

Sorry Marsh you lost me!
"Add another text box to the detail section next to the VRM
text box."
Presumably this is on the subreport?

"Set its control source expression to refer to the
VRM count text box"
Where is this? Do you mean the count box in the Company header of my main
report or is this on the subreport? If so how do I do that and what would
be
the control source of the VRM count text box??


"Then set the company group footer text box to refer
to the running sum text box."

Which running sum text box is this?

Sorry if I appear dim I just couldn't follow your explanation (I normally
do!!!You've helped me a lot in the past.)

Tony Williams wrote:
I have a report which has two sub reports which appear in the Detail
section. All the reports are based on different tables.
The main report has a header and footer for "Company"
Subreport 1 has a control called "VRM". In the Company footer I want to
show
the value of the count of the control VRM on the sub report. How do I
reference that is it? I have tried
=Sum([Reports]![Subreport]![subreport1]![VRM]) but that doesn't work.
Similarly Subreport2 has a control called "balance". In the Company
footer
I
want to show the value of the sum (or Total) of the value of the sum of
all
the Balances. so the report looks like this

Company Header
Company Name
Detail
Subreport showing VRM Subreport showing Balance
Company footer
Count of VRM Sum of Balance

I can get the detail section to show the correct information for each
company but cant show the totals in the Company footer.

"Marshall Barton" wrote
Add another text box to the detail section next to the VRM
text box. Set its control source expression to refer to the
VRM count text box and set its RunningSum property to Over
Group. Then set the company group footer text box to refer
to the running sum text box. After you get that to work
make the running sum text box invisible.

 

[Tony Williams]

Just an update. With your notes and looking at various help screens on
running sum I manage to get it to work. Something else I've learned to store
away in my overflowing brain!
Thanks again Marsh.
Tony

Thanks Marsh sorry for the confusion. It's 19.55, enough for today. I'll
get my brain around this in the morning using the info from your second
post and if I don't, I'll post back tomorrow. Thanks a lot.
Tony

Did I misunderstand your description? I thought you said
the main report's detail section contained a subreport that
calculates a total in it's footer and a main report detail
text box that displays the subreport total. It was this
latter text box that I was using as the basis for the main
report grand total calculation.

On rereading your question, I could also interpret what you
posted as the subreport total was only displayed in the
subreport's footer and that the total does not appear on the
main report. If this is what you have, then create a text
box named txtRunVRM in the main report detail section, set
the text box's expression to this kind of reference:
=subreportcontrol.Report.CountVRM
and set it's RunningSum property to Over Group

Then the company footer can use a text box with the
expression:
=CountVRM
to display the company grand total.

I think we could avoid a lot of confusion if you would post
the name of relevant subreport control and text box
controls in the main report and the pertinate subreport text
box names and their control source. Then we could talk
about specific items instead of trying to use verbose
descriptions of the items.
--
Marsh
MVP [MS Access]

Sorry Marsh you lost me!
"Add another text box to the detail section next to the VRM
text box."
Presumably this is on the subreport?

"Set its control source expression to refer to the
VRM count text box"
Where is this? Do you mean the count box in the Company header of my main
report or is this on the subreport? If so how do I do that and what would
be
the control source of the VRM count text box??


"Then set the company group footer text box to refer
to the running sum text box."

Which running sum text box is this?

Sorry if I appear dim I just couldn't follow your explanation (I normally
do!!!You've helped me a lot in the past.)

Tony Williams wrote:
I have a report which has two sub reports which appear in the Detail
section. All the reports are based on different tables.
The main report has a header and footer for "Company"
Subreport 1 has a control called "VRM". In the Company footer I want to
show
the value of the count of the control VRM on the sub report. How do I
reference that is it? I have tried
=Sum([Reports]![Subreport]![subreport1]![VRM]) but that doesn't work.
Similarly Subreport2 has a control called "balance". In the Company
footer
I
want to show the value of the sum (or Total) of the value of the sum of
all
the Balances. so the report looks like this

Company Header
Company Name
Detail
Subreport showing VRM Subreport showing Balance
Company footer
Count of VRM Sum of Balance

I can get the detail section to show the correct information for each
company but cant show the totals in the Company footer.


Add another text box to the detail section next to the VRM
text box. Set its control source expression to refer to the
VRM count text box and set its RunningSum property to Over
Group. Then set the company group footer text box to refer
to the running sum text box. After you get that to work
make the running sum text box invisible.

 

[Marshall Barton]

Just an update. With your notes and looking at various help screens on
running sum I manage to get it to work. Something else I've learned to store
away in my overflowing brain!

Glad you got it to work in spite of my mixed up control
names.

The RunningSum property is a powerful feature. You will
definitely want keep it in mind as a useful tool in many
different situations.