Accounting DB in ms access

[coyote]

I have developed an accounting db in ms access 2003 but am having a small problem with a part of it.
I have a table tblAccounts with the name of the account, the account ID, openingBalance and the currentBalance fields.
The operations involved are
Money can be withdrawn from the account
Money can be deposited in the account
To handle this I have four tables

tblWithdrawal and tblWithdrawalDetails to handle the withdrawals
tblDeposits and tblDepositDetails to handle the deposits
All the forms are there and update queries to handle the transactions

What I want is the user to view all the transactions and the Balance before the accounts were updated e.g asumming the account balance is $1000 and a withdrawal was done of $100 then a deposit was made of $ 200 then the user will view that the balance was $1000 then $900 and the current balance is $1100.
Short of storing the totals in a table I cant figure how to do this

 

[Rich]

Why separate tables for deposits and withdrawals?

 

[coyote]

The tables are separed because withdrawals are done by checks and deposits by deposit slips
which I have alredy designed plus remember the rules of data normalization.
The logic behind this is that any time the account transaction is saved the currnt balance in tblAccounts has to be updated.
But if anybody has another way of doing this then am very much appreciative.

 

[coyote]

am separating the tables because they involve different transactions
its like sales and purchases in northwind db
If any one has an idea how to go about this then I really appreciate the help

 

[namliam]

Seems to me like you should have only one table "account actions" and include a "Type" field...

Getting the account balance now would involve 2 tables which is messy, but can be done... Look into a union query to get where you want to be.

In this case I can see merit in actually storing the calculated value of "account balance" because of the fact that the history on which to base your account balance will get HUGE over time (many deposits and withdrawals).

 

[gemma-the-husky]

reconstructing a history is quite tricky - as it would depend on timestamping your transactions carefully.


You could

- store a separate start of month balance, and progress from there
- or store a running balance with every transaction (ok if you cant delete anything, and everything is in order)

- its harder with multiple tables, especially if you are storing transactions in both tables as positive values. - obviously income should be positive, and expenditure negative. if you only have 2 tran types its not too bad - if you have more it can become a bit unwieldy, because of the need for union queries

 

[Rich]

You only need one table to store transactions, whether you have separate fields for deposits and withdrawals is simply a matter of personal choice.

 

[CEH]

Accounting systems can be done different ways..... just depending on your preference... I will update this post when I get home tonight... I have a book you might still be able to find.. it's on Accounting systems using MS Access. I'll post the title and info for you. One thing I saw here I don't agree with.... I would use negative for income and positive for expenses. Income accounts are a normal credit balance as are liability and Capital. Assets and expenses are a normal debit balance. On the other hand there are ways to set it up so you would just enter if debit or credit.... Needless to say... Accounting DB's get VERY involved!

 

[GaryPanic]

I would have 1 table with
type - Cheque/transfer/withdraw/drawings ( or whatever)
behind these would be either -1 or 1

then your value and a date/time stamp

1 table then would effectiveloy be the history

and a form tied to table

accountid/name
amount
transaction type
date

then run qrys off this ..

 

[gemma-the-husky]

CEH

coyote (the poster) was talking about his bank account, and as far as the bank account goes income is positive, if we are having proper double entry

the double entry is

Cr income account with sales value
Dr bank account (or sales ledger account)

for an expense its
Dr expense account
Cr Bank (or purchase ledger account)

if you dont post this way, it makes double TB's very difficult

 

[neileg]

CEH

coyote (the poster) was talking about his bank account, and as far as the bank account goes income is positive, if we are having proper double entry

the double entry is

Cr income account with sales value
Dr bank account (or sales ledger account)

for an expense its
Dr expense account
Cr Bank (or purchase ledger account)

if you dont post this way, it makes double TB's very difficult

And the implicit convention is that credits are negative and debits are positive.

 

[CEH]

Quote:
Originally Posted by gemma-the-husky View Post
CEH

coyote (the poster) was talking about his bank account, and as far as the bank account goes income is positive, if we are having proper double entry

the double entry is

Cr income account with sales value
Dr bank account (or sales ledger account)

for an expense its
Dr expense account
Cr Bank (or purchase ledger account)

if you dont post this way, it makes double TB's very difficult
And the implicit convention is that credits are negative and debits are positive.

And I thought that is what I said.........."Income accounts are a normal credit balance as are liability and Capital. Assets and expenses are a normal debit balance."
And to clarify a statement that is confusing from above...."and as far as the bank account goes income is positive" The entry to the bank account to record a sale would be positive, a debit, the entry to the income account would be a negative, a credit.
From this thread I think anyone who is planning on designing an accounting DB can tell the first step would be to read up on GAAP.

 

[neileg]

From this thread I think anyone who is planning on designing an accounting DB can tell the first step would be to read up on GAAP.

I would start with Business Accounting Volume 1 by Frank Wood (available in all good UK bookstores and the 'bible' for trainee accountants for the last 40 years!)

 

[CEH]

Since "Coyote" is using dollar signs I'm guessing he is in the states. It's been 25 years since I've picked up an "Accounting I" book But I suppose any college level "intro to Accounting" book would cover the basics. Good thing is, they last forever. A debit is still a debit and a credit is still a credit!

 

[neileg]

Yes, Curtis, I know that. I was being a little ironic. For a layperson to get into GAAP is a bit of a leap!

 

[gemma-the-husky]

CEH

i thought you were saying the receipt in the bank account should be negative - i can see you undestand debits and credits

CEH and NEIL

Everybody knows that Debit is the side nearest to the Window!

Accounting systems can be done different ways..... just depending on your preference... I will update this post when I get home tonight... I have a book you might still be able to find.. it's on Accounting systems using MS Access. I'll post the title and info for you. One thing I saw here I don't agree with.... I would use negative for income and positive for expenses. Income accounts are a normal credit balance as are liability and Capital. Assets and expenses are a normal debit balance. On the other hand there are ways to set it up so you would just enter if debit or credit.... Needless to say... Accounting DB's get VERY involved!

 

[neileg]

Everybody knows that Debit is the side nearest to the Window!

When I was training, somebody switched my desk around. Took me weeks to rebalance everything!