LOOKING FOR ALTERNATIVES TO ACCOUNTING PACKAGES 1

[GClinic]

Hi All,
We are designing a program to capture all the activities of our church, and so far, other members of the church think we have been doing a good job. Our Accounting Manual has been incorporated as fairly as we could translate it. Reports are generated quite nicely.

The problem we have is with the area of accounting called TRIAL BALANCE. It has not been too difficult displaying or reporting each account individually, be it Asset Depreciation or Cash-At-Hand, or any of the 85 items in the Chart of Accounts. The problem is that when we display in one report, it takes up to five minutes to open the form or report on the church pc, although it takes half that time on mine. Some tables have up to 2000 records, and are growing daily.

Meanwhile, it has been determined that we cannot use any of the accounting packages in the market because they lack flexibilty to incorporate all other non-financial activities of the church, and would be too expensive anyway.

I had tried a union query that attempted to capture all items in the chart of account, but the ensuing form took ages to open. Now, I have broken the monster query into smaller ones which I base several sub-forms on. Although the queries open instantly, the parent form is still slow, marginally better than a form based on the monster union query.

I need advice on how I can go about solving the problem.

 

[JoeAtWork]

Some tables have up to 2000 records

That's tiny, tables can have hundreds of thousands or even millions of records before it starts affecting the efficiency of queries.

I had tried a union query that attempted to capture all items in the chart of account

I'm curious as to why a UNION query would be needed in the first place, wouldn't all the chart of accounts be in the same table? This statement, plus your talk of a "monster UNION query" makes me suspect the problem here is a very unnormalized database. Is each Chart Of Account in it's own separate table by any chance?

It's very hard to give advice without knowing anything about your database. I suggest you pick an area to focus on and ask specific questions in the forums. Include the SQL statements of the data sources for the forms/reports, plus any relevant VBA code. That "monster query" might be a place to start.

 

[GClinic]

Thanks, Joe.

The chart of account is just one table alwright. But the reports that are needed for a trial balance cannot be reproduced by just grouping records according to the chart only.

For example, there will be no record in the database that deals with depreciation of assets, or funds lost in transit, or maybe goods paid for but not yet delivered; such things have to be deduced or calculated by whatever means, even though the items are clearly listed in the chart of accounts. In other words, many items in the chart are for the auditors only, not for those who enter records routinely.

The reason I embarked on the monster queries was my attempt to reflect the auditors' wish: to see the various accounts of the chart of accounts at a glance. However, what I am getting is very slow indeed, except on the fastest machines, considering that the number of records we have is virtually at infancy.

I appreciate your help. I am getting desperate.

 

[PHV]

Anyway, have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GClinic]

Here is what the chart of accounts look like:

