Need help with accounting database

I am working on a database that will contain an accounts recevable
section. I know what I want to do, but just keep drawing a blank when
it comes to doing it.

Here are the Feilds I'm working with

* ClientID - The Key that relates thoughout the whole db
* PrevBal - balance remaining after the 10th of the previous month
* PaymentsMade - Paments made between the 1st and the 29th of
previous month
* BalCarried - balance left after payments
* InterestFee - 1.5% added to BalCarried if Bal Carried is over 30
days
* AdditionalServices - New Fee's for current billing
* CurrentBalDue - BalCarried + Interest Fee + AdditionalServices

Invoices are made the last day of each month, and mailed on the 1st.
CurrentBalDue needs to be added to PrevBal on the first of each month.
Payments between dates need to be calculated to find BalCarried. If
BalCarried is over 30 days then interest needs to be applied.

Once that's done the AdditionalServices need to be totaled and added to
the calculation. Leaving CurrentBalDue.

Any ideas, hints, tips, help??

This is driving me nuts.

Marina

re: Need help with accounting database

My approach:
Have two table. One with your client's name, addy, and so on. No
balances needed here at this point.

The other with

TransID -- Auto Increment
Customer ID -- Same as you noted above
Trans Date -- Date of the Transaction

MonthEnd -- The month end of the transaction occured.
DateSerial(Year(Trans Date),Month(Trans Date)+1,1) which can be updated
with a query on a form closed.

TransactionType - Pmt, Service Charges, Interest, etc.

Amount - Payments should be negative. Service charge, and interest
should be positive.


On a report create you can use create a calculation Beg Bal.
dsum("[Amount]","tblTransaction","CustID = " & chr$(34) & [CustID] &
chr$(34) & "[MonthEnd] < " & [MonthSelected] &))

The ending bal Calulation is dsum("[Amount]","tblTransaction","CustID =
" & chr$(34) & [CustID] & chr$(34) & "[MonthEnd] <= " &
[MonthSelected] &))


Notice I have ditched the prev and currentBalDue fields in any table.

This is not exactly correct, but should give you ideas on how to
proceed.

re: Need help with accounting database

Thank you so much Dean, I'll give your idea a shot!!

re: Need help with accounting database

Thank you so much Dean, I'll give your idea a shot!!