How to get Running Total in Query ? 1

[breukelen]

I have something like this in query:

Id Descr Amount
1 AAAA 100
2 BBBB 200
3 CCCC 300

I like to get :

Id Descr Amount Running T.
1 AAAA 100 100
2 BBBB 200 300
3 CCCC 300 600

Help is very much appreciated,thanks in advance.

Gunter

 

[GingerR]

add a field in your query:

RunningSum: dsum(&quot;[Amount]&quot;,&quot;Table1&quot;,&quot;[ID]<=&quot;&ID)

this says:
sum the Amount field in the table Table1 where the ID in Table1 is <= {the ID in this row in the query}

you'll have to change the table name.

g

 

[JimHorton]

g,
Excellent solution, if the query order is always by a PK.
But an academic question: What if the query is sorted Amount, and you have sevaral amounts = 100?
--Jim

 

[breukelen]

Ginger ,

Thank you very much for your good advice.
It worked fine !!

best regards ,

Gunter

 

[MichaelRed]

Any soloution involving a domain aggregate function within a query is likely to be (painfully) slow for production processes. You should CAREFULLY review the app to determine how and where the 'running sum' will be used and look for alternative soloutions.

MichaelRed
m.red@att.net

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

 

[GingerR]

michael-i agree it is slow. i use it fairly frequently at work to create trend metrics. if i can, i crunch data once in the morning, getting as far thru my calculations as i can, leaving as much as possible in a table that can be queried on all day using The Final Query by users choosing diff criteria.

what alternate solutions?

 

[astrevens]

how do you do that if you do not have an &quot;ID&quot; field?
i have customer account codes, how can i do that?

 

[MichaelRed]

Ginger, as I mentioned, you need to review the overall process to know how to improve the overall application. One thing I would look into would be to simply use a form/report, as the running sum is easily implemented here, and you can include some basic plots (graphs). Another would be to implement the running sum in a simple maketble, so the calculation is not recalling entire records to do the simple calcuation, however this can be problmatical, depending on the network situation. An attractive alternative (to me) would be to just have a small module. this would require a damll twist, as the module (or the sum var) needs to be static, and then you need a flag to re-set the total to 0 for each calculation, and then you need to either put the whole process in another procedure (which sets the flag for you on ONE call to the runsum, or YOU need to rember to do this yourself.

I have provided (below) a procedure to do the calc in a module:

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

 

[BoxHead]

astrevens,

You could run a make table query and then use ALTER TABLE on the new table to add a column.

ALTER TABLE TempTable ADD COLUMN NewID Counter;

Run your query against the TempTable referenceing NewID to get your running sum. Same should hold true if you want to change the sort order to a table that has an existing ID field (as to Jim's post).


HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[astrevens]

sorry cant get that Alter table to work, can you totally simplify it for me, thanks

 

[BoxHead]

astrevens,

I put the following on a command button click event.

Private Sub Command0_Click()

DoCmd.RunSQL ("SELECT tblAccounts.EmployeeName, tblAccounts.AmountDue, tblAccounts.AccountNumber INTO qryTempNewID FROM tblAccounts;")

DoCmd.RunSQL ("ALTER TABLE qryTempNewID ADD COLUMN NewID Counter;")

End Sub

The result was a new table named 'qryTempNewID' with the following columns:
EmployeeName
AmountDue
AccountNumber
NewID

NewID is the incremented ID field that you would run the query against:

RunningSum: dsum(&quot;[AmountDue]&quot;,&quot;qryTempNewID&quot;,&quot;[NewID]<=&quot;&NewID)

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[JerryDennison]

As MichaelRed says, domain aggregate functions are excrutiatingly slow. If you'll use a subquery you can improve performance dramatically. Use the following instead of the DSum() function you are using:

RunningSum: (Select Sum (Amount) FROM [Table1] as Temp
WHERE [Temp].[OrderID] <= [Table1].[OrderID])

This technique is known as using an Alias. This can be done in a query. You can also use the Alias technique to build your sequential number in a seperate query (if you'd like to change the order for example) then use this query as the basis for another one to get the running sum. Of course, it's usually far easier to build a report which has running sum built in.

 

[BoxHead]

Jerry,

I tried the method from your post in a new query (because frankly, I couldn't figure out what it was supposed to do without trying it).

It worked!


Now, is there a way to use this to add an Id column?


Thanks,



John

Use what you have,
Learn what you can,
Create what you need.

 

[JerryDennison]

Actually, there is. But (there's always gotta be a &quot;but&quot; doesn't there?), you must have a unique identifier for each record. This identifier doesn't have to be sequential but it must be unique and incremental in some fashion.

RowNum: (Select Count (*) FROM [tblOrders] as Temp
WHERE [Temp].[OrdNum] < [tblOrders].[OrdNum])+1

Again, this uses the concept of subquery and ALIAS.

 

[BoxHead]

Jerry,

Just wanted to say thanks. I only started working with queries 5 or 6 weeks ago and the tips you gave here have really opened up some exciting possibilities.

Greatly appreciated!

John

Use what you have,
Learn what you can,
Create what you need.

 

[JerryDennison]

UR Welcome.

 

[martinloat]

Hi
I have a similar problem but I need to use a date field as my unique identifier and I need to count incidences of an event per month over several years and then do a running sum on the result.
The reason for not using the running sum in Reports is that I wish to reserve this to produce a graph as my final output.
Is there an elegant way of doing this?
Thanks
Perry

 

[JerryDennison]

Normally, dates are not unique identifiers. Are you sure that you will not have two dates alike? If so, you could in fact use the date field just as you would the number field (for the running sum query anyway). Just use a subquery using the date field in place of the number field. It should work the same way.

 

[martinloat]

Hi
I have got to the point where I have data in the form I need (year/month occurances)using the following:

1/ A General Query to collect the fields I need
2/ Query RSum1 to format the date so it is unique and can count occurances per month (plus any fields to use as filters).

SELECT DISTINCTROW Format$([Query General].[AuditDate],'yyyy mm') AS [AuditDate By Month], [Query General].[Action Type] AS [First Of Action Type], [Query General].Status AS [First Of Status], Count(*) AS [Count Of NonConformances]
FROM [Query General]
GROUP BY Format$([Query General].[AuditDate],'yyyy mm'), [Query General].[Action Type], [Query General].Status, Year([Query General].[AuditDate])*12+DatePart('m',[Query General].[AuditDate])-1
HAVING ((([Query General].[Action Type])=&quot;ECA&quot AND (([Query General].Status)=&quot;closed&quot);

3/ Using Michael Red's module I produce a running sum:

SELECT [Action RSum 1].[AuditDate By Month], [Action RSum 1].[Count Of NonConformances], basRunSum([Action RSum 1]![Count Of NonConformances],0) AS CummVal
FROM [Action RSum 1];

I find this works fine, but as he warns only on the first run through, after which the values aggregate.

My difficulty is that I wish to use the little sub-routine he offers for resetting the Cummval to &quot;0&quot; using the final reports Open event but I cannot seem to get it to work.
Can anyone offer an &quot;idiots&quot; guide to doing this (including syntax)please? or a viable alternate.
TIA
Perry

 

[martinloat]

Problem solved. I just put it on the OnClick property of the command button calling the query. (Obvious Really).

This gives me the ability to plot curves independently. Though it cannot handle the following pivot table result, due I guess to there being two values per unique identifier.

YearMonth Open Closed CumOpen? CumClosed?
99Dec 5 2 5 2
00Jan 4 9 2
00Feb 3 6 12 8

Is there any way of doing this please?
All the best
Perry