Running Sum Query to create Graph / Chart

[Cosmos75]

I see it now, you need the field Ranking to identify a unique Test within each HOSP-PLS pair! Am I right?

So it Counts/Sums for Ranking = 1, then 2, then 3 till it reaches the highest ranking for the HOSP-PLS pair!! Hence the Ranking<=a.Ranking part of your SQL Statement.

But doesn't that negate the need for a calculated RunningCount? MovingAverage could just be RunningSum/Ranking.

 

[Jon K]

>
But doesn't that negate the need for a calculated RunningCount? MovingAverage could just be RunningSum/Ranking.
<


The Ranking field (and the demo mdb) originally came from this thread about "Running Total by product line":-
http://www.access-programmers.co.uk/forums/showthread.php?threadid=31158&highlight=Running+sum

You may still remember the RunningCount was added by you when you further developed the RunningSum into MovingAverage calculations. And the RunningCount just happened to be the same as the Ranking.

In other situations, there is not likely to be a ranking field and you will have to calculate the RunningCount (based on a field that can be compared with <=) to arrive at the MovingAverages.

 

[Cosmos75]

What about just using an Autonumber (Maybe the Primary Key?)instead of a Ranking Field? They won't have to be sequential, will they?

Any potential problems? I can't think of any since it'll just Count/Sum for Autonumber = 84, then 128, then 298 till it reaches the highest Autonumber for the HOSP-PLS pair, right? Would the query have to be sorted by HOSP-PLS pair than by Autonumber? Or does it not matter??



JonK,
Thank you for bothering to figure out a better way to accomplish a running sum!!

 

[Jon K]

You are right. You will have no problem using the Autonumber since it can uniquely identify the records within each group of HOSP-PLS and it can be compared using <=.

But most likely, the query will need to be sorted by the HOSP-PLS pair then by the Autonumber just for the sake of our eyes. The calculations themselves do not require such a sorting.


Gina,
If you change
[Month] <= " & [Month] & "
to
[Month] < " & [Month] & "

when [Month] is the smallest, you will not have a Month smaller than the smallest Month to compare, so you will get an error.

 

[NOL]

Thanks Jon !

I have to show a zero for the lowest ranking. So now it works fine! Thanks a lot!

In case anyone is interested ...

IIf([Month]=DMin("Month","ACC 001 - PRINCIPAL","FAC_PID_FACILITY = '" & [FAC_PID_FACILITY] & "'"),0,Val(DSum("Principal","ACC 001 - PRINCIPAL","[Month] <= " & [Month] & " and FAC_PID_FACILITY = '" & [FAC_PID_FACILITY] & "'")))

The mdb is attached.

Regards,
Gina.

 

[Cosmos75]

NOL, (a.k.a GinaO?)

Took the liberty of changing your query to match JonK's new SELECT method for doing running sums instead of using the inefficient Dsum.

Gave me chance to use JonK's new method. Need the practice!


So your SQL Statement is now

SELECT a.FAC_PID_FACILITY, a.Month, a.MonthEndDate, a.Principal AS APricipal, IIf((Select Min(Month) from [ACC 001 - PRINCIPAL] where FAC_PID_FACILITY = a.FAC_PID_FACILITY)=[Month],0,(Select Sum(Principal) from [ACC 001 - PRINCIPAL] where FAC_PID_FACILITY = a.FAC_PID_FACILITY and Month <= a.Month)) AS MySum, a.Outstanding
FROM [ACC 001 - PRINCIPAL] AS a;

It should run faster now!

Am attaching the db file here for you.

Hope this helps!

 

[NOL]

Hi Cosmos,

Yes It's Gina Sorry about the mix-up !

I had tried Jon's optimized query with the Select instead of DSUM and was wondering why it wldn't work for me ..

Now i realise that there are 2 copies of the same table.
That is necessary for this query to work , isn't it ?

I didn't know that one could access a table through SQL even if it wasn't added to the query...
That will certainly help in future !

Thanks a million !

Thanks,
Gina.

 

[Cosmos75]

Now i realise that there are 2 copies of the same table.
That is necessary for this query to work , isn't it ?

I had the same question for JonK, but I guess he didn't notice them or doesn't have enough free time to answer. Maybe someday he will, till then we'll just have to be awed by the magical abilities of his SQL statement!!!


