none
Please help a Running Totals query in access 2010

    Question

  • I need a running total number in the Balance column with the result in the following table.   What is the correct SQL or expression for it?

    CustomerID RefNbr SortKey3 Date  Memo   Amount  Balance
    ABC 720001 1 1/5/2012  T11         960.00        960.00
    ABC 720001 2 1/5/2012  CREDIT MEMO        (730.00)        230.00
    ABC 720001 3 2/6/2012  PAYMENT CK NO. 775          (92.00)        138.00
    ABC 720001 3 2/28/2012  PAYMENT CK NO. 662            (6.00)        132.00
    ABC 720001 3 2/28/2012  PAYMENT CK NO. 139          (30.00)        102.00
    ABC 720001 3 3/13/2012  PAYMENT CK NO. 936          (40.00)          62.00
    ABC 720001 3 3/13/2012  PAYMENT CK NO. 451          (62.00)                 -  
    ABC 720027 1 1/5/2012  T12         150.00        150.00
    ABC 720027 2 1/5/2012  CREDIT MEMO          (80.00)          70.00
    ABC 720027 3 2/6/2012  PAYMENT CK NO. 775          (20.00)          50.00
    XYZ 360339 1 4/30/2012  B38         222.00        222.00
    XYZ 361117 1 4/30/2012  E38           36.00        258.00
    XYZ 361838 1 5/31/2012  B38         171.00        429.00
    XYZ 362610 1 5/31/2012  E38           18.00        447.00




    • Edited by Citon Sunday, July 29, 2012 5:20 PM
    Sunday, July 29, 2012 5:19 PM

Answers

  • Sunday, July 29, 2012 8:05 PM
  • The most efficient method is a join, but first you need a distinct key column in the table, e.g. an autonumber, which I've called TransactionID below, to act as the tie breaker where more than one transaction takes place on the same date:

    SELECT T1.CustomerID, T1.RefNbr, T1.SortKey3, T1.Date, T1.Memo, T1.Amount
    SUM(T2.Amount) AS Balance
    FROM Transactions AS T1 INNER JOIN Transactions AS T2
    ON (T2.TransactionID<=T1.TransactionID Or T2.Date<>T1.Date)
    AND (T2.Date<=T1.Date)
    GROUP BY T1.CustomerID, T1.RefNbr, T1.SortKey3, T1.Date,T1.Memo, T1.Amount
    ORDER BY T1.Date, T1.TransactionID;

    Or you can use a subquery:

    SELECT T1.*,
       (SELECT SUM(Amount)
        FROM Transactions AS T2
        WHERE T2.Date  <=  T1.Date
        AND ( T2.TransactionID <= T1.TransactionID
        OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
    FROM Transactions AS T1
    ORDER BY T1.Date, T1.TransactionID;

    For an updatable query you can call the VBA DSum function:

    SELECT Transactions.*,
    DSUM("Amount","Transactions","(TransactionID <= "
    & [TransactionID] & " OR [Date] <> #"
    & FORMAT([Date],"yyyy-mm-dd") & "#)  AND [Date] <= #"
    & FORMAT([Date],"yyyy-mm-dd") & "#") AS Balance
    FROM Transactions
    ORDER BY Transactions.Date, Transactions.TransactionID;

    I'd advise against the use of Date as a column name, however, as it's the name of a built in function, so as a 'reserved' word should be avoided for object names.

    Ken Sheridan, Stafford, England

    Sunday, July 29, 2012 9:54 PM
  • Tuesday, July 31, 2012 2:39 PM

All replies

  • I hope I don't make you cringe. Tables are not used to store calculated results from Tables. It is not the intended use in relational databases. This should be on a Query, Form or Report. Access is not a spreadsheet. Now on a Form or Report you can add a textbox in the Detail section with a formula to give you a total at that point.

    Chris Ward

    Sunday, July 29, 2012 5:44 PM
  • Sunday, July 29, 2012 8:05 PM
  • The most efficient method is a join, but first you need a distinct key column in the table, e.g. an autonumber, which I've called TransactionID below, to act as the tie breaker where more than one transaction takes place on the same date:

    SELECT T1.CustomerID, T1.RefNbr, T1.SortKey3, T1.Date, T1.Memo, T1.Amount
    SUM(T2.Amount) AS Balance
    FROM Transactions AS T1 INNER JOIN Transactions AS T2
    ON (T2.TransactionID<=T1.TransactionID Or T2.Date<>T1.Date)
    AND (T2.Date<=T1.Date)
    GROUP BY T1.CustomerID, T1.RefNbr, T1.SortKey3, T1.Date,T1.Memo, T1.Amount
    ORDER BY T1.Date, T1.TransactionID;

    Or you can use a subquery:

    SELECT T1.*,
       (SELECT SUM(Amount)
        FROM Transactions AS T2
        WHERE T2.Date  <=  T1.Date
        AND ( T2.TransactionID <= T1.TransactionID
        OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
    FROM Transactions AS T1
    ORDER BY T1.Date, T1.TransactionID;

    For an updatable query you can call the VBA DSum function:

    SELECT Transactions.*,
    DSUM("Amount","Transactions","(TransactionID <= "
    & [TransactionID] & " OR [Date] <> #"
    & FORMAT([Date],"yyyy-mm-dd") & "#)  AND [Date] <= #"
    & FORMAT([Date],"yyyy-mm-dd") & "#") AS Balance
    FROM Transactions
    ORDER BY Transactions.Date, Transactions.TransactionID;

    I'd advise against the use of Date as a column name, however, as it's the name of a built in function, so as a 'reserved' word should be avoided for object names.

    Ken Sheridan, Stafford, England

    Sunday, July 29, 2012 9:54 PM
  • Tuesday, July 31, 2012 2:39 PM