Totals from Report and SubReport

[bmoberly8888]

I have a report that I have added a subreport to but need a grand total.

The main report is fees and the the subreport is expenses.
I need to have a total at the bottom that will include both.

Originally I had this all in an append query -(and created a table) - which worked pretty well - except when I created a query from that table - it kept putting a $0.00 in the ExpAmount column - so that's why I keep getting one row with a $0.00 in it.)

 

[LonnieJohnson]

You could take the value on the main form and add it to the sum of the records on the sub form by using the DSum function. It would look something like this...

=[amount]+Nz(DSum("subAmount","mySubFormTable","ID = '" & [ID] & "'"),0)

The tick marks are there if the ID is a text field. If numeric then no tick marks like so...

=[amount]+Nz(DSum("subAmount","testCustomerSub","cust = " & [cust] ),0)

Hope this helps.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[dhookom]

You can total a numeric value on your main report with a basic expression like:
Control Source: =Sum([Fees])
You can have a text box in the Report footer of your subreport like:
Name: txtTotExpenses
Control Source: =Sum([Expenses])
Then in your main report, add a text box with a control source like:
=Sum([Fees]) + subrptCtrlName.Report!txtTotExpenses
This may need some modification if the subreport might not return any records or if the subreport is in a different section on the main report from the text box.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]