Results 1 to 7 of 7

Double entry accounting - before I go further down the rabbit hole

  1. #1
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23

    Double entry accounting - before I go further down the rabbit hole

    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
    Click image for larger version. 

Name:	AccountingRelationships.jpg 
Views:	14 
Size:	42.6 KB 
ID:	39706

  2. #2
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    156
    Hi

    If you can upload a zipped copy of the Db it would help.

  3. #3
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    Here it is. Had to strip out the images to fit the 2mb limit.

    TransBasedNpImages.zip

  4. #4
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    Forgot to mention - dclick invoice numbers to show the bill detail. I've yet to figure out how to use the whole row other than adding the event to every column separately.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,547
    personally, (and I am an accountant) you need more than that because you will have many different types of transactions which are not 1-1 on value such as journals, invoice part paid/over paid, credit against invoice, etc. If you have customers and suppliers you need sales and purchase ledgers as well although at a pinch you might be able get away without.

    So as a minimum your transactions table needs (with 3 examples)

    type of transaction (e.g. sales invoice..........................purchase invoice.................................BACS)
    account to debit (e.g. customer account.....................nominal ledger costs account.................nominal ledger bank account)
    account to credit (e.g. nominal ledger sales account......supplier account.................................customer account)
    transaction reference (e.g. sales invoice number...........supplier invoice number........................paying in bank reference)
    transaction description (e.g. 'product sale'....................'office supplies'...................................'whate ver')
    amount (e.g. .....................300.00....................... .....100.00....................................... .......300.00)
    date of transaction (e.g. .......1/1/2019.........................1/1/2019..........................................1/2/2019)

    As you can see the account to debit might be a customer, a supplier or a nominal account code, same for the account to be credited

    you could combine the customer and supplier tables into 1, but that still leaves account codes. By having consistency in the type of transaction data, you can use this to determine where to go for the other details, like customer name. i.e.

    Code:
    select case transaction type
        case sales invoice
               get data from customer table for debit
               sales account is probably predefined
        case BACS
               bank account probably predefined
               get data from customer table for credit
        case purchase invoice
               get data from nominal ledger data for costs account
               get data from suppliers table for credit
    
    etc

  6. #6
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    Thanks for the info but I doubt we'll get that complicated. I probably didn't get the idea across but the scope of our accounting is really just telling the district what we spent, why, where, and who. The district handles the real accounting. Our commission just wants to know how much we've spent and what is left over.

    Accounting by us to date has been taking a bank statement and explaining each transaction. At first it was hand written then done in a spreadsheet. Each quarter they had to spend lots of time matching invoices to statments etc.

    What I want to write is very limited - simply duplicating the above in a more organized fashion. That way at the end of the quarter they can print the report and match the invoices and be done with it. Reports to the commission will include assets and liabilities so they don't just go by a bank balance that may have hoarded cheques waiting to decimate it.

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,547
    ok, you asked for suggestions.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Accounting Project
    By Mario Robleto in forum Access
    Replies: 5
    Last Post: 12-30-2016, 05:27 PM
  2. Double Data Entry Forms
    By huongdl1987 in forum Programming
    Replies: 3
    Last Post: 09-14-2013, 01:57 AM
  3. Double Entry System
    By mkc80 in forum Access
    Replies: 3
    Last Post: 04-27-2012, 03:53 PM
  4. Building double data entry database
    By Hyunjee in forum Programming
    Replies: 5
    Last Post: 12-13-2011, 10:40 AM
  5. Accounting Template
    By mastromb in forum Access
    Replies: 4
    Last Post: 02-24-2010, 10:36 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums