By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Need help with accounting database

P: n/a
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
* 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.


Jan 2 '06 #1
Share this Question
Share on Google+
3 Replies

P: n/a
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

Jan 3 '06 #2

P: n/a
Thank you so much Dean, I'll give your idea a shot!!

Jan 3 '06 #3

P: n/a
Thank you so much Dean, I'll give your idea a shot!!

Jan 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.