Solved Hi, Can anyone guide me how to make an accounting database? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 15:02
Joined
Sep 21, 2011
Messages
14,054
Bit like learning to drive an F1 car then. Then employing an F1 driver for the actual races? :)
 
Local time
Today, 20:02
Joined
Aug 19, 2021
Messages
212
Bit like learning to drive an F1 car then. Then employing an F1 driver for the actual races? :)
Sir, I am creating a very simple database. Which will be checked first and then used. And I got this task from my manager.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:02
Joined
Feb 19, 2013
Messages
16,555
Accounting is about double entry book keeping - for every group of entries there is an equal and opposite group of entries. It can be one to one, but not always. At the moment your general ledger account resembles what is called a 'cash book'. You are only showing one side of the transaction, the other is assumed - to be a cash 'box' or a bank account

Consider the situation where you have made a credit sale - you want to credit the sales account, but the opposite entry can't be the bank account because you haven't received the money yet. So you need a sales ledger account. Then when the money is received you credit the sales ledger account and debit bank.

And sticking with that example, you no doubt have a sales tax of some sort. so you also need to credit the sales tax account - and debit that to the salesledger account as well

so for a simple one line sale you would have

AccountDrCr
Sales100
Sales Tax15
Sales Ledger115

and perhaps you need further entries for tracking things like discounts, cost of sales, shipping, etc

and against all of these you will need to know as a minimum the customer account, invoice number and invoice date so when the payment is made you can match the payment to the correct invoice.

Cash books are relatively easy to create and a good task for Access. If that is all your manager requires then go for it. An accounting app is a different task all together.
 
Local time
Today, 20:02
Joined
Aug 19, 2021
Messages
212
Accounting is about double entry book keeping - for every group of entries there is an equal and opposite group of entries. It can be one to one, but not always. At the moment your general ledger account resembles what is called a 'cash book'. You are only showing one side of the transaction, the other is assumed - to be a cash 'box' or a bank account

Consider the situation where you have made a credit sale - you want to credit the sales account, but the opposite entry can't be the bank account because you haven't received the money yet. So you need a sales ledger account. Then when the money is received you credit the sales ledger account and debit bank.

And sticking with that example, you no doubt have a sales tax of some sort. so you also need to credit the sales tax account - and debit that to the salesledger account as well

so for a simple one line sale you would have

AccountDrCr
Sales100
Sales Tax15
Sales Ledger115

and perhaps you need further entries for tracking things like discounts, cost of sales, shipping, etc

and against all of these you will need to know as a minimum the customer account, invoice number and invoice date so when the payment is made you can match the payment to the correct invoice.

Cash books are relatively easy to create and a good task for Access. If that is all your manager requires then go for it. An accounting app is a different task all together.
Yes CJ exactly my manager needs simple database like this.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:02
Joined
Feb 28, 2001
Messages
27,003
Yes CJ exactly my manager needs simple database like this.

My boss wanted me to create a small database so that I could also learn the basics of accounts and because of my interest in access databases I could learn accounting with more interest. And when a small database is created, people using it will become familiar with computerized accounting.

You are not seeing the big picture here. Do you know the difference between London's "Big Ben" clock and a cheap Timex wrist watch? The size of the gears - but not the complexity. There is no difference in complexity for clocks that tell time on the 12-hour or 24-hour clock - both have gears that do 60:1 reduction for minutes and for seconds. But some are huge because they drive big "hands" while some are tiny because they are small enough to wear on your wrist.

Similarly, a "small" database that acts as a "general ledger" package is just as complex as one that drives a 50,000 employee factory. The mistake is confusing physical size of the company and procedural complexity of the corresponding package requirements. You use the same process for ONE transaction as you do for 10,000 transactions.

In the end analysis, it is your project, your problem. I can only cheer you on from the sidelines and offer advice. My first level of advice is to take the time to develop a procedural specification before you do any coding. My second level of advice is to clarify to the managers that your product will not be fully functional for a long time, and the size of the company has NOTHING to do with the complexity of the project.
 
Last edited:

Cotswold

Active member
Local time
Today, 15:02
Joined
Dec 31, 2020
Messages
521
Well Khurramshaikh47 after all of the advice I cannot believe you still want to continue.

Maybe get your manager to write the specification? Which he should have done already anyway. How can you create something he needs if he hasn't created a detailed specification? His specification should also include how VAT or sales tax is handled. Any total cannot be one penny/cent incorrect, so you'll need to use integer calcs. What about security?

