>Who has a good idea about how to set up tables and
>procedures to facilitate creating monthly statements,
>based on charges and payments, that will age balances,
>30, 60 90, 120 days?
I don't know how other people have done it, but since most of the aging stuff is only needed in reports (at least here), in my "aging" report I take the invoice date (ie. "due date") and subtract it from the current date. In the report I have three columns:
< 31 < 61 < 91
Under each column I have a command that checks the aging of it, and displays the (invoice amount-paid amount) in the correct column. I don't have the exact code here, but it's kind of like:
(first column)
IIF(oAR.AgingDate(Invoice.Date) <= 31 ,oAR.Balance(Invoice.Number),"")
(second column)
IIF(oAR.AgingDate(Invoice.Date) <= 61 ,oAR.Balance(Invoice.Number),"")
(third column)
IIF(oAR.AgingDate(Invoice.Date) <= 91 ,oAR.Balance(Invoice.Number),"")
oAR.AgingDate: Takes invoice date and returns # days aged
oAR.Balance: Takes an invoice # and returns the balance
Then in the report I set each of the columns to "Don't print if empty".
You can apply different filters to the data to display/print only one customers account, or all accounts, etc.
As far as the payments, I would create a single table that had fields for invoice # (or foreign key) in it, the customer # (or FK), the amount paid, the date, method of payment, check #, cc#, exp. date, credit card type. Then when you wanted to show all the payments for a customer/invoice it's pretty easy to create a view or SQL select that can grab the info. you're interested in (based on customer #, invoice #, or both). It can get a little more complex if you also need to apply payments to a specific G/L #, or split G/L, but hopefully this gives you some ideas.