AnsweredAssumed Answered

Trouble with Summary Field

Question asked by ABuchtel on Aug 8, 2014
Latest reply on Aug 8, 2014 by ABuchtel

Title

Trouble with Summary Field

Post

     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: 

     NAME                   TYPE

     BillID                     Text (Auto-Enter Serial)

     fk_EventID            Text

     DueAmount           Number

     DueDate                Date

     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: 

     NAME                   TYPE

     PayID                    Text (Auto-Enter Serial)

     fk_BillID                Text

     fk_EventID             Text

     PaidAmount          Number

     PaidDate                Date

     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

     c_SumBills            c_SumPayments

      

     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!

Outcomes