I've been messing around over a month playing with designing an accounting system for a small non-profit I belong to. I messed around with different approaches which appeared good until I got to the point of making reports, doing cancellations and refunds, and trying to figure out how to do a year end then it'd get messy. I'd end up with the same data in two places which really didn't sit well.
Most of the examples and tutorials helped with certain aspects but none seemed truly useful for a live system so I just kept experimenting (which is fine because I find it fun!).
I know people will say that it'd be way easier to simply buy an existing package and that's what I looked at first. The problem with those is that they are so generalized they are difficult to learn and adapt to our situation. The person doing most of the work won't be an accountant and even with a bit of accounting knowledge I find some of them pretty opaque.
We're also a bit weird in that we act as an agent for a regional government so many of the usual accounting tasks don't even apply to us. We pay bills, rent meeting rooms, collect two sales taxes at different rates and one has to be broken out in reports because the region gets reimbursed later. We don't pay taxes (that's done at the regional level) so none of the usual asset depreciation etc even applies to us.
Our work boils down to a quarterly report to the regional district showing activity with GST broken out and arbitrarily spaced reports to the governing committee for their business meetings. Pretty simple really.
Anyway I think I finally hit upon a concept that will work that uses double entry accounting. At first I went cash because I thought double entry would be overly complicated but it's looking like the opposite is true. While I have to keep a normal balance cheat-sheet handy (Can never remember when to debit or credit) the structure and coding is looking incredibly easy!
Anyway, the basic structure is:
- Contact table with vendors and customers in it (with a field showing which they are or both).
- Invoice table that will hold both bills and sales invoices and a field saying which it is. Most of the fields are common but a couple are specific so I just don't show/use those when not needed. There's no POS since they haven't decided what/how to charge - they'll have to work out prices etc manually then enter the final numbers in the database.
- Transactions table that holds all transactions of both types. Again I have some fields that aren't shared between types of transactions.
I've entered a few contacts and bills to work with (imaginary people/companies) and I'm just about to get to making payments on bills. No sales stuff yet since it'll be nearly identical.
About bills: I've broken it down to three steps.
- Receive and record a bill: goes to accounts payable, two tax accounts, and an expense account.
- Review and record payment (almost exclusively by cheque)
- Reconcile when bank statement received which finalizes the payments
For a new bill I used a form with unbound controls and transactions in code since I have to do up to five inserts (4 bills, 5 sales). I also find working with bound controls and new records very unwieldly
I set up a trial balance query to check the results and it actually comes to all zeros (Yay).
I'm just about finished the form that shows existing bills that have a balance owing. At first I was recording the amounts in the invoice table but last night I figured out how to use DLookup()/DSum() to get the values from the transactions instead which solves some of the dilemmas I had with earlier attempts. Now the values are only in one place and will always be up to date with cancellations or refunds.
Here's the relationships. I haven't really utilized them well. I'm still not great at using joins and even worse at using the editor so I resort to manually writing the SQL most of the time. I am practicing though! The Invoices still have the fields for amounts but those will disappear soon once I'm sure using transactions to get the values works out.
I can also upload the database if anyone is interested in checking it out. It's not like it's going to be a commercial product or anything.
So what do people think - does this look promising? Any suggestions on how I should change things?
Oops! Pic didn't work. Trying again