Running Sum Query to create Graph / Chart

Running Sum (using DSum) Query to create Graph / Chart

I am trying to create a RUNNING SUM to create a graph.

I have two project numbers, and every week, I calculate we much time was spent for each project number. I want to create a graphe to show how much time I've spent to date for each project number and I want to show it as a running sum by week. How do I create a query to do that? I know a report can do that but that doesn't help since I need to be able to get the data from a table or query to create the graph?



Edit: There is now an article demonstrating how to create a running total/sum/average/count. But by all means please continue to read this thread!

This'll give you an idea of what I want to accomplish.

This will do what you need

http://www.access-programmers.co.uk/forums/attachment.php?s=&postid=101818

Thank You!!!!!!!!

Geoff Codd,

Thank you!!!!!!!

Question...

Is the field Ranking a must for the expression RunningSum to work?

RunningSum: Val(DSum("[test]","[The Ranking]","[Hosp] & [Pls] & [Ranking] <= '" & [Hosp] & [Pls] & [Ranking] & "' and [Hosp] & [Pls] = '" & [Hosp] & [Pls] & "'"))

So this does this,

Sum [Test] from table [The Ranking] for Each [Hosp] & [Pls] & [Ranking] combination for each unique [Hosp] & [Pls] combination.

Am I understanding it correctly?

EDIT: Could you explain what the critera portion of the expression is doing? I don't understand it.

Thanks! This goes in my must-keep file!

Your question inspired me to re-examine the query in my RunningSum_Access97.mdb. Though it worked, I found it to be a bad piece of code that could be made much simpler.

Here is the improved code:-

SELECT HOSP, PLS, Test AS Percentage, Ranking,
val(DSum("[Test]","The Ranking","Hosp & Pls = '" & Hosp & Pls & "' and Ranking <= " & Ranking)) AS RunningSum
FROM [The Ranking];

(RunningSum_Access97_improved.zip 11K is attached.)


The Ranking field is needed for making the comparison <= in DSum. For each record, the criteria asks Access to sum the Test field WHERE Hosp & Pls equals to the Hosp & Pls in the current record AND Ranking is <= the Ranking in the current record. The value of Ranking must be unique within each group of Hosp & PLs.


So long as there is a field for making the comparison <= within a group, DSum can be used to produce a running sum within each group.

In another post you said you had a query that produced:-

Project - Task - Week ------ Cost
P1 ------- 1 --- 01/07/02 -- $10
P1 ------- 1 --- 01/14/02 -- $15
P1 ------- 1 --- 01/21/02 -- $16
P1 ------- 2 --- 01/07/02 -- $20
P1 ------- 2 --- 01/14/02 -- $22
P1 ------- 2 --- 01/21/02 -- $26
P2 ------- 1 --- 01/07/02 -- $5
P2 ------- 1 --- 01/14/02 -- $8
P2 ------- 1 --- 01/21/02 -- $12
P2 ------- 2 --- 01/07/02 -- $30
P2 ------- 2 --- 01/14/02 -- $11
P2 ------- 2 --- 01/21/02 -- $16

There the field Week can be compared using <=. Since it is unique within each group of Project & Task, a running sum can be produced within each group.

Assuming Week is a Date/Time field, the query to produce the running sum is:-

SELECT Project, Task, Week, Cost,
val(DSum("Cost","yourQuery","Project & Task='" & Project & Task & "' and Week <= #" & Week & "#")) AS RunningSum
FROM yourQuery;


If you don't want to combine Project & Task, you can separate them using:-

val(DSum("Cost","yourQuery","Project = '" & Project & "' and Task=" & Task & " and Week <= #" & Week & "#")) AS RunningSum


The running sum is equivalent to:-
Sum Cost
From yourQuery
Where Project = Project AND Task = Task AND Week <= Week

in which Project, Task and Week after the equal sign all refer to the values in the current record.


I hope you will find the improved code simpler and easier to comprehend.

Wonderful Explanation and Improvement!

Jon K,

Thank you for bothering to explain the query expression to me and to actually improve upon it!!


I am sure that this will be very helpful to others and myself.

Just an idea for another use for your expression, another calculation I will be using this for is to calculate moving averages.

For example, taking my Project-Task-Week-Cost, I want to calculate the average cost (starting from the second week) for each week after the first week. To use that as a way to figure out how I am doing on the task budget.

