Report Calculations

[Stonewall]

I have an accounting report in Access 2003 that looks like this: It is
grouped by Student and then by Payment Type (charges, payments, payroll).
The totals for the payment type groups come out fine but I can't figure out
how to calculate the balance due since the total field has the same name in
each payment type group.

Student ID: 1
Charges Tuitiion 5,000.00
Supplies 500.00
Total Charges: 5500.00 Field name
[totamount]

=Sum[amount])
Payments Tuition 4500.00
Books 500.00
Total Payments 5000.00 Field name
[totamount]

=Sum[amount])
Balance Due: ?????

How do I subtract Total Charges from Total Payments when the field for both
totals has the same name and what section of the report do I put it in? I
currently calculate the Total charges and payments in the [type] footer. The
totals have to be separated by student ID as well.

Thanks

 

[John Spencer]

I assume that you have a field that tell you the type of the amounts
entered.

In a control on the form you could enter the following as the control
source.

= Sum(IIF([Type]="Charges",[Amount],Null)) -
Sum(IIF([Type]="Payments",[Amount],Null))

(above all on one line)

You would need that calculation to take place in a group footer based on
the StudentID. Obviously you need to replace the field names with your
field names.