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

Local time
Today, 14:41
Joined
Aug 19, 2021
Messages
212
Hi, I am beginner in not very expert in Ms Access but I am learning.
I got a project from the company I work for. The Project is to create an accounting database.
Database should be included:
  1. General Ledger
  2. Chart of Account
  3. Different types of vouchers (including voucher printing functionality)
  4. Trial Balance
  5. Income Statement
  6. Balance Sheet
  7. Account Wise Ledger
  8. Depreciation Schedule etc.
I tried to create an accounting database with the help of YouTube. I succeeded in this to some extent. But when I try to modify this database according to my company's requirements, I have a hard time. And everything goes wrong. Every time I try to change, something gets wrong. Which is why I have to create that database again and again. I have worked hard many times to create a database.

Can anyone guide me step by step for this project to accomplish?
I am attaching the database I have created to this post.

Thank you very much
 

Attachments

  • SampleToShare.accdb
    912 KB · Views: 242

CJ_London

Super Moderator
Staff member
Local time
Today, 09:41
Joined
Feb 19, 2013
Messages
16,553
suspect that is too big a question for a forum. For all the time you are spending trying to develop wouldn't you be better just buying one off the shelf?

But if you are going to down this route, make sure you are fully familiar with accounting principles and understand how the specific requirements of your company impact on those principles before starting. You also need to understand database principles.

I've taken a quick look at your sample db. You have a number of calculated fields, some of which simply will not work and some should be resolved by joining tables or using queries. You have also not specified all your relationships. Although these are not essential it does guide you on what your queries should look like.

Sorry to say the design looks like something an excel developer might come up with. You only have 4 tables at the moment - appreciate you have not finished but I would expect around 20-30 perhaps more.

My best advice to you is only store in the tables what you need to store. Things like balance, format of account number, auto determination of some values should be left to use in queries, forms and reports.

As a couple of examples,
  • your general ledger table has account, accountgroup and accounttype fields, it only needs an account field - it should look up the other two fields when required from the account table - but you don't have one. If you want to see those values, use a query or a combo in a form.
  • Your VoucherNo field is text and presumable needs to link to VoucherNo in your Vouchers table. But it can't because that field is calculated. You should rename VoucherNo in General ledger as VoucherFK and make it a long and then link it to the ID field in your Vouchers table.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:41
Joined
Sep 21, 2011
Messages
14,038
Nothing like jumping in the deep end before learning how to swim. :)

Have a look here, for something to get you started.
This is not an easy task, even for a reasonably experienced developer I would have thought, so for a novice, somewhat adventurous, if not foolhardy?
 

GPGeorge

Grover Park George
Local time
Today, 02:41
Joined
Nov 25, 2004
Messages
1,775
Hi, I am beginner in not very expert in Ms Access but I am learning.
I got a project from the company I work for. The Project is to create an accounting database.
Database should be included:
  1. General Ledger
  2. Chart of Account
  3. Different types of vouchers (including voucher printing functionality)
  4. Trial Balance
  5. Income Statement
  6. Balance Sheet
  7. Account Wise Ledger
  8. Depreciation Schedule etc.
I tried to create an accounting database with the help of YouTube. I succeeded in this to some extent. But when I try to modify this database according to my company's requirements, I have a hard time. And everything goes wrong. Every time I try to change, something gets wrong. Which is why I have to create that database again and again. I have worked hard many times to create a database.

Can anyone guide me step by step for this project to accomplish?
I am attaching the database I have created to this post.

Thank you very much
There are few cases where an off-the-shelf application is appropriate for a wide variety of contexts, but this is probably one of them. Accounting packages, from QuickBooks to several SaaS type applications such as Freshbooks, are generally quite effective and need little customization.

Rather than rebuild that same old wheel, why not invest the time in finding one that works for your organization with a minimum of customization?

I love Access; it's more powerful than many would have you believe, but trying to re-create the same old application is probably not a wise investment of your time and salary. Do the math. Multiple your hourly wages by the number of hours you've spent. Now price out one of these packages. I'll bet your organization has already spent more than the cost of a license and you've still not produced a working product.

