Adding totals of subreport to a group sum

[Manuel10]

Hello,
- I have a report based on a query.
- The report contains two grouplevels based on [Order_Id] and [Delivary_Id].
- The Delivary_Id group contains a.o. the field [Amount]
- In the footer of grouplevel Order_Id a total is given of all records of [Amount] that belong to a specific order: =sum([Amount]).
- The report shows all the delivaries categorised by the orders they belong to. And every order shows a =sum([Amount]). This all works well.

Now I have added a subreport in group level Delivary_Id based on QueryX that contains a.o. [Amount2]. For every Delivary_Id there are 1 or more records of [Amount2] that are shown on the report too.

Now I would like to add the sum of all the records of [Amount2] from all delivaries belonging to a specific Order_Id and add this to =sum([Amount]).
I have not been able to do this.

I allready have tried a lot, but now I think it must be something like:
=sum([Amount])+ Dsum("[Amount]";"[QueryX]";Criterium???)

But how to define the criterium?
Dsum("[Amount]";"[QueryX]";"Order_Id=1") for example works, but the Order_Id is not a fixed number here!

Can anyone help me out please?
Manuel, Netherlands.

 

[THWatson]

Manuel
You should be able to put an expression in the report footer. In the expression, refer to the subreport text box that has the total for the subreport. If there is the chance that the subreport might not have data, you need also to test for data using the HasData property.

Try something such as the following...

=IIf([YourSubreportName].[Report].[HasData]=-1,[YourSubreportName].[Report]![Amount2],0)

Then add this total to the other total from the main report.

Tom

 

[Manuel10]

Thanks for this tip Tom,
I still am figuring it out though, 'cause at the moment I still get a syntax error.
Regards, Manuel.

 

[Manuel10]

Hi Tom,

Your tip does work. With your suggestion I can put the total for a subreport in the footer.
But I cannot sum the totals of the subreport. The report nicely sums up the totals of Delivaries and subsequently of the Orders. But is does not sum the totals of subreport (belonging to the delivaries). So something like
=Sum(IIf([YourSubreportName].[Report].[HasData]=-1,[YourSubreportName].[Report]![Amount2],0)) does not work.
And when I use the function Dsum is only uses the 1st total and forgets the rest. Dsum was not made for use in page footers like the function sum is.

Maybe you have another tip?
Thanks, Manuel.

 

[Manuel10]

Hi Tom,
Like you say in other posts that you have answered: you cannot sum controls, but you can (sometimes) sum the expressions.
In my case we have: Name textbox: Amount2
Control Source: FieldnameX
When I simply sum the expression of the subReport as in =sum([FieldnameX]) and put it in the footer of the report then Access asks me for a value for FieldnameX.
Why can't it just use and sum the values of FieldnameX? It is as though it doesn't see the subreport.
Is it possible to somehow refer to it location in the subreport?
Regards,
Manuel.

 

[THWatson]

Manuel
Here are actual controls from a report of mine that uses subreports...
a text box called Text91 (this retrieves a total from one subreport)

=IIf([rptAcctSummarySubArchived].[Report].[HasData]=-1,[rptAcctSummarySubArchived].[Report]![Text35],0)

a text box called Text93 (this retrieves a total from the second subreport)

=IIf([rptAcctSumryCreditSubArchived].[Report].[HasData]=-1,[rptAcctSumryCreditSubArchived].[Report]![txtCreditRunTotalAmt],0)

a third text box which sums the first two text boxes

=[Text91]+[Text93]

All 3 of these are in the main body of the report.

Perhaps that points you to a solution for your project. Post back if you still have difficulty.

Tom