Summary Reports: Calculating the difference between two groups of data

Question

I set up a query and then built a summary report.  I grouped the data as follows:  revenue accounts = "a", expense accounts = "b".  My report sums all of the a's and the b' in each footer group.  In the report footer I want to subtract the sum of revenues "a" from the sum of expenses "b".  I can get a grand total in the report footer that sums all data from the groups but how do I subtract one group from another?  Help!

Answer 1

How do I write the formula to sum transaction amount for account type = "a" - sum transaction amount for account type = "b"? 

Try this:

Sum([Transaction Amount] * Switch([Account Type] = "a",1,[Account Type] = "b",-1,True,0))

For each pair of expressions in the parameter array the Switch function returns the second of the first pair encountered in which the first expression of the two is True.  So if the account type is "a" 1 will be returned, if "b", -1 will be returned, zero otherwise.  So, the amount will be multiplied by 1 or -1, or zero if the account type is neither.  The sum of these values is the equivalent of the sum of those where the account type is "a" less those where the account type is "b".

Answer 2

That works!  Thank you so much.  I had not heard of the switch function :-)

Answer 3

Thank you, that makes sense.  There is a text field called "account type" and a numeric field called transaction amount. How do I write the formula to sum transaction amount for account type = "a" - sum transaction amount for account type = "b"?

Answer 4

You will need to repeat the formulas, not refer to the control names. Probably something like:

=sum("a") - sum("b")

rather than:

= txtSumOfA - txtSumOfB