If your accounting is so unique and so specific to your organization, then, yes, by all means building your own application is worth it. Otherwise, probably not.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:41
Joined
Feb 19, 2002
Messages
42,970
I'm going to jump on the buy don't build bandwagon. Accounting applications are very complicated and you can put your company out of business if you make a serious mistake. The packages that you can buy for a few hundred dollars represent MILLIONS of dollars in R&D and include functionality that at your skill level you could never hope to replicate. Plus applications like QuickBooks are recertified by accountants to be accurate for every new release. There are even several versions each targeted to a certain type of business.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:41
Joined
Feb 28, 2001
Messages
26,996
For a one-man project that will probably be used to run a whole company, this is a case where you are setting yourself up for massive failure no matter how good you are. This is a massive project. Working full-time on a USA-style work week of 8 hours per day, 5 days per week, this is the kind of thing that will require several months to get an integrated package that is STARTING to work, but will probably take a year or more to "get it right." Working overtime to try to shorten the calendar lead-time will shorten your life.

My personal question to you is "how much of your hair are you willing to rip out in frustration before you decide to find a new job that won't give you ulcers, stress-related heart disease, and alienation from your family?" I know, that sounded a bit extreme. But from my perspective, it was only a TINY bit extreme. Early in my career, I let myself get overwhelmed by a project and ended up not having much of a life for a while. Trust me, you don't want to go there. I've been, and I got out as soon as I realized what I had done - but it was a very painful lesson.

A bigger problem is that you added this statement:

But when I try to modify this database according to my company's requirements,

If the bosses are looking for a non-standard general accounting package, it is time for them to do some soul-searching. I am ABSOLUTELY in with the others here that you should VERY STRONGLY consider that you would do better with a commercial off-the-shelf package. If you can find a package that has a way to somehow customize things, fine. The bosses have to consider that this custom package being done by a one-man project will take months to deliver, maybe over a year even if you are very good. How long are they willing to run the company without this package that you can't develop quickly because it is a massive bit of work for one man?

Your question of "Why is this happening" is because you don't have the proper foundation on which to build this magnum opus for your bosses. From your description, I infer that you have skipped THE MOST IMPORTANT PART of any project of this size. You should NEVER EVER write a line of code until you have completed a program design document, sometimes called a problem analysis document. You have to explore the problem and record the answers that you discover for each thing that you face. Also from your description, you appear to be adding in new features a piece at the time and it isn't going well because there are so many moving parts that they don't fit together very well. Again, with the problem analysis document or design document in hand, you would KNOW how pieces fit together and would have far fewer disasters.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:41
Joined
Feb 19, 2013
Messages
16,553
The thing that off the shelf products tend not to do well is things like invoicing and stock management, yes it works but has limited capabilities particularly around bespoke products. But they all should have the ability to import/export data. So you can write your own invoicing system and then import the required resulting values to the accounting system.

An example I did a while ago was for a glazing company. To sell a sheet of glass they needed to enter the glass type, thickness, length and width, plus a cutting charge based on length and width and it also needed to take into account wastage. Became even more complicated when double and triple glazing was taken into account. No way an off the shelf accounting system could deal with that. So I wrote the invoicing package which took data from the accounting package (such as customer details) and when the invoice was printed automatically uploaded the summarised invoice details to the sales ledger. My client was very pleased and then asked me to incorporate a means of adding new customers to the accounting system via the invoicing package.

My point is, if this is an option for you, ensure you get an off the shelf accounting system that will allow you to import/export the necessary data for your other apps.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:41
Joined
Feb 19, 2002
Messages
42,970
Good point Chris. I have also created specific addons, mostly order entry to work with Quickbooks. There is even an ODBC driver for QB so you can interact with the tables directly although people say it is slow. I've also built several applications to replace sections of SAP which is a huge application that runs on mainframes.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:41
Joined
Feb 19, 2013
Messages
16,553
yup - dealt with SAP on a number of occasions - just wish they would stick to what they know (accounting) and leave the other stuff alone, most of which they do (or did) quite badly. Had a client that had two non accounting SAP applications, and they couldn't talk to each other or other applications. I was commissioned to write an app that drew data down from one, passed it through several other apps then uploaded to the other. Usual issue - fighting over who had control of the PK, different datatypes, etc
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:41
Joined
Feb 19, 2002
Messages
42,970
I think SAP stands for sucky application program. It tried to do everything but ended up doing nothing well. I can't complain. I made a lot of money building surrounding applications in both COBOL and with Access.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:41
Joined
Feb 19, 2013
Messages
16,553
but as they say 'no-one ever got fired for recommending SAP'
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:41
Joined
Feb 19, 2002
Messages
42,970
And they never got fired for recommending IBM either even when IBM PC's were going for three times the price of Gateway's. I was cycling through the desktop group as this fight was going on. The price was worse for accessories. A card with two printer ports was going for $250 from IBM but $25 from Gateway. Given our limited budget everyone was screaming at my boss including all his team.
 

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 15:11
Joined
Mar 22, 2009
Messages
771
Hi, I am beginner in not very expert in Ms Access but I am learning.
I got a project from the company I work for. The Project is to create an accounting database.
Database should be included:
  1. General Ledger
  2. Chart of Account
  3. Different types of vouchers (including voucher printing functionality)
  4. Trial Balance
  5. Income Statement
  6. Balance Sheet
  7. Account Wise Ledger
  8. Depreciation Schedule etc.
