Subreport Totals

[2NDNATURE]

In the footer of my main report I would like to have a total from my subreport.
The subreport has the Total calculation [FundSum]- and it correctly sums all
values.
When I try the advice from previous threads on this site - referencing
[Subreport].[Report].[FundSum], Access returns a value that is not the entire
total (it is only a small percentage of the value). I have tried the
[HasData] and nz functions. They also return the incorrect value.
The other weird thing is that using [FundSum] it will return different
(incorrect) answers when I run the report, even though none of the underlying
data has changed.
I have also tried =Sum[Subreport].[Report].[Funds], but that does not work
either.
Any suggestions would be appreciated.
Thank You.
Maggie

 

[Marshall Barton]

In the footer of my main report I would like to have a total from my subreport.
The subreport has the Total calculation [FundSum]- and it correctly sums all
values.
When I try the advice from previous threads on this site - referencing
[Subreport].[Report].[FundSum], Access returns a value that is not the entire
total (it is only a small percentage of the value). I have tried the
[HasData] and nz functions. They also return the incorrect value.
The other weird thing is that using [FundSum] it will return different
(incorrect) answers when I run the report, even though none of the underlying
data has changed.
I have also tried =Sum[Subreport].[Report].[Funds], but that does not work
either.

I can not explain what you are seeing.

The "right" way to get that kind of total is to add a
(hidden?) text box (named txtRunTotal) next to the
subreport. Set tits control source expression to:

=IIf([Subreport].[Report].HasData,[Subreport].[Report].[FundSum],0)

and set its RunningSum property to Over All

The the main report footer's text box can display the grand
total by using the expression:

=txtRunTotal

 

[Allen Browne]

Maggie, what you describe is not normal behavior in Access. There must be
some other factor affecting what is going on here.

For example, you may have calculated expressions where Access does not
understand the data type correctly. Or you may have some expression that
involve a Null and they are not being handled correctly. Or you may have
some formatting applied that is masking the real values instead of showing
them as they really are. Or you may have a timing issue (such as a running
sum value that is being read too early.)

The solution will be to break the problem down into smaller chunks, until
you are able to pin down what's gone wrong.

 

[2NDNATURE]

Hi Allen.
Thank you for at least suggesting I'm not crazy.
All of the fields (in the tables, queries and reports) that I'm referring to
are formatted as Currency, with Auto for the decimal places.
None of the values in the underlying table I'm querying have any null values
for any of the fields I've queried.
Is there some other formatting property I should check?
I haven't used any running sums. Do you have a suggestion for how I check to
see if I have a timing issue?
The other weird thing. When I'm in the layout view of the report, as I'm
scrolling up and down in the report the value in the report will change. It
seems to reporting just one project's sum, instead of giving me the grand
total for all projects. But it isn't consistent in which project's total it
returns.
Thanks.
Maggie

 

[2NDNATURE]

One more thing I've noticed. When I right click the cell in Layout View and
select Total - Count Reords is highlighted, but all other options (Sum,
Average, Count Values, etc) are greyed out. Could that be part of the
problem? And is there a way to deselect it? Access is not allowing me to
change it at all. When it is selected and I select it again, it does not do
anything.
Thanks.
Maggie

 

[2NDNATURE]

I think I've figured out the problem, but I don't know how to correct it.
In my subreport, I have a field called [FundSum]. When just the subreport is
run, it totals all values. When it is run within the main report, it sums the
values by project. So in the detail part of the main report, I have the
individual values by project and then the sum by that project.
At the bottom of the main report I'd like it to give me the grand total
across all projects. But the per project totals are called [FundSum] it seems
to just select one project to total, instead of giving me the overall grand
total.
What's the best way to get around that.

 

[Allen Browne]

Add another text box beside your FundSum box, and give it these properties:
Control Source: =[FundSum]
Running Sum: Over All
Format: Currency
Visible: No
Name: txtFundSumRS

That text box will accumulate the subtotals as the report runs. Now you can
add another text box to the Report Footer, and set its Control Source to:
=[txtFundSumRS]

 

[2NDNATURE]

Hi Allen.
I tried what you suggested, but it is still returning only a sum by one of
the projects, instead of giving me a grand total. The summing in the
subreport works fine and it gives me the correct running total.
I've also tried running a separate query and report, that sums all of the
project values and then inserting that value into the report. Then it returns
the #Name? error. I've checked and re-checked the names and used the
expression builder, but in neither case does it give me a value.
Thanks for your help.
Maggie

 

[2NDNATURE]

Also, I've set up a dummy database, with just simple numbers. I am not able
to get anything to work in it either. I have two tables that are related to
each other by an ID field. The subreport produces all the correct numbers
(sums by group, sums overall). But when I try to put these sums in the main
report, it only reports a sum by one group.
Thanks.
Maggie

 

[Allen Browne]

You will need to break this down into steps.

Firstly, make sure that the text box you have in the Report Footer section
of your subreport is returning the correct value.

Then make sure that the main report is picking up the total correctly and
showing it on the main report.
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

Then pass that value to a running sum text box on the main report, and make
sure it is accumulating it.

Finally pass the total to a text box in the Report Footer of the main
report.

Take a deep breath, and take it one step at a time. Obviously we can't do it
for you, but I assure you that the process does work.

 

[2NDNATURE]

Yesssssssssssss.
It worked. Thank you so much for your help and your patience. Deep breath
taken.
If you ever in Santa Cruz, I'll buy you a beer.
Maggie