Totals on Main Report

[Guest]

Thanks for taking the time to read my question.

I have a main report with one small sub report.

I have them linked and it all works fine. The sub report displays one value
and is placed in the detail section.

I would like to have a total on the main report based on the sub report.

I have used to builder to create the formula, but it didn't work.
=Sum(rptExpenditures.rptBudgetAmt.Report!BudgetAmt) is what it gave me. I
am using Access 2003 (XP)

What do I need to fix to get a proper total.

Thanks,

Brad

 

[Allen Browne]

1. Open your subreport in design view.

2. If you do not see a Report Footer section, go to the View menu and click
Report Header/Footer. Set the Visible property of this section to No if you
do not want it displayed.

3. In the Report Footer section, add a text box with these properties:
Control Source =Sum([BudgetAmt])
Format Currency
Name txtSumBudgetAmt

4. On the main report, set this Control Source for the text box that should
show the total:
=IIf([rptBudgetAmt].[Report].[HasData],
[rptBudgetAmt].[Report].[txtSumBudgetAmt], 0)

That assumes the name of the subreport control is "rptBudgetAmt". The
control can have a different name than its SourceObject (the name of the
report loaded into the control).

If there are no records in the subreport, trying to refer to the
non-existent records generates an error. To avoid that, the expression
checks the HasData property.

 

[Guest]

Thanks so much for the reply Allen,

My appologies, I don't think I was clear enough.

What I need is a total on the main report.

My situation is:
I have expenses per category, per time period. I also have a budgeted
amount per category per time period. In the source query, I have one instance
of the budgeted number for every instance of an expence number. So if I have
3 expenses in one category/time pd, I get 3 budget amounts. So my total
budget per report is 3 times as big as it should be. If I make a sub report
of the budgeted amount and link it, I only get one budgeted amount per
category/time pd. This works well, except at the moment I can't get a total
of the budgeted amount on the main report as it is on the sub report.

I think what you told me was how to get a total on the sub report. The sub
report is on a footer of the category section so it shows up several times
per time period. My totals are per time period (it's a budget report), and
so I need to total all the various budget results for all the categories per
time period on the main report.

The text box on the Time Period footer that should hold the total budgeted
amount should look like:
=Sum(All Budget Amounts Shown that reside on the Sub Report)

Thanks again for the help.

Brad

1. Open your subreport in design view.

2. If you do not see a Report Footer section, go to the View menu and click
Report Header/Footer. Set the Visible property of this section to No if you
do not want it displayed.

3. In the Report Footer section, add a text box with these properties:
Control Source =Sum([BudgetAmt])
Format Currency
Name txtSumBudgetAmt

4. On the main report, set this Control Source for the text box that should
show the total:
=IIf([rptBudgetAmt].[Report].[HasData],
[rptBudgetAmt].[Report].[txtSumBudgetAmt], 0)

That assumes the name of the subreport control is "rptBudgetAmt". The
control can have a different name than its SourceObject (the name of the
report loaded into the control).

If there are no records in the subreport, trying to refer to the
non-existent records generates an error. To avoid that, the expression
checks the HasData property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for taking the time to read my question.

I have a main report with one small sub report.

I have them linked and it all works fine. The sub report displays one
value
and is placed in the detail section.

I would like to have a total on the main report based on the sub report.

I have used to builder to create the formula, but it didn't work.
=Sum(rptExpenditures.rptBudgetAmt.Report!BudgetAmt) is what it gave me. I
am using Access 2003 (XP)

What do I need to fix to get a proper total.

Thanks,

Brad

 

[Allen Browne]

So the subreport is giving the correct total, but you want to accumulate
these for a grand total on the main report?

Use the method in the previous post to bring the subreport total back onto
the main report. Assuming you named that text box "txtSubTotal", add another
text box, and set these properties.
Control Source =[txtSubTotal]
Running Sum Over All
Format Currency
Name txtSubtotalRS
Visible No

This gives you a running total from each of the subreports. Now in the
Report Footer section, you can place a text box with Control Source of:
=[txtSubreportRS]
so it shows the total accumulated from the subreports.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks so much for the reply Allen,

My appologies, I don't think I was clear enough.

What I need is a total on the main report.

My situation is:
I have expenses per category, per time period. I also have a budgeted
amount per category per time period. In the source query, I have one
instance
of the budgeted number for every instance of an expence number. So if I
have
3 expenses in one category/time pd, I get 3 budget amounts. So my total
budget per report is 3 times as big as it should be. If I make a sub
report
of the budgeted amount and link it, I only get one budgeted amount per
category/time pd. This works well, except at the moment I can't get a
total
of the budgeted amount on the main report as it is on the sub report.

I think what you told me was how to get a total on the sub report. The
sub
report is on a footer of the category section so it shows up several times
per time period. My totals are per time period (it's a budget report),
and
so I need to total all the various budget results for all the categories
per
time period on the main report.

The text box on the Time Period footer that should hold the total budgeted
amount should look like:
=Sum(All Budget Amounts Shown that reside on the Sub Report)

Thanks again for the help.

Brad

1. Open your subreport in design view.

2. If you do not see a Report Footer section, go to the View menu and
click
Report Header/Footer. Set the Visible property of this section to No if
you
do not want it displayed.

3. In the Report Footer section, add a text box with these properties:
Control Source =Sum([BudgetAmt])
Format Currency
Name txtSumBudgetAmt

4. On the main report, set this Control Source for the text box that
should
show the total:
=IIf([rptBudgetAmt].[Report].[HasData],
[rptBudgetAmt].[Report].[txtSumBudgetAmt], 0)

That assumes the name of the subreport control is "rptBudgetAmt". The
control can have a different name than its SourceObject (the name of the
report loaded into the control).

If there are no records in the subreport, trying to refer to the
non-existent records generates an error. To avoid that, the expression
checks the HasData property.

Thanks for taking the time to read my question.

I have a main report with one small sub report.

I have them linked and it all works fine. The sub report displays one
value
and is placed in the detail section.

I would like to have a total on the main report based on the sub
report.

I have used to builder to create the formula, but it didn't work.
=Sum(rptExpenditures.rptBudgetAmt.Report!BudgetAmt) is what it gave me.
I
am using Access 2003 (XP)

What do I need to fix to get a proper total.

Thanks,

Brad

 

[Guest]

Hi Allen,

I hope you get this post.

I have posted a web page showing you my report. I hope this makes more sense.
Making a sub total on the sub report doesn't make sense to me. Won't that
just display either the same value as the detail section (only one value ever
shows up in the detail section), or a total of all info that the query
returns. Both of these are not useful to me.

What I need is what is my formula to sum up the total of all the Budget
amounts, which reside in the subreport, and place that value on the main
report.

TotalBudgetAmtForReport =
Sum(Reports!MainReport!SubReport!BudgetAmoutTextBoxValue)

here is the link www.pierced.ca/Report.htm (this is my band web site, so I
just made up this page to show you.)

Thanks so very much for your help and for all your patience

Brad

So the subreport is giving the correct total, but you want to accumulate
these for a grand total on the main report?

Use the method in the previous post to bring the subreport total back onto
the main report. Assuming you named that text box "txtSubTotal", add another
text box, and set these properties.
Control Source =[txtSubTotal]
Running Sum Over All
Format Currency
Name txtSubtotalRS
Visible No

This gives you a running total from each of the subreports. Now in the
Report Footer section, you can place a text box with Control Source of:
=[txtSubreportRS]
so it shows the total accumulated from the subreports.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks so much for the reply Allen,

My appologies, I don't think I was clear enough.

What I need is a total on the main report.

My situation is:
I have expenses per category, per time period. I also have a budgeted
amount per category per time period. In the source query, I have one
instance
of the budgeted number for every instance of an expence number. So if I
have
3 expenses in one category/time pd, I get 3 budget amounts. So my total
budget per report is 3 times as big as it should be. If I make a sub
report
of the budgeted amount and link it, I only get one budgeted amount per
category/time pd. This works well, except at the moment I can't get a
total
of the budgeted amount on the main report as it is on the sub report.

I think what you told me was how to get a total on the sub report. The
sub
report is on a footer of the category section so it shows up several times
per time period. My totals are per time period (it's a budget report),
and
so I need to total all the various budget results for all the categories
per
time period on the main report.

The text box on the Time Period footer that should hold the total budgeted
amount should look like:
=Sum(All Budget Amounts Shown that reside on the Sub Report)

Thanks again for the help.

Brad

1. Open your subreport in design view.

2. If you do not see a Report Footer section, go to the View menu and
click
Report Header/Footer. Set the Visible property of this section to No if
you
do not want it displayed.

3. In the Report Footer section, add a text box with these properties:
Control Source =Sum([BudgetAmt])
Format Currency
Name txtSumBudgetAmt

4. On the main report, set this Control Source for the text box that
should
show the total:
=IIf([rptBudgetAmt].[Report].[HasData],
[rptBudgetAmt].[Report].[txtSumBudgetAmt], 0)

That assumes the name of the subreport control is "rptBudgetAmt". The
control can have a different name than its SourceObject (the name of the
report loaded into the control).

If there are no records in the subreport, trying to refer to the
non-existent records generates an error. To avoid that, the expression
checks the HasData property.


Thanks for taking the time to read my question.

I have a main report with one small sub report.

I have them linked and it all works fine. The sub report displays one
value
and is placed in the detail section.

I would like to have a total on the main report based on the sub
report.

I have used to builder to create the formula, but it didn't work.
=Sum(rptExpenditures.rptBudgetAmt.Report!BudgetAmt) is what it gave me.
I
am using Access 2003 (XP)

What do I need to fix to get a proper total.

Thanks,

Brad

 

[Allen Browne]

Brad, the expression you have won't work. If you follow back through the
last 2 replies, you will have a solution that (apparently) you have not
implemented yet.

There is an alternative to the subreport. Because "only one budget shows up
per category", you could include the BudgetAmt in the source query for the
report, and display the text box in the Category Group Footer section. Then
use a running sum (as explained in the last post) to collect a progressive
total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I hope you get this post.

I have posted a web page showing you my report. I hope this makes more
sense.
Making a sub total on the sub report doesn't make sense to me. Won't that
just display either the same value as the detail section (only one value
ever
shows up in the detail section), or a total of all info that the query
returns. Both of these are not useful to me.

What I need is what is my formula to sum up the total of all the Budget
amounts, which reside in the subreport, and place that value on the main
report.

TotalBudgetAmtForReport =
Sum(Reports!MainReport!SubReport!BudgetAmoutTextBoxValue)

here is the link www.pierced.ca/Report.htm (this is my band web site, so I
just made up this page to show you.)

Thanks so very much for your help and for all your patience

Brad

So the subreport is giving the correct total, but you want to accumulate
these for a grand total on the main report?

Use the method in the previous post to bring the subreport total back
onto
the main report. Assuming you named that text box "txtSubTotal", add
another
text box, and set these properties.
Control Source =[txtSubTotal]
Running Sum Over All
Format Currency
Name txtSubtotalRS
Visible No

This gives you a running total from each of the subreports. Now in the
Report Footer section, you can place a text box with Control Source of:
=[txtSubreportRS]
so it shows the total accumulated from the subreports.

Thanks so much for the reply Allen,

My appologies, I don't think I was clear enough.

What I need is a total on the main report.

My situation is:
I have expenses per category, per time period. I also have a budgeted
amount per category per time period. In the source query, I have one
instance
of the budgeted number for every instance of an expence number. So if
I
have
3 expenses in one category/time pd, I get 3 budget amounts. So my
total
budget per report is 3 times as big as it should be. If I make a sub
report
of the budgeted amount and link it, I only get one budgeted amount per
category/time pd. This works well, except at the moment I can't get a
total
of the budgeted amount on the main report as it is on the sub report.

I think what you told me was how to get a total on the sub report. The
sub
report is on a footer of the category section so it shows up several
times
per time period. My totals are per time period (it's a budget report),
and
so I need to total all the various budget results for all the
categories
per
time period on the main report.

The text box on the Time Period footer that should hold the total
budgeted
amount should look like:
=Sum(All Budget Amounts Shown that reside on the Sub Report)

Thanks again for the help.

Brad

:

1. Open your subreport in design view.

2. If you do not see a Report Footer section, go to the View menu and
click
Report Header/Footer. Set the Visible property of this section to No
if
you
do not want it displayed.

3. In the Report Footer section, add a text box with these properties:
Control Source =Sum([BudgetAmt])
Format Currency
Name txtSumBudgetAmt

4. On the main report, set this Control Source for the text box that
should
show the total:
=IIf([rptBudgetAmt].[Report].[HasData],
[rptBudgetAmt].[Report].[txtSumBudgetAmt], 0)

That assumes the name of the subreport control is "rptBudgetAmt". The
control can have a different name than its SourceObject (the name of
the
report loaded into the control).

If there are no records in the subreport, trying to refer to the
non-existent records generates an error. To avoid that, the expression
checks the HasData property.


Thanks for taking the time to read my question.

I have a main report with one small sub report.

I have them linked and it all works fine. The sub report displays
one
value
and is placed in the detail section.

I would like to have a total on the main report based on the sub
report.

I have used to builder to create the formula, but it didn't work.
=Sum(rptExpenditures.rptBudgetAmt.Report!BudgetAmt) is what it gave
me.
I
am using Access 2003 (XP)

What do I need to fix to get a proper total.

Thanks,

Brad

 

[Guest]

Thanks for the reply Allen,

Now that I read this post, I think I get it.

Thanks again

Brad

Brad, the expression you have won't work. If you follow back through the
last 2 replies, you will have a solution that (apparently) you have not
implemented yet.

There is an alternative to the subreport. Because "only one budget shows up
per category", you could include the BudgetAmt in the source query for the
report, and display the text box in the Category Group Footer section. Then
use a running sum (as explained in the last post) to collect a progressive
total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I hope you get this post.

I have posted a web page showing you my report. I hope this makes more
sense.
Making a sub total on the sub report doesn't make sense to me. Won't that
just display either the same value as the detail section (only one value
ever
shows up in the detail section), or a total of all info that the query
returns. Both of these are not useful to me.

What I need is what is my formula to sum up the total of all the Budget
amounts, which reside in the subreport, and place that value on the main
report.

TotalBudgetAmtForReport =
Sum(Reports!MainReport!SubReport!BudgetAmoutTextBoxValue)

here is the link www.pierced.ca/Report.htm (this is my band web site, so I
just made up this page to show you.)

Thanks so very much for your help and for all your patience

Brad

So the subreport is giving the correct total, but you want to accumulate
these for a grand total on the main report?

Use the method in the previous post to bring the subreport total back
onto
the main report. Assuming you named that text box "txtSubTotal", add
another
text box, and set these properties.
Control Source =[txtSubTotal]
Running Sum Over All
Format Currency
Name txtSubtotalRS
Visible No

This gives you a running total from each of the subreports. Now in the
Report Footer section, you can place a text box with Control Source of:
=[txtSubreportRS]
so it shows the total accumulated from the subreports.


Thanks so much for the reply Allen,

My appologies, I don't think I was clear enough.

What I need is a total on the main report.

My situation is:
I have expenses per category, per time period. I also have a budgeted
amount per category per time period. In the source query, I have one
instance
of the budgeted number for every instance of an expence number. So if
I
have
3 expenses in one category/time pd, I get 3 budget amounts. So my
total
budget per report is 3 times as big as it should be. If I make a sub
report
of the budgeted amount and link it, I only get one budgeted amount per
category/time pd. This works well, except at the moment I can't get a
total
of the budgeted amount on the main report as it is on the sub report.

I think what you told me was how to get a total on the sub report. The
sub
report is on a footer of the category section so it shows up several
times
per time period. My totals are per time period (it's a budget report),
and
so I need to total all the various budget results for all the
categories
per
time period on the main report.

The text box on the Time Period footer that should hold the total
budgeted
amount should look like:
=Sum(All Budget Amounts Shown that reside on the Sub Report)

Thanks again for the help.

Brad

:

1. Open your subreport in design view.

2. If you do not see a Report Footer section, go to the View menu and
click
Report Header/Footer. Set the Visible property of this section to No
if
you
do not want it displayed.

3. In the Report Footer section, add a text box with these properties:
Control Source =Sum([BudgetAmt])
Format Currency
Name txtSumBudgetAmt

4. On the main report, set this Control Source for the text box that
should
show the total:
=IIf([rptBudgetAmt].[Report].[HasData],
[rptBudgetAmt].[Report].[txtSumBudgetAmt], 0)

That assumes the name of the subreport control is "rptBudgetAmt". The
control can have a different name than its SourceObject (the name of
the
report loaded into the control).

If there are no records in the subreport, trying to refer to the
non-existent records generates an error. To avoid that, the expression
checks the HasData property.


Thanks for taking the time to read my question.

I have a main report with one small sub report.

I have them linked and it all works fine. The sub report displays
one
value
and is placed in the detail section.

I would like to have a total on the main report based on the sub
report.

I have used to builder to create the formula, but it didn't work.
=Sum(rptExpenditures.rptBudgetAmt.Report!BudgetAmt) is what it gave
me.
I
am using Access 2003 (XP)

What do I need to fix to get a proper total.

Thanks,

Brad