Totaling Subreports

[tialongz]

Hi everyone,

First thing first, I am working with access 2003.

Here is the problem, I spent hours trying to figure out how to do this:
My report encompasses probably around 9-10 subreports (source=crosstabs). All these crosstabs are generated from the same main table. The reason, just in case someone asks, is that there are like 10 value fields need to be summed individually by year. A Crosstab only allows 1 value field. All the subreports have linked by the CustomerID.

I need to sum some of the subreports together, say 6 out of the 10. Anyone know how to do this? Do I need to create an another subreport just for the sum of the subreports?


Thanks in advance.

[andrew93]

Hi

You don't need another subreport for this. Make sure each subreport has a unique name and each subtotal in each subreport also has a unique name. Then create an unbound text box in your main report footer and give it a control source of something like this:

=MySubReport1!MySubReportTotal1 + MySubReport2!MySubReportTotal2 + MySubReport3!MySubReportTotal3 +etc

HTH, Andrew

[tialongz]

Hi

You don't need another subreport for this. Make sure each subreport has a unique name and each subtotal in each subreport also has a unique name. Then create an unbound text box in your main report footer and give it a control source of something like this:

=MySubReport1!MySubReportTotal1 + MySubReport2!MySubReportTotal2 + MySubReport3!MySubReportTotal3 +etc

HTH, Andrew

This is a beginner question, but this total is the summation of the first 5 subreports, there are still 4 subreports suppose to appear after it. If I insert all the subreports into the detail section, and type your function in my main report header, wouldn't the total come at the very end of the report? I need the total to appear after the 5th report.

[andrew93]

Hi

Is there anything preventing you from putting the total between the 5th and 6th subreports? It's hard to visualise what you are doing but if you are only after totals (within the subreports) could you use a DSum function instead of a subreport? Also, if you want each subreport to only appear once (witihn the entire report), have you considered inserting the subreports into the report header or footer instead of the detail section?

Andrew


This is a beginner question, but this total is the summation of the first 5 subreports, there are still 4 subreports suppose to appear after it. If I insert all the subreports into the detail section, and type your function in my main report header, wouldn't the total come at the very end of the report? I need the total to appear after the 5th report.

[XLGibbs]

You can also combine all totals into 1 query....there are ways to create Crosstab "feel" with Select query syntax and "Group By" totalling using creative column creation. I can help with this if you like...

You might also consider creating your report in excel and linking to the main table and having a MS Query feed directly into a formatted pivot table. You could open and refresh...and have a dynamic, refreshable "CrossTab" query with all the bells and whistles (A crosstab query is a 1 value pivot table, but Pivot tables can have many calculated fields and options...including customized subtotals)