none
Help with Running Total in Query in Access 2010

    Question

  • Hello, All. I am new with Access and trying to create a running total in access. I read many solution about running total in query, but still don't get the concept... please explain it! My query is bellow....

    RceivedDate     BalanceForward     Received      Processed       EndingBalance

    6/01/13                  0                       100              50                   50

    6/02/13                50 (*)                  150              100                100

    6/03/12                100 (*)                 100              200                  0

    6/04/12                  0 (*)                  etc......

    (*) = Previous day Ending Balance (And I don't want to type this balanceForward = EndingBalance (Previous day) every time I enter a new date/transaction... How can I write a running total in query so that the balanceforward will be automatically entered from the previous day ending balance. Thanks

    Sunday, June 30, 2013 4:41 AM

Answers

  • The principle is that (a) the opening daily balance is the sum of all net transaction values prior to the date of the current transaction, and (b) the closing daily balance is the sum of all net transaction values prior or equal to the date of the current transaction.  In the case of the first opening balance account must be taken of that fact that there are no prior transactions, so the Nz function must be used to return a zero in place of the Null.

    The most efficient method for doing this is to join two instances of the table, but that results in a non-updatable query, so this is not suitable for a data entry form.  For the query to be updatable the VBA DSum function can be used to compute the balances.  So if we assume that the table is named Transactions:

    SELECT Transactions.*,
    Nz(DSum("Received-Processed","Transactions","ReceivedDate < #" &
    Format([TransactionDate],"yyyy-mm-dd") & "#"),0) AS BalanceForward,
    DSum("Received-Processed","Transactions","ReceivedDate <= #" &
    Format([ReceivedDate],"yyyy-mm-dd") & "#") AS EndingBalance
    FROM Transactions
    ORDER BY ReceivedDate;

    Note:

    1.  BalanceForward and EndingBalance are not columns in the Transactions table, but columns of the query's result table.  This is very important as values which can be time-independently computed from other values should not be stored, as this introduces redundancy and the risk of update anomalies.

    2.  The above will give daily opening and closing balances, not transactional balances if there are multiple transactions in one day.  To compute transactional balances in this situation either the ReceivedDate column must contain distinct full date/time values or another value, usually the table's primary key must be brought into play as the tie-breaker between two or more transactions on one day

    3.  The balances are computed over all rows.  If you want them grouped by customer then this must be added to the criteria of the DSum function calls.

    4.  Where a table has separate credit and debit columns, as with your Received and Processed columns, it is important that each column has a DefaultValue property of zero, and disallows Nulls by setting its required property to True (Yes).

    5.  The date literals in the above query are formatted in the ISO standard for date notation of YYYY-MM-DD to ensure international unambiguity.  In your data base the dates will be entered and viewed in your local data setting of course.

    You'll find examples of how balances can be computed in various contexts read-only and updatable, including balances grouped per customer in Balances.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file computes closing transactional balances only, not opening balances, however.

    Ken Sheridan, Stafford, England

    Sunday, June 30, 2013 1:29 PM
  • If you are doing this for a report, it's MUCH simpler to use the report engine for this. Note the Running Sum property for textboxes in reports. You can set the controlsource to "=1" and see what happens :-)


    -Tom. Microsoft Access MVP

    Sunday, June 30, 2013 1:42 PM

All replies

  • Post the SQL of a select query that has your table and field names. 

    Open the query in design view, click on VIEW - SQL View, highlight all, copy, and paste in a post.


    Build a little, test a little

    Sunday, June 30, 2013 5:10 AM
  • The principle is that (a) the opening daily balance is the sum of all net transaction values prior to the date of the current transaction, and (b) the closing daily balance is the sum of all net transaction values prior or equal to the date of the current transaction.  In the case of the first opening balance account must be taken of that fact that there are no prior transactions, so the Nz function must be used to return a zero in place of the Null.

    The most efficient method for doing this is to join two instances of the table, but that results in a non-updatable query, so this is not suitable for a data entry form.  For the query to be updatable the VBA DSum function can be used to compute the balances.  So if we assume that the table is named Transactions:

    SELECT Transactions.*,
    Nz(DSum("Received-Processed","Transactions","ReceivedDate < #" &
    Format([TransactionDate],"yyyy-mm-dd") & "#"),0) AS BalanceForward,
    DSum("Received-Processed","Transactions","ReceivedDate <= #" &
    Format([ReceivedDate],"yyyy-mm-dd") & "#") AS EndingBalance
    FROM Transactions
    ORDER BY ReceivedDate;

    Note:

    1.  BalanceForward and EndingBalance are not columns in the Transactions table, but columns of the query's result table.  This is very important as values which can be time-independently computed from other values should not be stored, as this introduces redundancy and the risk of update anomalies.

    2.  The above will give daily opening and closing balances, not transactional balances if there are multiple transactions in one day.  To compute transactional balances in this situation either the ReceivedDate column must contain distinct full date/time values or another value, usually the table's primary key must be brought into play as the tie-breaker between two or more transactions on one day

    3.  The balances are computed over all rows.  If you want them grouped by customer then this must be added to the criteria of the DSum function calls.

    4.  Where a table has separate credit and debit columns, as with your Received and Processed columns, it is important that each column has a DefaultValue property of zero, and disallows Nulls by setting its required property to True (Yes).

    5.  The date literals in the above query are formatted in the ISO standard for date notation of YYYY-MM-DD to ensure international unambiguity.  In your data base the dates will be entered and viewed in your local data setting of course.

    You'll find examples of how balances can be computed in various contexts read-only and updatable, including balances grouped per customer in Balances.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file computes closing transactional balances only, not opening balances, however.

    Ken Sheridan, Stafford, England

    Sunday, June 30, 2013 1:29 PM
  • If you are doing this for a report, it's MUCH simpler to use the report engine for this. Note the Running Sum property for textboxes in reports. You can set the controlsource to "=1" and see what happens :-)


    -Tom. Microsoft Access MVP

    Sunday, June 30, 2013 1:42 PM