Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Running Sum - Not Working if More than One Transaction on Same Date

    I am using the following to get a running sum by date - no matter when the transaction is entered....

    it works - as long as there are no transactions on the same date - then it goes awry

    I really cannot sort, or calculate by the id field - because a user may forget to enter a payment on a certain date, and enter it later - meaning a greater id but an earlier date

    what can I do if there are 2 or more transactions on the same date?

    in the sample - enter a debit or credit on the same date - and the calculation/running balance is not correct!

    explanation of attached sample db


    in the example - run qryRunningTransactions - here if a debit it is a positive no, and if a credit it is converted to a negative - using this : cAmount: IIf([type]="Debit",[Debit],-1*(Abs([Credit])))


    then qryRunningBalance uses this sql code to calculate the running balance

    SELECT D0.id, D0.transactiondate, D0.cAmount, D0.type, (SELECT SUM(camount)
    FROM qryRunningTransactions as D1
    WHERE D1.transactiondate <= D0.transactiondate) AS balance
    FROM qryRunningTransactions AS D0;

    the parameter limits the query/running balance to one customer

    try using the customer numbers 1000 - then 2000 and then 3000
    Attached Files Attached Files
    Last edited by abinboston; 07-17-08 at 00:02. Reason: error

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Personally, I'd do all running balances in reporting rather than in queries.

    Basically, you'd have to sum the credits/debits performed on each day.

    BTW, why have a Type field? It makes it interesting when you enter in "Credit" into the type field and then enter a zero for credits and, say, 500 for debits. I really don't think you need the Type field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can do this:

    Code:
    SELECT D0.transactiondate, D0.id, D0.cAmount, D0.type, (SELECT SUM(camount) 
      FROM qryRunningTransactions as D1 
      WHERE D1.transactiondate <= D0.transactiondate AND d1.id <= D0.id) AS balance
    FROM qryRunningTransactions AS D0
    ORDER BY D0.transactiondate, D0.id;
    Paul

  4. #4
    Join Date
    Aug 2004
    Posts
    173

    Thanks!!!

    Thanks!

    I tried your query and it works well!

    AB

    Quote Originally Posted by pbaldy
    You can do this:

    Code:
    SELECT D0.transactiondate, D0.id, D0.cAmount, D0.type, (SELECT SUM(camount) 
      FROM qryRunningTransactions as D1 
      WHERE D1.transactiondate <= D0.transactiondate AND d1.id <= D0.id) AS balance
    FROM qryRunningTransactions AS D0
    ORDER BY D0.transactiondate, D0.id;

  5. #5
    Join Date
    Aug 2004
    Posts
    173

    Works - Unless Transactions are on the Same Date

    The query below works - unless a user enters a dated transaction later than the last date

    ex:

    if transactionid = 101 and date is 1/2/08 , then another transaction is entered for 1/3/08 (trans 102) and later on a user enters a transaction with a date of 1/2/08 - the transactionid is now 103

    when you sort transactions by date - and a transaction no is > than the previous, the calculations are off.

    Any help is appreciated, as this is close to working..

    AB






    Quote Originally Posted by pbaldy
    You can do this:

    Code:
    SELECT D0.transactiondate, D0.id, D0.cAmount, D0.type, (SELECT SUM(camount) 
      FROM qryRunningTransactions as D1 
      WHERE D1.transactiondate <= D0.transactiondate AND d1.id <= D0.id) AS balance
    FROM qryRunningTransactions AS D0
    ORDER BY D0.transactiondate, D0.id;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •