Report Calculations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stonewall - 28 Jan 2007 16:17 GMT

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

Reply to this Message

John Spencer - 28 Jan 2007 17:47 GMT

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.

---
John Spencer
Access MVP 2001-2005, 2007

> 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).
[quoted text clipped - 23 lines]
>
> Thanks

Reply to this Message