Subtracting aggregate fields in a report

[ProjectHelp]

Hi all,



I'm trying to create an expense report based off two tables. I have a table for Bouts, and a table for Bout transactions. In the Bout transaction table there is a field that specifies whether the transaction is an expense or a revenue.

I'm trying to create a report that will split the transactions out by expenses and revenues, then in the summary show the total income, (income - expenses.) I have the report formatted, but I'm not sure how to get the grand total, any help is appreciated.

I currently have the report laid out as such:


Bout Name

Revenue

Sample Revenue 1 $100
Sample Revenue 2 $50

Revenue Total: $150

Expenses

Sample Expense 1 $100

Expense Total: $100

Net Total: (I would like to subtract the 150 from 100 here)

[June7]

How have you 'split' the Revenue and Expenses records - by creating two group sections in report? There is a textbox in each section to sum each group? Then in the report footer have a textbox with expression that calculates difference of those two textboxes.

[ProjectHelp]

I have the report organized by transaction type, then the text box in the transaction type footer sums the transaction totals to give the Revenue or Expense totals.

Forgive my ignorance, I'm new in regards to creating Access reports, but how would I create an expression that references the two totals in the transaction type footer? Since they share the same name I don't know how I can reference each one individually.

[June7]

So Expenses and Revenues are not in separate group sections?

Would you like to provide project for analysis?

[ProjectHelp]

Sure, I've attached a zip file containing the two tables and the report. When you open the report a form will open and you'll select which bout you want to see a report of, then the report should pop up. Let me know what you think, and I really appreciate the help.

Project Sample.zip

[June7]

This expression in the boutname footer textbox seems to work:
=Sum(IIf([transactiontype]="Revenue",[TransactionAmount],0)-IIf([transactiontype]="Expense",[TransactionAmount],0))

[ProjectHelp]

That's exactly what I needed, thank you so much for helping me out June7!