Accounting DB

[CEH]

OK... My DB has began after looking at a few examples and after browsing a book called "Building Accounting Systems
Using Access 2002" A good book....BUT... One MAJOR item left out! The General Ledger. I have made the tables and forms to hold Vendor info, Transaction info, Job info, and a few others. Also have my forms to record checks and deposits, and record the distribution to different GL Accounts. My problem now is "How to build the General Ledger" My final goal will be to have a report that is a "Trial Balance" Showing all GL accounts, balances, and resulting in a final figure of zero. Thus being in balance.
So the question is... What is the best way to aproach this? All the info for the general ledger (at the moment) is input thru the "Checks form" or the "Deposit form" (a third to come will be for adjusting entries to the GL itself)
What is the common method? Using positive and negative numbers? A field for Debit and a second for credit?
Looking for someone to point me in the right direction before I get too far into it.
The DB is attached.... Lots of work and coding still to do... Everything is in a pretty basic form.. But it gives you a good idea of where I'm going. Any suggestions would be of value.
Thanks

 

[Pat Hartman]

I'm not sure what the current thinking is since I haven't worked with a General Ledger for at least 15 years but at that time the preferred method was a single field with a positive value and an assumed sign depending on the transaction type. So some transactions were credits and some were debits. Each transaction type had a cr/db indicator which controlled calculation and selection. I would use 1 and -1 for the indicators so that rather than having to use an If statement, I could always just multiply everything by the cr/db indicator.

 

[edtab]

I am not surprised that the book doesn't cover the General Ledger module since this is the most complicated part of an accounting system.

As far as having separate fields for debits and credits, or a single field with a debit/credit indicator, I must say that the system I am working with at the moment uses two separate fields (one for debit and another one for credit). This doesn't mean that it is superior to having just one field with a debit/credit indicator. Both could work well.

However, my preference would be to have two separate fields as this way, I know I have a ready field for either one. It makes life a little easier to get a total of the debits and credits since they are already represented by two separate fields. This will make the generation of your Trial Balance a much easier task.

Also, when entering a journal entry, you do not have to type in an extra character ( minus key ) to indicate that the amount just entered is a credit. It is much easier to just hit the enter key twice to get into the credit column then type in the amount rather then finding the minus key then entering the amount to get a credit into the system.

I am sure that a counter argument could be offered against having one field for debits and another for credits. I have not devoted time on that issue. In the end, it is up to you, the programmer to decide which one would be easier to code. As for me, I have decided to use two field method.

Good luck on your journey as a full-blown general ledger with full reporting (Trial Balance, Balance Sheet, Income Statement) is not going to be a cake walk. The Balance Sheet is going to be the most complicated one to complete successfully.

 

[CEH]

Thank you both for your input. Maybe I'll give both methods a shot and see which one is easier to use and maintain. As you have seen I have already done my tables,queries and forms for entering checks, deposits and producing P&L statements in summary or detail.
OK, so lets say I go with the two field method... I would add the "DRAmount" and "CRAmount" fields to my "GLAccount" table" and a "BatchID". This may be the point where I have seen some programs using a "Batching" method.... Not sure how it is done... but I am guessing this.... I would run a query of say all transaction with "Check" ID for dates between 09/01/05 and 09/30/05 and sum the totals by GLAccount number. This would give me totals for all checks in the month of September. Then copy the "Batch" totals to the Debit or Credit fields in the GLAccounts table. Sound like it would work???
BTW.... Let me know if there are any errors you see in the DB above.... or anything that might create future problems.
Thank you once again!
Curtis

 

[rahulgty]

Trial Balance

Hi

In my recent project I have also done some work on double entry system ledger. Though a lot of work is to be done in this project but the trial balance and further the group balance, account balance, Voucher detail, etc. has been completed. How ever I am not an expert, but still it may be beneficial for you. I would also like to take a look of your project.

rahulgty

 

[Malik786]

Hello Dear,

Asslam-o-alaikum


I am beginer in the database programming. i am student of computer sciences i meet a task to make "Accounting System" in ms Access. Sir I do'nt have idea about accounting system.please help me and send me copy of your project.
I am very thankful to you for your kind activeness.
Please Reply at tech_others@hotmail.com