I tried to create an accounting database with the help of YouTube. I succeeded in this to some extent. But when I try to modify this database according to my company's requirements, I have a hard time. And everything goes wrong. Every time I try to change, something gets wrong. Which is why I have to create that database again and again. I have worked hard many times to create a database.

Can anyone guide me step by step for this project to accomplish?
I am attaching the database I have created to this post.

Thank you very much
I am ready but maybe charged...
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:41
Joined
Jan 20, 2009
Messages
12,849
but as they say 'no-one ever got fired for recommending SAP'
And they never got fired for recommending IBM
Here is a disaster they were both involved in:

IBM's original $6 million contract ended up costing the Queensland taxpayers $1.2 billion.
 

Cotswold

Active member
Local time
Today, 09:41
Joined
Dec 31, 2020
Messages
520
Back in the days before the GUI, when coding was the only way for everything. It was generally accepted that ten man years were about right for a payroll package. What you are attempting is pretty far from a 'simple' payroll package.

The advice from TDM and PH is sound, take it. Rest assured whatever your company wants to achieve it has been done before by many software companies, in every country. Go back to the managers (if they can be called managers) and tell them to spend more time understanding exactly what is out there, and studying how many systems there are that will do exactly what they want to do. The time they spend doing that will be tiny when compared to the time needed to re-create the wheel. Apart from the fact that they simply cannot afford to create the software.

Plus, it is likely that the accounts system you already use will do what is required. It may just require someone to spend a little time to understand and learn how to use it. (Microsoft used to say that 95% of the requests for new features on Word and Excel already existed in the software.)

What they are asking is close to giving you a box of spanners and asking you to maintain their fleet of 60 vehicles under a lean-to at the back of the offices.

The lack of understanding of computing and software by your company in 2022 is astounding. Just guessing but do they waste their days fiddling about with spreadsheets and graphs? If they try to get you to buy shares don't!
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:41
Joined
Sep 12, 2006
Messages
15,613
I actually think a general ledger is a pretty good learning tool - probably single user, although in principle it would be multi-user.
Ditto, for a decent plant register and depreciation calculator.

Treat these as non integrated exercises, and you are looking at no more that a week for each to provide a full featured example of each, with no support costs, and easily improved when necessary. Having said that, these examples pre-suppose a certain level of familiarity with both Access and Accountancy.

A fully integrated accounting system with all the other modules you might need (accounts receivable and payable etc) is a lot more complicated, and a lot of work for an academic exercise, but a standalone general ledger or plant register is useful, and really not too hard to do.

Having said that, It's rare that you would see an accounting system that wouldn't include a general ledger, so there's hardly ever a need to build a free-standing general ledger. (I did want one for my own use. I didn't need any other modules, but I did want an easy way to prepare my own business accounts, so I built a GL in about 3 days, and I do my quarterly accounts in a couple of hours or so, and have used it for many years.)

A plant register is different. Generally that wouldn't automatically be part of an accounting system, and would be a very useful tool to have.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:41
Joined
Sep 12, 2006
Messages
15,613
but as they say 'no-one ever got fired for recommending SAP'

I don't know about that. It's a pretty big and costly tool for someone who could easily get by with Access, (or if necessary SQL Server or MySql). Maybe it's not. Maybe it's inexpensive and easy to use.
 
Local time
Today, 14:41
Joined
Aug 19, 2021
Messages
212
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. And people here are not very familiar with computerized accounting. I am currently a beginner in the field of accounts. And I love to work Microsoft Access. 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. Our company intends to purchase a complete accounting software in the future.
 

Users who are viewing this thread

Top Bottom