Name the TOTAL control on each report something easy and distinctive.
The expression in each of those unbound controls will be something
like "=sum([linetotal])" assuming that [linetotal] is a field from
your query.
Add each one of those subreports (also with an easy and distinctive
name) to your main report. For the Grand Total on your main report,
create another unbound textbox, and make its control source something
like
"=[myMainTotal]+(Reports![rptMyMain]![Report.rptSub1].Report!MyTotal1)+Reports![rptMyMain]![Report.rptSub2].Report!MyTotal2)...."
-Ed
Ed Robichaud wrote:
Oops! That should have read:
You'll need to use the correct syntax to
refer to controls on subreports, and if those are themselves
expressions, you'll need to repeat the expressions, and not refer
to the control names - i.e. you can NOT have a calculated control
reference another calculated control.
OK, an example of syntax needed to refer to a control on a subreport
(when you are currently on the main report):
"Me!mySubreportName.Report!myControlName"
Me is a shortcut for - "Reports!myMainreportName"
-Ed
Ed Robichaud wrote:
Yes, you can have a grand total of controls from all
subreports+main. Create an unbound textbox and set its data source
to an expression that adds the values in the other controls.
You'll need to use the correct syntax to refer to controls on
subreports and if those are themselves expressions, you'll need to
refer to the expression not the control name - e.g. you can have a
calculated control reference another calculated control.
-Ed
I created a report and subreport from two different
and unrelated queries. One groups monthly sales by
customer for a few of our best customers. The second
query groups our largest customer monthly sales by
dept. The reports work OK. What I would like to do is
add the totals from both reports to get a Grand total.
Is this possible?
gls858
Ed,
Thanks for the info. I guess now it's just a matter of getting the
right syntax.
gls858
Ed (and anyone else that would care to help),
Thanks for the help. I can't seem to get the syntax right.
If I have a report that shows a total for 25 customers on
25 different lines. Do I need to get a grand total for these
and the a grand total for the other report THEN add them together?
I can easily get a grand total on each report but I can't seem to get
them added together. Here are the sums from the queries:
Query name: DeptMonthlyTotalsQry
SELECT ECNHDetail.Department, Sum(ECNHDetail.LineTotal) AS
SumOfLineTotal
Query name: CustMonthlyTotalsQry
SELECT ECNCMaster.CustomerName, ECNHDetail.CustomerNumber,
Sum(ECNHDetail.LineTotal) AS SumOfLineTotal
I just pasted the part of the query that refers to the sums. If
you need more info just let me know.
Thanks,
gls858