If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subreport Totals
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 |
#2
|
|||
|
|||
Subreport Totals
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. -- 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. "2NDNATURE" wrote in message ... 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 |
#3
|
|||
|
|||
Subreport Totals
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 |
#4
|
|||
|
|||
Subreport Totals
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 |
#5
|
|||
|
|||
Subreport Totals
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. |
#6
|
|||
|
|||
Subreport Totals
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] -- 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. "2NDNATURE" wrote in message ... 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. |
#7
|
|||
|
|||
Subreport Totals
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 |
#8
|
|||
|
|||
Subreport Totals
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 |
#9
|
|||
|
|||
Subreport Totals
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. -- 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. "2NDNATURE" wrote in message ... 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 |
#10
|
|||
|
|||
Subreport Totals
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 |
Thread Tools | |
Display Modes | |
|
|