Graphing Cumulative Total over time - how?

[bmarks]

I am trying to plot a cumulative sum over time (Access Chart - Line graph), but cannot figure it out. I've seen the question posted by others on different forums - but have never seen an answer.

I'll simplify what I am trying to do in order to focus on the problem.

I am using Access 2002 and the data is stored in MS SQL
I have sales numbers by day (the sale amount for each transaction and the date of the transaction). If I sum by day I get something like:

Sept 20 $1000
Sept 21 $1500
Sept 22 $500
Sept 23 $900

I want to plot

Sept 20 $1000
Sept 21 $2500 (1000+1500)
Sept 22 $3000 (1000+1500+500)
Sept 23 $3900 (1000+1500+500+900)

I'm not sure if the calculation should be done in a query and then a chart/graph report done on the query, or if there is a way to do it directly on the chart/graph.

Does anyone know how to do this?

 

[MichaelRed]

I am not aware of any way to do a calculation in the chart applet/MS Chart. You need to do the sum elsewhere and provide it to the chart applet as part of the recordset.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[bmarks]

Thanks for you reply.

I had a feeling it would have to be done in a query first and that the chart appliet probably wouldn't handle it. Problem is, I can't figure out how to do a query to do the cumulative totals.

Any hints/help on how to do a cumulative total in a query would be appreciated.

 

[MichaelRed]

Well, it CAN be done in a query, but this is one place where a procedure -along with the query is- probably prefered.

The following procedure MAY be used to enerate your running sum as (in SQL terms):

basRunSum([ReceiptAmount]) AS TotToDate

where

[ReceiptAmount) is the field to be summed

and

TotToDate is the running sum for the field.

You NEED to be cautious in using this, as the STATIC declaration is both necessary AND hazzardous. The hazzard part is that the sum last throughout the programm/application session, so IF you use it a second time, you need to RESET it to Zero. You SHOULD ALWAYS preceed the execution of any process (query?) which relies on this with a call to the function with the VALUES of Zero and TRUE, as illustrated in the comments in hte code.

Public Function basRunSum(valAdd As Variant, _
                          Optional InitFlg As Boolean) As Variant

    
    'To (properly) use this, you need to "Call" it ONCE, as shown below
    'BEFORE each use of the query.  Otherwise it will continue to accumulate
    'the SUM from the last instantiation since it's LAST use in the current
    'Program / Application session.

    '? basRunSum(0, True)
    '0

    Static CummVal As Variant

    If (InitFlg = True) Then
        CummVal = 0
    End If

    CummVal = CummVal + valAdd

    basRunSum = CummVal

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[bmarks]

I had an answer from another user - in another forum (I had wrongly asked my question in two forums - the thread only remains in this forum). I am putting that answer here for those that would like it. This solution works although the query is a little slow.

Answer by: Schroeder (MIS) Sep 26, 2001
This might work for you.
Make a query - “SELECT TransDate, Sum(Sales) AS Sales FROM Transactions GROUP BY TransDate;” I’ll name it DailySales

Make another query - “SELECT DailySales.TransDate, Sum(IIf([DailySales_1]![TransDate] <= [DailySales]![TransDate],[DailySales_1]![Sales], 0)) AS RunningSum FROM DailySales, DailySales AS DailySales_1 GROUP BY Daily Sales.TransDate”

So, the first query sums your daily totals. The second query uses the first one twice with no JOIN expression so that for every record in the first instance, it will check every record in the second instance and add its amount to RunningSum if it should.
It returned results immediately with a test table of 1600 records comprising four days but I suspect it might be a bit inefficient for a much larger recordset.

My Thanks to Schroeder.

I have discovered, if you want to do the running total over a particular year or month and year, just put in the JOIN expression to join on these elements.