AccountID AccountSubHead Account
1 CASH CASH ON HAND
2 CASH CASH AT BANK
3 CASH CASH IN TRANSIT
4 CASH SPECIAL FUNDS
5 STOCKS STOCKS OF ECK MATERIALS
6 STOCKS STOCKS OF CONSUMABLES
7 STOCKS OTHER STOCKS
8 DEBTORS PREPAYMENTS/DEPOSITS
9 DEBTORS STAFF DEBTORS
10 DEBTORS OTHER DEBTORS
11 FIXED ASSETS LAND
12 FIXED ASSETS BUILDINGS
13 FIXED ASSETS PLANTS & MACHINERY
14 FIXED ASSETS MOTOR VEHICLES
15 FIXED ASSETS FURNITURE & FITTINGS & EQUIPMENT
16 FIXED ASSETS MUSICAL, OFFICE EQUIPMENT & OTHERS
17 FIXED ASSETS CONSTRUCTION WORK-IN-PROGRESS
18 FIXED ASSETS BUILDING - CUMULATIVE DEPRECIATION
19 FIXED ASSETS PLANT AND MACHINERY - CUMULATIVE DEPRECIATION
20 FIXED ASSETS MOTOR VEHICLE - CUMULATIVE DEPRECIATION
21 FIXED ASSETS FURNITURE AND FITTINGS - CUMULATIVE DEPRECIATION
22 FIXED ASSETS MUSICAL EQUIPMENT & OTHERS - CUMULATIVE DEPRECIATION
23 CURRENT LIABILITIES CREDITORS FOR ECK MATERIALS
24 CURRENT LIABILITIES ACCRUALS
25 CURRENT LIABILITIES VAT AND SIMILAR TAXES
26 OTHER LIABILITIES TERM LOANS
27 CAPITAL & RESERVES ACCUMULATED RESERVES - PRIOR YEARS
28 CAPITAL & RESERVES NET SURPLUS/DEFICIT FOR THE YEAR
29 CAPITAL & RESERVES CONTRIBUTIONS FROM (TO) N. S. C.
30 GENERAL DONATIONS GENERAL DONATIONS - TEMPLE OF ECK
31 GENERAL DONATIONS GENERAL DONATIONS - ECK TEMPLE
32 GENERAL DONATIONS GENERAL DONATIONS - ECK INFORMATION CENTER
33 GENERAL DONATIONS GENERAL DONATIONS - OTHERS
34 FUND RAISING FUND RAISING - TEMPLE OF ECK
35 FUND RAISING FUND RAISING - ECK TEMPLE
36 FUND RAISING FUND RAISING - ECK INFORMATION CENTER
37 FUND RAISING FUND RAISING - SEMINAR & VAHANA PROGRAMME
38 FUND RAISING FUND RAISING - OTHERS
39 SEMINAR DONATIONS SEMINAR DONATIONS - ZONAL SEMINAR
40 SEMINAR DONATIONS SEMINAR DONATIONS - REGIONAL SEMINAR
41 SEMINAR DONATIONS SEMINAR DONATIONS - AREA/SUB-REGIONAL SEMINAR
42 ECK MATERIALS SALES ECK MATERIALS SALES
43 SATSANG PRODUCT PROJECT REVENUE SATSANG PRODUCT PROJECT REVENUE
44 NEWSLETTER REVENUE NEWSLETTER REVENUE
45 INTEREST RECEIVED INTEREST RECEIVED
46 OTHER REVENUE OTHER REVENUE
47 DONATIONS IN KIND DONATIONS IN KIND
48 DIRECT COSTS DIRECT COSTS - FUND RAISING COSTS
49 DIRECT COSTS DIRECT COSTS - SEMINAR COSTS
50 DIRECT COSTS DIRECT COSTS - ECK MATERIALS COSTS
51 DIRECT COSTS DIRECT COSTS - SATSANG COSTS
52 DIRECT COSTS DIRECT COSTS - NEWSLETTER COSTS
53 ADMINISTRATIVE COSTS ADMINISTRATIVE COSTS - INTRODUCTORY/VAHANA PRESENTATION EXPENSES
54 ADMINISTRATIVE COSTS ADMINISTRATIVE COSTS - CHELA MEETING EXPENSES
55 ADMINISTRATIVE COSTS ADMINISTRATIVE COSTS - DEPRECIATION
56 ADMINISTRATIVE COSTS ADMINISTRATIVE COSTS - RESA EXPENSES
57 ADMINISTRATIVE COSTS ADMINISTRATIVE COSTS - BOARD COUNCIL EXPENSES
58 GENERAL EXPENSES GENERAL EXPENSES - WAGES & SALARIES
59 GENERAL EXPENSES GENERAL EXPENSES - RENT & RATES
60 GENERAL EXPENSES GENERAL EXPENSES - BANK CHARGES
61 GENERAL EXPENSES GENERAL EXPENSES - TELEPHONE, INTERNET & POSTAGE
62 GENERAL EXPENSES GENERAL EXPENSES - HOTEL, TRANSPORT & TRAVELLING
63 GENERAL EXPENSES GENERAL EXPENSES - PRINTING & STATIONERY
64 GENERAL EXPENSES GENERAL EXPENSES - HOUSEHOLD REPAIRS & MAINTENANCE
65 GENERAL EXPENSES GENERAL EXPENSES - SECURITY EXPENSES
66 GENERAL EXPENSES GENERAL EXPENSES - WELFARE, HOSPITALITY & ENTERTAINMENT
67 GENERAL EXPENSES GENERAL EXPENSES - PLANT & MACHINERY MAINTENANCE
68 GENERAL EXPENSES GENERAL EXPENSES - IMPREST ACCOUNT
69 GENERAL EXPENSES GENERAL EXPENSES - FUEL
70 GENERAL EXPENSES GENERAL EXPENSES - STAFF ALLOWANCES
71 GENERAL EXPENSES GENERAL EXPENSES - SUNDRY EXPENSES
72 GENERAL EXPENSES GENERAL EXPENSES - GENERAL UPKEEP
73 GENERAL EXPENSES GENERAL EXPENSES - STAFF PENSIONS DEDUCTIONS
74 GENERAL EXPENSES GENERAL EXPENSES - EXTERNAL LOANS SERVICING
75 GENERAL EXPENSES GENERAL EXPENSES - PUBLIC RELATIONS
76 DIRECT COSTS DIRECT COSTS - VAHANA/MISSIONARY PROGRAM
79 GENERAL EXPENSES GENERAL EXPENSES - WATER
80 GENERAL EXPENSES GENERAL EXPENSES - NEPA
81 FIXED ASSETS BOOKS - CUMULATIVE DEPRECIATION
82 FIXED ASSETS DEPRECIATION PROVISION
83 DIRECT COSTS DIRECT COSTS - YOUTH PROGRAMS
84 CURRENT LIABILITIES OTHER CREDITORS
86 FIXED ASSETS CAPITAL GAINS
87 FIXED ASSETS DISPOSAL OF ASSET
88 ECK MATERIALS SALES ASSET DISPOSAL
89 FIXED ASSETS BOOK

As you can see, there are many items that are not applicable when one is entering a record. For example, nobody can issue a receipt for item 1, (Cash In Hand), or item 2 (Cash At Bank), or make expenses under a listing of Assets Depreciation.

Meanwhile, accountants want to see a report incorporating everything in the chart of accounts in a trial balance or whatever.

Actually, they make sense. For if all details are known (date of purchase, cost, depreciation rate etc) one can always calculate the total depreciation for a given asset in a given period. But then, such calculations would not have much relationship with the regular 'buying and selling transactions' that take place in an organization, other than date. So I reckoned that the only way to make depreciations appear with regular transactions was to build a union query. This was how the idea of monster queries evolved.

I hope I have given a fair picture of the problem.

 

[JoeAtWork]

I think the advice you would get from most of the professional programmers on this forum is to buy an accounting package.

What most businesses do is to use an off the shelf accounting package that they use for reporting, creating cheques, etc. For the areas of the business that don't fit in with the generic accounting package, they create a separate custom program (this is usually where I come in). The custom program will handle their unique processes, and will summarize the financial data (orders, invoices, etc.) to be transferred to the accounting program. Pretty much every accounting program has some way of importing financial data from another program (the simplest methods are with delimited text files, but many now also offer an API - Application Programming Interface - that will let a 3rd party developer have access to almost every part of the accounting system).

I think that is your best option, since writing an accounting application with the features you require is a daunting task for even a seasoned programmer. There are good accounting packages out there that are only a few hundred bucks (I believe Quickbooks is in that price range).

 

[GClinic]

Thanks again, Joe.

If an accounting package will truly allow integration of a custom program with their own, then that would be just fine. The impression I had was different.

I appreciate your help.