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.
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.
No comments:
Post a Comment