There aren't two copies, the sql Statement just renames the existing table to a. And pulls values from the table and just against those from "table a". I think... not too sure since I never use SQL but it looks like that is what it is doing. JonK can probably answer that.


Yes, It's necessary for it to work. I tried the same thing but just pulling the fields from the table and not renaming the table and it didn't work. As to why? I haven't the foggiest idea and that's a question for JonK.


Sorry I couldn't answer your question...

 

[NOL]

Hi Cosmos ,

Thanks for trying anyway !

I had a closer look at the query and i think a self join is used , not two physical copies as i first thought.

whatever it is , it works great !

Thanks,
Gina.

 

[Jon K]

Cosmos,
My apology.

When you wrote "I see it now, you need the field Ranking .... Hence the Ranking<=a.Ranking part of your SQL Statement", I mistakenly thought that you had already figured it out.


A Select statement in brackets is called a subquery as opposed to the outer query. When the same table is used in both the outer query and the subquery, at least one of the tables must use an alias if there is a reference between the fields in the two queries.

In our example, I used a as the alias for the outer table:-
FROM [The Ranking] AS a

so when I use Hosp=a.Hosp etc in the subquery, Access knows a.Hosp refers to the Hosp field in the outer query.


In this query (which finds the duplicate IDs in Table1):-
SELECT *
FROM Table1
WHERE ID in (Select ID from Table1 group by ID having count(*)>1)

no alias is used since there is no reference between the fields in the outer query and the subquery.

I hope this helps to explain the use of alias "a" in our example.


Gina,
I hope the above has also answered you question.

Cosmos's optimised version of your Running Sum query works fine.


By the way, a self-join is slightly different. There is an example of self-join in this thread. It also uses an alias.:-
http://www.access-programmers.co.uk/forums/showthread.php?threadid=33431&highlight=selfjoin

Jon K

 

[Cosmos75]

JonK,

No need for apologies, I am grateful for any help you have to offer!

 

[NOL]

Thanks Jon !
That clears up a lot of questions !!

Thanks Cosmos , u've been a great help !

 

[WillEllis]

Crosstab won't recognize 'a' table

Jon,

I used your SQL to get my cummulative numbers (work great, thanks!), however, I now have a problem generating a crosstab query on the results. It says it won't recognize 'a.ForecastMonth'. Even after entering this into the Parameters statement, it still will not work. Any suggestions?

 

[michaelvo]

basing it on a query causes Access 2007 to crash..

Hi there,

Thanks to all your info, I finally managed to get this to work.
However, if you base the code below on a query and not a table my Access 2007 crashes...
As a temporary workaround, I use an Action Query to create a table - but this is a bit annoying..
Any suggestions ?

(My goal is to make a chart, based on a query that cumulatively sums up the product quantity in stock, with a view per month)

thanks,

Michael.

NOL, (a.k.a GinaO?)

Took the liberty of changing your query to match JonK's new SELECT method for doing running sums instead of using the inefficient Dsum.

Gave me chance to use JonK's new method. Need the practice!


So your SQL Statement is now

SELECT a.FAC_PID_FACILITY, a.Month, a.MonthEndDate, a.Principal AS APricipal, IIf((Select Min(Month) from [ACC 001 - PRINCIPAL] where FAC_PID_FACILITY = a.FAC_PID_FACILITY)=[Month],0,(Select Sum(Principal) from [ACC 001 - PRINCIPAL] where FAC_PID_FACILITY = a.FAC_PID_FACILITY and Month <= a.Month)) AS MySum, a.Outstanding
FROM [ACC 001 - PRINCIPAL] AS a;

It should run faster now!

Am attaching the db file here for you.

Hope this helps!

 

[maggioant]

Has anyone had trouble creating a graph of the running sum field?

I am new to Access and trying to create a comparison graph using two queries:

Query 1: [Yr], [Mth], [Count_New_Issues], [Total_New_Issues]
Query 2: [Yr], [Mth], [Count_Closed_Issues], [Total_Closed_Issues]

Where [Total_New_Issues] and [Total_Closed_Issues] are running sums.

I am trying to create a bar graph that will compare the two totals fields. How do I create this?

 

[yrosie]

Jon K, I just wanted to say your optimised solution works for my running total purposes too. This is great and thank you for sharing such a smart solution over Dsum!!!