So I would use your calculated Running Sum (used to chart) and maybe just change Val(Dsum... to Val(DCount... to count number of records and divide the Running Sum by the Running Count to get a Moving Average.

Just thought you might be interested in that.

Moving Average from Running Sum and Running Count !

Viola! Moving Average works! COOL!

Thank you, Jon K!!!!!!

Whoops!

Forgot to post the file!!!

Project NUMBER!

Jon K,

Was testing the Running Sum with just Project Number.

Run into a little snag, my Project Number is actually a NUMBER (e.g 123457). Been trying to get the RunningSum to work, and at first I thought it was a problem with the Week. But I changed my Project Number to text and entered P1, P2, P3 .... the Running Sum worked. Do I need to change this part

SELECT Project, Task, Week, Cost,
val(DSum("Cost","yourQuery","Project ='" & Project & "' and Week <= #" & Week & "#")) AS RunningSum
FROM yourQuery;

What do I need to change it to if Project is a NUMBER and not TEXT?

Sorry for the trouble...

Here's a database that recreates my problem.

For a numeric field, remove the two single quotes, so:

"Project ='" & Project & "' and Week <= #" & Week & "#"

becomes:

"Project =" & Project & " and Week <= #" & Week & "#"

Works like a charm!

Jon K,

Works like a charm!! Bravo!!

Thank you!!

Regarding the use of DSum (Pat Hartman)

Here's Pat Hartman's take on using DSum in queries.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30619

But it's still pretty cool! Jon K's expression works so well!!

I'm still using Jon K's expression (even after Pat Hartman's warning) since I don't have too much data. Some time down the road if it starts to take too long, then I'll export it to the data Excel (via an Access Report with running total).

Hi ,

This is wonderful !! Just what i needed !

Now I only need to modify it to get the DSum for
"ranking < " instead of "ranking <="
But it gives me an error saying "data type mismatch" when I try to make the change.
Is there a way around this?

Would really appreciate the help .
Thanks in advance .

Gina.

GinaO,
what are your fields and table structure?

Better yet, if you can post your database file here (make a copy and get rid of data, forms and reports and be sure to zip it) or email it to me, I'd be happy to try and look at ti for you.

Did you make sure you used the correct syntax for numbers?

JonK said

For a numeric field, remove the two single quotes, so:

"Project ='" & Project & "' and Week <= #" & Week & "#"

becomes:

"Project =" & Project & " and Week <= #" & Week & "#"

Click to expand...

Cosmos75,

Recently I found that I could use a subquery (i.e. a Select statement in brackets) in the Select Clause of a query.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=34037

This gives me an idea of applying it in the running sum and running average calculations so as to get rid of the inefficient Aggregate Domain Functions of DSum() and DCount().


Using subqueries, the moving average query:-

SELECT HOSP, PLS, Test AS Percentage, Ranking,
Val(DSum("Test","The Ranking","Hosp & Pls = '" & Hosp & Pls & "' and Ranking <= " & Ranking)) AS RunningSum,
Val(DCount("Test","The Ranking","Hosp & Pls = '" & [Hosp] & [Pls] & "' and Ranking <= " & [Ranking])) AS RunningCount,
[RunningSum]/[RunningCount] AS MovingAverage
FROM [The Ranking];


can be revised to:-

SELECT a.HOSP, a.PLS, a.Test AS Percentage, a.Ranking,
(Select Sum(Test) from [The Ranking] where Hosp=a.Hosp and Pls=a.Pls and Ranking <=a.Ranking) AS RunningSum,
(Select Count(*) from [The Ranking] where Hosp=a.Hosp and Pls=a.Pls and Ranking <=a.Ranking) AS RunningCount,
[RunningSum]/[RunningCount] AS MovingAverage
FROM [The Ranking] AS a;


It runs significantly faster as Select statements are optimised, particularly when there are many records or when it is run on a slow machine.

And it is easier to write as there is no need to build text strings using double quotes, single quotes, # signs, & characters, and sometimes no quotes, which can be very confusing.


The optimised query is added in the mdb attached.

Jon K

DSUM

Hi ,

Thanks for replying.
I'm sorry I'm having trouble logging in , so i couldn't reply immediately.

I'm attaching my mdb.

Hope you can help.

Thanks,
Gina.

JonK,

WOW! Thank you for staying on top of this thread!

I do have one question.

qryMovingAverage_Optimised refers to the fields in the table [The Ranking], but from a table called a?

How does that work when you don't even have a table named a? Or is that defined through SQL? I understand it in the graphical design view but it (table a) doesn't make sense unless I switch to SQL view (I only understand a little SQL) but it looks like you can define a pseudo-table through SQL, is that what you did?

Why not just reference the table [The Ranking]? I tried that and it doesn't work. But why?

Also, the table [The Ranking] contains a field Ranking, wouldn't it be ideal to have that be a calculated field, i.e. have a RunningCount? While you do have the RunningCount but it seems to need the field Ranking?