Creating a double entry book keeping system
Can anyone please tell me how to create a basic double entry book keeping system database using SQL Server or Access. I need to know what tables amd fields I need to create, and what relationships need to be defined. I just need to know the db structure here. Also how should I go about designing the forms ( I use visual Basic 2005 Express and SQL Server 2005 Express, and Access 2002 ), the front end user interface for creating journal entries, how should I display the ledgers, and how do I calculate the totals for the ledgers, because we also need to take into consideration the opening and closing balances as well.
I tried a lot using Access, but I could not get a satisfactory result. I first created a account base type table which has the four account types - assets, liabilities, expenses, and incomes, linked to aother table which stores the actual account types classifications ( which is actually used only for grouping purposes in the reports ), which in turn is linked to the accouts table which has the names of all the accounts. Now I tried to create a journal whose debit a/c name is linked to the accounts table, and the credit a/c name also has to be linked to the same field of the same table ( i.e the accounts table ). Now when I tried to create a ledger (form) which shows all the corresponding transactions for an account, i couldn't proceed further and got stuck, because I don't know what to do here. Also I would like to 'store' all the ledger transations in a separate table, and not just keep it as a display of the collection of queries created from journal entries (as I have attempted up till now).
( If you guys need to take a look at my access db, I could send it over to you. Its barely 550 something kb. I could zip it further. Is there any way we can attach files to threads on this forum ? )
Can someone please help me out here. ( Instructions using the GUI tools would be appreciated )
Also if I get the tables, fields and relations in Access right, I could replicate the same in SQL Server.
Réponses
Please, try the following:
Double Entry Accounting in a Relational Database. Michael Wigley
http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html
Modeling Business Rules: Data Driven Business Rules. David C. Hay
Also, try www.windowsitpro.com. Search "accounting metamodel"
For a complete list of Entities use a basic accounting book, like (not kidding): "The Complete Idiots Guide To Accounting"
Toutes les réponses
You seem to me to be asking for questions relating to your business rules, and not to application development. You should design the tables so that they are normalised, and so that they reflect your business rules. You will probably find that VB.NET is more flexibile in UI design than Access, but again, you would go about designing the forms by making sure that they reflect the data you want to collect and are reasonably self documenting.
You should not create a table to store data which you can derive from existing tables, that would be denormalising your database, creating duplicate data, and potential problems.
- You're right, I shouldn't be denormalising the database by storing data derived from existing tables. But I'm not asking questions related to business rules. I am asking how should I design the database - what tables, fields and relations should I use ? And how should I design the UI for the journal and ledger ? I ultimately plan to use VB Express for designing the UI. I just asked for help using Access because I'm more familiar with it than with VB (which I'm still learning ) , and I could use it for the time being to understand my queries.
How to create a table is a programming question. What your tables look like is definitely a business rule question, one that can only be answered by people who know what data you're trying to store and how you intend to use that data.
How the UI should look is really not something that anyone can answer in words anyhow, and is again dependent entirely on what data you're storing and what you want to do with it. Unless you're asking which to use out of VB and Access, or how to create a UI in Access ( which is such a broad question that I'd start with some online tutorials and then ask specific questions from there ) ?
I'd love to help, but in the first instance, I really have no idea what to tell you. What's a double entry book keeping system ? What data are you storing ? What actions do you want to perform ? What do you want to be told about your UI ? Can you not envisage how it should look based on what you want it to do ? Is Access the best choice for a database here, anyway ? How much data are you likely to store ?
Oh! I see your situation here cgraus. It seems that you are a science grad and I have a comerce background. The double entry book keeping system was invented in France in the 18th century and is the standard method worldwide which is used to store financial information of any entity, business or non business, using 'accounts' in which each financial transaction has two effects - debit and credit. Hence the term 'double entry'. So, as a simple example of the double entry mechanism - if you purchase something, purchase account would be debited, because it is coming into the entity, and cash account would be credited, as cash is going out of the entity.
So, if anyone is familiar with the double entry system, they would know what I am talking about in my first post. Besides, if you guys, as programmers, develop applicaitons for large organisations, you should be familiar with the accounting systems, as they are one of the primary requirements of any organisation.
- Yeah. . . we use peachtree. @ US$1200 it would certainly take our company many more than 12 manhours to even begin writing anything close to robust.
Rishi Khetan wrote: Oh! I see your situation here cgraus. It seems that you are a science grad and I have a comerce background. The double entry book keeping system was invented in France in the 18th century and is the standard method worldwide which is used to store financial information of any entity, business or non business, using 'accounts' in which each financial transaction has two effects - debit and credit. Hence the term 'double entry'. So, as a simple example of the double entry mechanism - if you purchase something, purchase account would be debited, because it is coming into the entity, and cash account would be credited, as cash is going out of the entity.
So, if anyone is familiar with the double entry system, they would know what I am talking about in my first post. Besides, if you guys, as programmers, develop applicaitons for large organisations, you should be familiar with the accounting systems, as they are one of the primary requirements of any organisation.
*grin* actually, I'm not a graduate at all, I'm self taught. But yes, you get my point, you're asking about things that don't really relate to programming, but to the system you're trying to create.
It's a bit of a common mistake to assume that all programmers work on accounting systems. In fact, I work on websites, image processing programs, databases, code libraries, but nothing accounting wise, not ever.
The core thing I guess is that you need to use transactions to make sure that your 'double entry' is a single operation that succeeds or fails. Beyond that, if you need help in designing the tables, you'd need to say what you're trying to store.
Yeah, the cost effectiveness of buying an existing tool/class library is often lost on programmers :-)
*grin* actually, I'm not a graduate at all, I'm self taught.
Great, that actually happens to be my philosophy as well
. And even I'm not a graduate, I'm still an undergrad. I think I should go into a bit more detail here, for those who don't know about accounting systems. As I mentioned, there are two effects for every financial transaction - debit and credit. Every transaction is related to an account. An account stores all the financial info of a particular item. For example you have the cash a/c which tells you about all the cash transactions that you have had, bank a/c which gives you details of all your transactions using your actual bank account ( the two are different ), accounts for different assets that you purchase, that keep track of how much of that asset was purchased, how much had to be sold etc. Then you have the various accounts for the heads of incomes and expenses that you have. These give details of which incomes and expenses you incurred at what dates and what amounts. Now whenever a transaction takes place, it has two effects - one account is debited with a certain amount and another account is credited with the equal amount because there are always atleast two accounts related to every transaction. When you buy something, as per my earlier example, you have an asset coming in your hands, and cash ( or a cheque which means the bank a/c ) going out. So the entry would be to debit the asset account with the purchase amount and to credit the cash a/c with the same amount. Now for storing any financial transaction, we have a book, which stores the base transaction, which is called the journal. So you enter the debited and credited accounts along with the amount and the date into this journal. Now, to make a meaningful picture of all the transactions you have entered in the journal, you need to 'post' them into the accounts' respective ledgers. So the ledger of the cash a/c would list on the left hand side ( debit side ) side, all the sales related transactions, and all income related transactions, because in these transactions, in the journal, the cash account is debited. In the right hand side of the cash a/c ( credit side ), all transactions relating to purchase, expenses etc are stored, as the cash a/c is credited here ( as seen in the above example ). Then you need to total the amounts on both the debit and the credit side periodically, to get the cash balance you have If the total of the debit side of the cash account is more than total of the credit side (which should be the case since you cannot have a negative cash balance), then total of debit side minus total of credit side gives you the cash balance. Now, without computers and RDBMSes, people used to actually write down the journal entries first and manually create the accounts in ledgers in large books and post the entries there and then total all the accounts. This would also lead to duplication of effort, you need to write the same thing twice while posting. But with a computerised system, you only need to enter the journal entry and the system automatically posts the transactions to the ledger and totalls all the accounts in real time to tell you the balances you have on hand for each account. Now there are quite a few good accounting software out in the market, and I am even aware of a few designed using access as well, but I am looking to create a larger database system of which the accounting system is only a part and which stores info about a lot of other aspects of a business, so I wouldn't be able to use these other software. I am just stuck on the accounting part of the database.
It seems to me that you have a good understanding of the business rules here, so you seem the best person to design the tables :-)
If you're building a larger system, is Access the best choice for a database ?
- Really? Sounds like your team needs a Systems Engineer with some integration experience.
Now there are quite a few good accounting software out in the market, and I am even aware of a few designed using access as well, but I am looking to create a larger database system of which the accounting system is only a part and which stores info about a lot of other aspects of a business, so I wouldn't be able to use these other software. It seems to me that you have a good understanding of the business rules here, so you seem the best person to design the tables :-)
Actually, as I mentioned Christian, I am still in college, undergoing studies in the commerce field. And I do not have much knowledge of programming, I just know how to use Access and a bit of HTML. I am trying to learn SQL Server Express and VB Express. And, I most certainly do not intend to use Access for creating the entire system, I am going to use SQL Server and VB Express. Its just that creating tables and relationships in Access is faster ( I have a slow system, need to buy a faster one in a couple of weeks time ) and I will understand it better using Access' example, and then I will recreate the whole thing using the GUI tools in SQL Server. Actually I did try to take assistance from the 'Accounts' template in Access, but couldn't make much headway using that either. I couldn't understand many of the fields they put in their tables. I think I will check out that template once more, to see if I can find anything of substance there. Meanwhile, if someone knows the solution to my problem, could you please help me out ?
No team here Blair, I'm the sole team member here, a one man army. Actually I'm just an amateur hobbyist, and I'm trying to build a complete ERP solution for my Dad's business, I've been trying to persuade him to get it computerised for a long time. Don't worry, its not a large project, the business is a proprietory concern with no employees ( not counting peons ) and is limited to a single computer, but has multiple operations and data requirements. No internet connectivity or setting up a web server for accesing the data remotely here, its just for internal access.
So is your question really how to create tables and relationships using SQL in SQL Server ? That would be a programming question. I doubt very much that anyone here is going to design your database for you, and it's still quite frankly impossible to do so based on the information you have provided.
No, my question is not how to create tables and relationships using SQL in SQL Server. My question is ( to anybody who knows both the accounting methods and principles, and SQL and RDBMS theories ) what tables do I create, what fields do I create in them and which relationships should I create, so that I can get the basic journal and ledger functionality of an accounting system.
I think the point is, it is not as simple as you think it is. We could fill up an entire forum just for hashing out this business solution.
What I would suggest is searching sourceforge.net for some freeware to get an idea on how others have approached this particular situation. You might even find a solution you can employ in its entirety or customize to your needs.
We can offer general help, such as how to design/create/use/manipulate databases/tables/relationships/triggers in general, But particular solutions, to seriously complex issues such as a functional accounting system, I believe are beyond the scope of the developer forums.
Exactly what I was about to say :-)
Hi
Creating a double entry book keeeping system is not that hard of a task if you have the accounting and programming experience. I'm what I would call a begoining programmer with a good bit of experience in accounting but no degree.
I have done three specialized systems for people who couldn't work with quick Books or Simply accounting because they did'nt understand accounting at all.
Set up your accounts (one file) in number categories . with names . A group table may also be necessary if you want to do some crystal reports and have it do summaryies in the report.
100-199 Assets
200-299 Liabilities
300-399 Equity
400-499 Sales
500-599 Purchasing
etc
Then do your forms for various entries
Purchases
Sales
Check issue
etc
With the programming taking care of putting the entries in the right tables.etc
It is rreally too much for this forum
Use sQL if is is to be a large system. MsAccess has some record limits on databases.
I hope this is of some help
- There is a basic reference for the development of a double entry system begining with a datamodel for the database. Check www.tdan.com.
Thanks for the help joeCZ. But I'm not quite sure I understood what you have mentioned. Besides, although I have accounting knowlwdge, as I mentioned above, I do not know any programming, I'm still trying to learn it. I'm looking for info on what tables, fields I need to create in order to get a proper journal entry. I can create an 'Accounts' table which has a field which stores the names of all accounts, along with another field which is FK linked to the account types table. I tried to create a 'Journal' table to enter journal entires, which had the fields - id, date, debit account, credit account, debit amoun, and credit amount. I tried to link both the debit and credit account fields to the account name field in the accounts table. But I couldn't get a meaningful ledger after using this design.( I was using Access here, I'm using access for understanding, I'll switch to SQL Server later, after I've understood the design )
Pier Angelo Pirro, I tried searching that site you mentioned, but I couldn't find anything about double entry systems. The article could be under another name altogether. If you have the direct link to it, could you please provide me with it ?
As I understand it you want to use MS Access. Have you used MSAccess before and if so have you used the macro options or the module options.You need to know how to use the macro system if you want to do the most basic thing in MSAccess. You can't do it by just setting up tables, queries etc.
In addition to a table of accounts you need a transacion file to store the transactions generated by the varuious functions, deposits, payments journals etc. Then you also need forms created for the user to make the various entries. And from those forms you use the macro's to generate the two sides of a transaction to the transaction file.
If this isn't helpfu; them you'll have too learn a lot more about mSAccess if you want to create a double entry booking system. I'm not even sure you can do it all with macros only. The last time I did an accouting system using MSAccess I had to do some programming under the module option.
- Oh! So that's how you do it, using Macros and Modules. I guess I would have to directly create my system in SQL Server now, and skip the Access step. I was never good with that Macros stuff anyway. I think VB would be easier. ;-) Anyway, thanks for your help JoeCZ. BTW, was I going in the right direction, with my definitions of tables and relationships, because that would be common for SQL Server as well, wouldn't it ?
Please, try the following:
Double Entry Accounting in a Relational Database. Michael Wigley
http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html
Modeling Business Rules: Data Driven Business Rules. David C. Hay
Also, try www.windowsitpro.com. Search "accounting metamodel"
For a complete list of Entities use a basic accounting book, like (not kidding): "The Complete Idiots Guide To Accounting"
Please, take this advise. The correct path to follow is developing your datamodel and try that all your business rules and constrains are represented by data models. A programmer always try to solve a problem with "code on the run", try the opposite; design a efficient datamodel.
You can use an Access Project as you Front End and SQl Server as your Back End in case you are confortabel using Access.
Pier Angelo Pirro wrote: A programmer always try to solve a problem with "code on the run", try the opposite; design a efficient datamodel. HERE, HERE!!! Give that man a cigar! Say it again, Pier!!!!
unfortunately . . . designing data models is a subtle art form, isn't it
But thats why we get paid the big bucks! 
I'm an app developer that's finishing my undergrad in Accounting Information Systems.
First, I'll say look at the off the shelf solutions available, you'll save a ton of time and the companies that produce them have been doing it for years and know all of the tricks.
Second, if you're designing something like this, start with the basics of a manual system: a Chart of Accounts, a General Ledger and your specific Account Ledgers. You'll have to refine it from there based on your specific needs. For your UI, take a look at the off the shelf demos and look at how they collect and process the info to get ideas for the info that needs to go in and out.
Third, as someone soon to be eligible to be a CPA, I'll say consult a financial professional for reporting requirements. Even if your father's business is a one man show, there is still an accepted method of reporting financial data that will be useful when dealing with banks or anyone else.
Kevin J. Todd
Edit: After posting this, I looked at Mr. Pirro's links above. Those are great resources if you're looking for a model.
Thanks Pier, those links were most helpful ( atleast from the surface glance I had of the pages. I've saved them to disk for offline viewing ). That appears to be the very thing I was looking for.
And Pier and Kevin, I am an accounting student myself, I'm undergoing the Chartered Accountancy course, which is equivalent to your CPA course. So I don't need much of an assistance with accounting, its just that I don't know any programming. I had already created a flowchart of my AIS and I needed to refine it a bit more, as well as add a few additional components. Its just that I did not know how to put it in the RDBMS format.
Anyway, thanks for all your help guys.
You’re welcome.
If you want to learn more about programming with VB, Access, SQL, SQL Express visit this site.
http://www.programmingmsaccess.com/
This site contains the complete listing of Rick Dobson´s bibliography. Rick Dobson is the more prolific and methodic author on this matters.
The basic “Programming Microsoft Office Access 2003” is going to be very helpful. Also “Professional SQL Server Development with Access 2000” is a complete introduction to Access Projects.
"Oh No!!! Frodo Failed!!! Bush Has The Ring!!! "
Great One Blair !
to create the transaction form after designing the tables you need to use something called the transaction coding in order for the tables you created to be able to update the information in the transaction to several records in several tables.
for example when you make an invoice form the following tables are affected by the transaction as you know:
Inventory table, Customers Table, sales account, cogs account, accounts receivable.
the transaction must start by locking the tables that will be related to the transaction then
the transaction must update via sql all the tables involves and if anything goes wrong you must code for a rollback procedure to ensure that the tables are not effected if something goes wrong, then you must end transation to allow for other transaction to be created or for other users if you have a multiuser system to create new transactions to program and code such transction procedures for a ms access database you must use VB (visual basic) else you wont be able to do this from within ms access only, to print the transaction after posting it via updating tables related to it you must design a report based on a query that reads the updated records in the related tables and produces the output data of the transaction you created.
each transaction you make is set for a journal such as sales journal or cash journal the complicated part would be to create a transacion procedure via code for the general journal especial because the GJE should support splitting transactions so you have to put several records in one table and only one record in another related table.
all the transactions programming in any relational database is done by programing an event driven procedure governed by the overall condition of posting the whole transaction or none of it, and the third state would be to rollback to the original state of the table.
i hope this is good for you work.
ashraf
