Results 1 to 13 of 13
  1. #1
    LeeSteventon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    6

    Advice needed on the right approach to designing and building a Peronal Accounts Ledger database

    Hi Forum!



    After several attempts over the years to try and build a replacement application for the now non-supported Microsoft Money Suite of applications (they worked great and gave me almost everything I needed, but found out that it does not run on Windows 10...), I decided I should bite the bullet and sit down and get this project done, once and for all!

    My Design Goal: to build an Access 2013 database / application that I can use to store transaction information for multiple bank accounts and then use that information at the end of each tax year to crunch the numbers based upon my local tax laws (Netherlands, although I see that it should be possible to load any set of tax laws being able to be loaded into the system and the system crunches based on those, but this is just a concept ). Also, to analyse spend on various categories (food, clothing household utilities, etc.)


    Now, with a single bank account, I think this is relatively simple - 1 table for the actual transactions in that account and other tables for the other information in each transaction (categories, payees, etc.) However, I'm struggling to understand how I would approach this for multiple bank accounts. My thought is that I would still need to have just one "Transactions" table and it holds all transactions for all accounts. But then how do I handle transactions between accounts, for example? That one is giving me headaches!

    I've looked through this and other forums for answers / advice on approaching this, but the answers seem to be varied depending on who you ask (of course ).
    Many people say "Fool! Why bother when you can buy an off the shelf package?" True, but where's the fun in that!? My purpose is to learn by doing and I have a need for an accounts system and I think I will learn a lot by building it (and more than a little help from this forum, I'm sure! )

    Can anyone point me in the right direction or show me a thread I may have missed on this topic?

    Any and all help is of course very much appreciated!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You might want to look into some of these just to see how they are set up. There may be others that offer a trial period -again look at the general design to get some ideas before you do too much independent work.

    http://download.cnet.com/1772-20_4-0...type=downloads

  3. #3
    Join Date
    Sep 2014
    Posts
    6
    Hi Orange and many thanks for the link.

    There are a lot of mixes of all sorts of things there! But again, my goal is to learn Access properly by doing and this project seems like a good way to do that. The solutions listed in that link vary from basic Excel sheets to programs that don't really give me what I am looking for. I couldn't find an Access example of a multiple bank account registration system, which is really what I am trying to build

    But thanks for the reply and tips anyway!

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I would start by reviewing relational database concepts and techniques. Maybe something like ...
    https://www.youtube.com/watch?v=mxYy4OINoSo

    The above link is one of several that are included here.
    https://www.accessforums.net/tutoria...ase-45451.html

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Lee,

    My point was really to see what others (shareware products/commercial products) actually claim to do. Get your requirements identified so you can compare with established solutions. I wasn't suggesting you buy one of these because it meets all your needs. One or two may very well meet you needs, but if you want to build something then use those for ideas and concepts. Their screens, process flow, colors... can be valuable.

    If you want to learn Access, I'm going to suggest you learn database concepts first. Even if it's only a refresher, here is a tutorial that will help you with tables and relationships. Research Normalization, Primary/foreign keys, indexing...naming conventions--before you build something and say "jeez I wish I had known about that...". (Just review posts in various forums and see what the common issues are --- forewarned is fore armed.

    And if you want a video that helps with analysis, concepts and Access watch this one.

    Good luck

  6. #6
    Join Date
    Sep 2014
    Posts
    6
    Hi Orange,

    Yep, completely understood what you were aiming at there No worries.

    I already understand a lot of the basic and more advanced concepts of Access (and other) databases, but I guess then I am just stumbling on the multiple accounts piece (i.e. how to make inter-account transfers work in a single table)

    I think I need to just sit down and think it through a bit more

    Many thanks again for your help!

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by LeeSteventon View Post
    ...
    (i.e. how to make inter-account transfers work in a single table)
    ...
    I don't think you will be able to. You will need multiple tables. I would start with an ERD.

  8. #8
    CPR is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Location
    Nature Coast of Florida USA
    Posts
    6
    If you start with the concept of double-entry bookkeeping you may be able to work it from there. Account A debits- Account B credits, Account A debits - expenses credits, etc. Table of transactions or a debit and a credit table??

  9. #9
    Join Date
    Sep 2014
    Posts
    6
    Aha! CPR, your comment on debit and credit tables has given me an idea! Maybe a form that collects the transaction information and depending upon whether or not the transaction is a debit or credit to any particular account, it is stored in a debit or a credit table. Account balances are then the sum of all transactions in both tables, with credits being added and debits being deducted.

    make sense?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If learning db concepts and design and programming is part of the goal, then have fun. But if you just want something that works for relatively little investment (after all, your time is an investment), why not QuickBooks Desktop - about $200? Or something similar.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Join Date
    Sep 2014
    Posts
    6
    Hi June, and many thanks for the reply!

    Yes, as I said, this for me is all part of the fun of learning and reaching a desired end goal. In any case, no off the shelf package will ever do everything exactly the way I want to do it....

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A single table for transactions could be made to work. Another field in table could identify the record as debit or credit. You would have to know the account type and know which value to select (debit or credit).

    Or maybe a chart of accounts identifies the type of account (asset, liability, expense, income) and the type of account determines if the account has positive balance in debit or credit. Then the transaction records would have positive and negative entries. Again, you would have to understand the account type and whether or not to enter the value as positive or negative number, instead of selecting 'debit' or 'credit' identifier.

    Or maybe go old school and have Debit and Credit columns (fields). Not strict normalization because every record would have value in only one of the fields. Again, still have to understand account types well enough to know which column the value goes into.

    Digital or paper, still need to understand double entry bookkeeping concepts.

    Makes me wonder what QuickBooks table structure is.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Join Date
    Sep 2014
    Posts
    6
    well, have just found a link to the list and structure of tables in QuickBooks - http://doc.qodbc.com/qodbc/usa/

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

Similar Threads

  1. General Ledger Database Needed
    By EddieN1 in forum Sample Databases
    Replies: 3
    Last Post: 10-09-2014, 07:46 PM
  2. Replies: 7
    Last Post: 09-11-2014, 11:48 AM
  3. Advice on designing my 1st database
    By cmb in forum Database Design
    Replies: 2
    Last Post: 09-09-2014, 03:42 PM
  4. Need help and advice designing a call center database.
    By AbandonedRobot in forum Database Design
    Replies: 3
    Last Post: 07-20-2014, 01:49 PM
  5. Replies: 3
    Last Post: 06-20-2013, 01:15 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
  •  
Other Forums: Microsoft Office Forums