Results 1 to 3 of 3

Using Transfer Transactions

  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    299

    Using Transfer Transactions

    Does anyone know how to create a "transfer" transaction like Quicken does between Accounts? By entering one transaction in Account A and referring to Account B in the Category field, Account A shows the increase and Account B shows the decrease, and vice-versa. Apparently, these are not two different records, but are somehow "linked" so that changing one affects both accounts.



    Thanks, Eddie

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,456
    I don't user Quicken so forgive if this isn't as simple as it sounds. Using a form with a control (textbox likely) where you type in the dollar amount, two other parameters would be required: the account to take the amount from and the account to move it to. These two would likely be combo boxes so you can control the list of possible accounts associated with the user. Upon click of a button, an positive entry goes into account A say $45.00, and a corresponding entry goes into account B ($45.00) or -$45.00 if you're not using accounting notation (wherein negatives are enclosed in brackets). The individual entries can be listed in a report or form, likely ordered by date for the user, formatted in any way that suits you along with textboxes that calculate the sum of negatives and positives for each account. The sum of 100.00+(10.00) is 90.00, so if those were the only two transactions for the account, the balance would show as $90.00 and the individual entries would be available for display. NEVER store calculated values (e.g. balances) for financial transactions such as this.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,570
    The usual way account programs store the data is as a single record - otherwise there is a risk that one half of the transaction could disappear, putting the accounts out of balance, plus other data required, such as description, date etc would otherwise need to be duplicated. So basically at the simple level you need a many to many linking table - something like


    tblTransactions
    TranPK autonumber
    DrAccount text
    CrAccount text
    TranDate date
    TranAmount currency
    TranDescription text

    Dr and Cr and Debit and Credit

    So to know the balance of the account you would have a query along the lines of

    SELECT sum(TranAmount*iif(DrAccount=[Enter Account],1,-1)) as Balance
    FROM tblTransactions
    WHERE DrAccount=[Enter Account] OR CrAccount=[Enter Account]

    If you are trying to build an accounting system, be aware there are significant variations to this for certain types of transactions - journals for example might dr one account and cr two other accounts. Within Sales and purchase ledger, you have the one control account but you also have the individual customer and supplier accounts which need to be tracked within the same record. You may have the application of discounts, sales tax etc which will have their own transaction elements also to be treated within the same record.

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

Similar Threads

  1. Transactions Per Date
    By Imaginativeone in forum Queries
    Replies: 2
    Last Post: 08-05-2014, 12:44 PM
  2. Inventory Transactions
    By mm26 in forum Access
    Replies: 7
    Last Post: 02-16-2014, 07:21 PM
  3. [HELP] Getting average of transactions
    By janthony in forum Queries
    Replies: 10
    Last Post: 10-17-2013, 12:58 PM
  4. Add all transactions under each other
    By jamesborne in forum Queries
    Replies: 5
    Last Post: 12-23-2011, 07:10 AM
  5. Transactions Report
    By limcalvin in forum Reports
    Replies: 3
    Last Post: 08-19-2011, 08:01 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
  •  
Tech Forums: Microsoft Office Forums