Unanswered: questiona about creating a payment ledger
Hi. Newbie here.
I'm working on trying to move old paper ledgers to access (I have notebooks full of them). Each page has an account with a balance that is being paid off. Payments are based on volume of use rather than month.
the easiest part is that each ledger entry pretty much takes the following formula:
Volume x Rate = Fee
Fee usually equals Posted Payment
The Current Balance = Previous Balance - Payment
Unfortunately, Fee doesn't always equal Payment (some payments are higher or lower than Fee for various reasons).
I'm recording the payments that don't equal Fee as AdjFee
Payment will show Fee if AdjFee is null
What I'm not sure about is how to set these up in a table (or tables) because I need to have Fee, AdjFee, Payment, and Balance recorded somewhere rather than have them calculated whenever a form is opened or a report is run.
I'm taking a leap here and making some assumptions.
You have an ACCOUNT , INVOICE table for recording customer info and 'volume actions' as invoices.
As you calculate the various dollar amounts (Fee, AdjFee, Payment, and Balance ), Those could be placed in the table CUSTOMER_BALANCE. As the calcuations are performed the updates would occur to this support table. It would make the reporting (to forms/reports) quick since you have SELECTed the CUSTOMER in some manner. The CUSTOMER_BALANCE table could also be broken down deeper by including YEAR. At the end of the 2003 year, roll the outstanding balances forward to 2004 entry.