I've been chugging along merrily on a solution to track payments to our accounting department, but I seem to have hit a wall. Hopefully it's not a dead end!
We receive payments from customers and need to track the multiple line items that a single payment can cover so that the money gets distributed to the correct accounts. I've got a table that contains information about the receivable (check #, total amount, date received, payer, etc.) Another table contains the accounts with their account numbers and descriptions. And I've got a join table called (perhaps confusingly) "payments" which connects an account and an amount with a receivable. For example, one record there would be
Receivable_ID, amount, account_code_id
Which would tell me, for example, that $50 from check #12345 should go into the sales tax account. Another record might tell me that $25 from that same check goes to membership fees.
I'm stuck though trying to build a report that will break out the subtotals for each account on a line with the receivable and total each column. The accounting department needs this for purposes of depositing the money to the bank and having each account funded correctly.
My plan was to build a list view in the receivables table, display check number and payer and total amount, and then I'd have a separate portal into the payments table, filtered by account code, for each account. I'd label each account name in the header. I hope that makes sense.
And that works fine, but I don't know how to total each account at the bottom of the page. Putting a summary field in the portal doesn't work, I assume because filtering is only for display purposes.
I'm afraid I'm out on the shaky end of my skillset here and I feel like I must be overlooking an obvious solution. Any ideas?
Thanks much.