Results 1 to 12 of 12
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Checkbook ledger

    Excel Genius - Access Idiot



    Recently finished a 14 hour training on Access, so now I know enough to really be annoying. (thanks for your help)

    I had been keeping a check ledger in excel and am now moving it to access and my basic question is should I have one "transactions" table

    or two separate tables one for "Deposits" and another for "Payments/withdrawals".

    Big picture information

    this is for a non profit and I have 15 chapters that will make regular dues payments into this account. sometimes they are fined and thus will make different payments for fines.
    also sometimes a chapter will have problems and this account will make loans to those chapters and then they will pay that back over a period of time.

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    I would always go with the Transactions

  3. #3
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Follow up question

    should all transaction amounts be listed in the same field or should I have separate fields for deposits and withdrawals

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You should have a field in the table for TransactionTypeID and then on your Data Input Form you would have a Combobox that gets the values from a table that lists all the Transaction Types.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    should all transaction amounts be listed in the same field or should I have separate fields for deposits and withdrawals
    it's up to you and depends on how your app is going to work. I tend to favour one field using positive and negative, but you still need a column for transaction type because there are more types than just deposits and withdrawals. for example opening balance, reversals, cash withdrawals, direct debits, supplier payments, interest, etc.

  6. #6
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    should all transaction amounts be listed in the same field or should I have separate fields for deposits and withdrawals
    Thinking ahead to building a report that perhaps shows deposits and withdrawals in separate columns, I would suggest separate fields in the underlying table.

  7. #7
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Attached is a template of a DB I use as my checkbook. You may want to alter to meet your needs, but it will give you an idea of what you may want to do.
    Attached Files Attached Files

  8. #8
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Alright, as you can imagine my first try at creating a db was less than perfect. So I am going to start over.

    But for the transactions tbl.

    I am leaning towards more fields rather than less, so yes a separate field for Deposits and withdraws

    BUT that then leads me to ask shouldn't I also have a separate field for Payee and Payer?

    especially because in this circumstance 90% of all my deposits will be coming from the same 10 chapters, every month each chapter pays dues, and i will want to make sure each chapter name is entered correctly for each deposit.

    NOW that leads to the question.

    Should I have separate tables for deposits and withdraws? and then transactions would be a Query rather than a Table????


    (and Yes I already know the real answer is to just download/buy quicken or other accounting software)

    anyway thanks for you help and thoughts

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    BUT that then leads me to ask shouldn't I also have a separate field for Payee and Payer?
    since funds go to and from the same chapters - I would suggest don't separate - but perhaps in the 'payer/payee' table set a field to define what 'type' - payer/payee/both

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I find your approach interesting in that it follows what I said over here. Check out posts 5 and 6 if you're interested in the snotty answer I got to my question.
    https://www.access-programmers.co.uk.../#post-1684360
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    I'd go with:
    tblTransactions: TransactID, TransactDate, TransactType, TransactAmount, ...;
    tblTransactionTypes: TransactType, TypeText, TypeDefSign, [TypeSignLock] .

    TypeDefSign has values 1 or -1, in calculations tblTransactions.TransactAmount*tblTransactionTypes .TypeDefSign is used.

    Optionally you can have field tblTransactionTypes.TypeSignLock with values 0 (negative values for tblTransactions.TransactAmount are not allowed) or 1 (negative values for tblTransactions.TransactAmount are allowed). Do you need this field or not depends on transaction types you have defined (e.g. do you have separate types for positive and negative inventory corrections, or a single inventory corrections type), is there a need to override default type signs for some set of types only, and on your preferences.

  12. #12
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    My take on this
    Attached Thumbnails Attached Thumbnails Capture1.JPG  

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

Similar Threads

  1. Ledger in Northwind Sample
    By aneela1 in forum Queries
    Replies: 25
    Last Post: 12-31-2015, 04:11 PM
  2. general ledger
    By gogita_79 in forum Access
    Replies: 3
    Last Post: 12-02-2015, 12:52 AM
  3. Creating a party ledger/Account?
    By darshit_goswami in forum Access
    Replies: 8
    Last Post: 02-03-2015, 03:31 PM
  4. General Ledger Database Needed
    By EddieN1 in forum Sample Databases
    Replies: 3
    Last Post: 10-09-2014, 07:46 PM
  5. CheckBook
    By Acc4me in forum Access
    Replies: 2
    Last Post: 11-16-2009, 08:06 PM

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