Accumulating SUM values in MS Access 2

[nondrinker]

Hello,

I am trying to disply the accumulated sum of the values in Col2, of a certain ID (Col1), in Col3.

Is there a way to have the Col3 display the values like this:

ID Col2 Col3
1 5 5
1 4 9
1 7 16
2 4 4
2 6 10
3 3 3
3 8 11

Thank you.

 

[RickSpr]

It depends. You can't do it with straight SQL at all. You can do it by calling a function that accumulates the sum in a Static variable, but it only works if you read the records sequentially in forward order.

Here's a sample function that does this:

    Public Function RunningSum(Value As Variant) As Long
        Static lngSum As Long

        If IsEmpty(Value) Then
            lngSum = 0
        Else
            lngSum = lngSum + Value
        End If
        RunningSum = lngSum
    End Function

To use it, you need to execute the statement RunningSum Empty first, to reset lngSum to zero, then run the query.

In your query, you add a calculated field with the expression RunningSum(Col2). This adds the current value to the previous running sum and returns the result.

Like I said, this only works for a recordset read sequentially forward. What this means is that if you open the query directly in the user interface, or open a form based on the query, the values will get all screwed up as soon as you start scrolling upward/backward or jump to another record, etc. This happens because these operations cause Access to read the records out of sequence. However, the function will work if you read a forward-only recordset based on the query.

You said you want to display the values, and it sounds like I just said you can't do that, but actually there's a way to make it work: Change the query to a MakeTable query that builds a temporary table. Run it, then open the temporary table to display the results. (Of course, you wouldn't want to allow any updating in the temporary table.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Golom]

Actually, you can do it with SQL ... IF you have certain characteristics in your table. Those "Characteristics" include the ability to order your query on a unique value (an autonumber field is ideal).

   Select f1, f2, 
          (Select Sum(f3) From tbl X Where X.Fnum <= A.FNum) As [RunningSum]
   From tbl As A
   Order By A.FNum

Where "Fnum" is the unique field. You can make it work with multiple fields by doing the match on a concatenation of several fields ... provided that they give you a unique value.

 

[RickSpr]

Golom's solution of using a subquery within the field list is clever. I only found out you could do this (from another post here on Tek-Tips) a couple of weeks ago, and it doesn't yet automatically occur to me to use it.

One warning, however: Besides requiring the special case "characteristics" Golom mentions, this solution could execute very slowly on moderate to large size tables. The reason is that it contains a "correlated subquery", that is, a subquery (the inner SELECT) whose value depends on data in the main query (A.FNum).

Potentially, the execution time of a query containing a correlated subquery increases with the square of the number of rows in the table. That means that if you have 10 times as many rows, it could take up to 100 times as long to run. If you have 100 times as many rows, it could take up to 10,000 times as long to run!

So be cautious about using correlated subqueries in production applications. What may work fine with a little test data may be unacceptable with production-sized tables.



Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[nondrinker]

Thank you very much to both of you.

It did work for me. I did the query way, but would've worked in the code the way RickSpr had mentioned. Since my table is not going to be too big, so i guess having a subquery, like Golom suggested, should be ok with me.

So again thank you to both of you.