Trouble with Summary Field
Good morning! I have three Tables, “Events”, “Bills” and “Payments”.
For Every Event there are typically two Bills, sometimes more. The first Bill is typically the “Deposit” required. The second Bill is typically the “Balance Due”. Sometimes there are Additional Charges. Typically a client will pay a “Bill” in full with one Payment, but there are times when multiple payments are made towards one “Bill”.
The Bills Table has the fields:
BillID Text (Auto-Enter Serial)
c_SumBills Summary = Total of DueAmount
c_MonthDue Calc = If ( Month (DueDate) < 10 ; Year (DueDate) & "0" & Month (DueDate) ; Year (DueDate) & Month (DueDate) )
The Payments Table has the fields:
PayID Text (Auto-Enter Serial)
c_SumPayments Summary = Total of PaidAmount
c_MonthPaid Calc = If ( Month (DueDate) < 10 ; Year (DueDate) & "0" & Month (DueDate) ; Year (DueDate) & Month (DueDate) )
The Bills and Payments Tables are Joined (=) by the BillID field.
I have a report, based on the Bills table, that shows, for each Event:
Amount Billed Amount Paid
The report is sorted by c_MonthDue then DueDate, so there’s a sub-total after every month.
The Body of the Report works perfectly.
In the Sub-Summary, the c_SumBills displays the Total Amount Billed for all listed Events. But, the c_SumPayments displays *only* the last Amount Paid (as displayed in the Body).
And in the Trailing Grand Summary c_SumBills displays the Total Amount Billed for all listed Events in all months, but c_SumPayments is entirely blank.
If anyone can point out where I’ve gone astray, I would greatly appreciate it. Thank you!