Maybe get hold of a copy of Microsoft Money (free) and type in the last 3 months accounts. You can all then sit round and see how it works. After that you might, just, might arrive at a spec. But it is still a big ask. I've written software for commerce and industry for over 30 years and I wouldn't attempt doing what you are. I also doubt that the auditors will be at all happy with the intention.

Also, download the MS Access templates for the "Personal Account Ledger" and "Desktop Business Account Ledger"
Don't expect to be looking at a professional system. The screens will be somewhat amateurish but they will have a database design you may understand or adapt. Enter your account information into them and test.

Additionally, rest assured, if your manager changes. So will the specification.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 15:02
Joined
Feb 19, 2013
Messages
16,555
Think the idea is the OP has to go develop the specification for themselves - which means talking to the relevant staff and managers who will have the sort of detail knowledge around how tax is handled (which is usually based on government guidelines), how sales and purchase ledgers are managed, what depreciation schedules look like, business rules (e.g. who is authorised to sign off payments, limits as to the amount, etc)

Once the spec is signed off and normalisation understood work on the tables can begin followed by forms and reports.

I've given a number of indications of problems with the initial example (post #2) and the sort of thing it will need to handle (post #23). Up to the OP to resolve these going forward.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:02
Joined
Feb 19, 2002
Messages
42,985
@Khurramshaikh47 As long as "small" is understood to be single entry and limited features, you can create something that can replace your paper system. But, before you start building forms and reports, you need a rational schema so if you want help, start by posting the schema you have so far.
 
Local time
Today, 20:02
Joined
Aug 19, 2021
Messages
212
You are not seeing the big picture here. Do you know the difference between London's "Big Ben" clock and a cheap Timex wrist watch? The size of the gears - but not the complexity. There is no difference in complexity for clocks that tell time on the 12-hour or 24-hour clock - both have gears that do 60:1 reduction for minutes and for seconds. But some are huge because they drive big "hands" while some are tiny because they are small enough to wear on your wrist.

Similarly, a "small" database that acts as a "general ledger" package is just as complex as one that drives a 50,000 employee factory. The mistake is confusing physical size of the company and procedural complexity of the corresponding package requirements. You use the same process for ONE transaction as you do for 10,000 transactions.

In the end analysis, it is your project, your problem. I can only cheer you on from the sidelines and offer advice. My first level of advice is to take the time to develop a procedural specification before you do any coding. My second level of advice is to clarify to the managers that your product will not be fully functional for a long time, and the size of the company has NOTHING to do with the complexity of the project.
I really like your advice. I agree with you. And my managers know that I am a beginner and that is part of my learning. That is why they are not stopping manual accounting. Rather, they will run the manual accounting side by side until they are satisfied with the database I have created. They know that this database will not last long term. And they don't even want to run it long term. When our accounting staff becomes familiar with computerized accounting, our managers will purchase a complete accounting software in consultation with company owners.
 

SHANEMAC51

Active member
Local time
Today, 18:02
Joined
Jan 28, 2022
Messages
310
I want to tell you that my boss gave me the task of creating this accounting database because our company is currently running manual accounting.
quite a reasonable manager - prepares a specialist in advance who will help master the accounting program in the future - there are too many flaws in them

these programs are made for a certain average company, do not take into account the specific product produced and working conditions

they will give the total amounts, even print them out, but no more
 

GPGeorge

Grover Park George
Local time
Today, 08:02
Joined
Nov 25, 2004
Messages
1,776
I really like your advice. I agree with you. And my managers know that I am a beginner and that is part of my learning. That is why they are not stopping manual accounting. Rather, they will run the manual accounting side by side until they are satisfied with the database I have created. They know that this database will not last long term. And they don't even want to run it long term.
When our accounting staff becomes familiar with computerized accounting, our managers will purchase a complete accounting software in consultation with company owners.
So, they are willing to invest months and months of your salary in building a completely custom accounting system, while paying the regular accounting staff to do the accounting as always. Then train the staff to use your new custom accounting system. THEN, invest again in a purchased accounting system and invest AGAIN in retraining them to use that purchased accounting system. Not afraid of spending money, apparently.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:02
Joined
Feb 19, 2013
Messages
16,555
In low level support of the OP - if the current accounting staff have no feel for how an accounting app works, they may struggle to have an effective involvement in specifying requirements. However still think time would be better spent using an existing accounting app or to use one of the many templates out there - suggested on this or another of the OP's threads.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:02
Joined
Feb 19, 2002
Messages
42,985
Even packaged systems can be difficult to set up especially if there is anything unusual about the current accounting methods so baby steps seems to be a rational approach.
 

Users who are viewing this thread

Top Bottom