Calaculating a column sum from within the same query

[likeyeah]

Below is the result of a select query which generates a list of planned activities based on a crosstab, which
totals the ouptut of four sections and combines them into a wek number and how many are closed this week.


KeyEventWeek TotalPlanned
0802 4
0806 3
0915 26
0917 6
1005 1
1009 2
1013 102
1035 2
1050 177
1111 61
1128 8
1129 37
1139 126
1218 72
1209 47
1227 140
1301 1
1314 1


I want to convert these to a glide path which will be in effect give me th total of all activities on the
TotalPlanned Column - the weekly total. To do this I need to pull out the total number of activities in the
TotalPLanned column as the number will change as the project plan changes, which is why I do not want to use a
fixed variable.

I suppose what I am after is a sort of DSum routine that will add up all the numbers in the TotalPlanned columns
but is itself the third column of the query. If we named this AllupTotal that way I could generate a fourth
column which would be GlidePathFig: AllupTotal - TotalPlanned

Is this possible or do I need to reat a separate query run by code to dump the All up total figure into a Global
Variable and call it in the query??

Thanks

 

[Bob Barrows]

Below is the result of a select query which generates a list of
planned activities based on a crosstab, which totals the ouptut of
four sections and combines them into a wek number and how many are
closed this week.


KeyEventWeek TotalPlanned
0802 4
0806 3
0915 26
0917 6
1005 1
1009 2
1013 102
1035 2
1050 177
1111 61
1128 8
1129 37
1139 126
1218 72
1209 47
1227 140
1301 1
1314 1


I want to convert these to a glide path which will be in effect give
me th total of all activities on the TotalPlanned Column - the weekly
total. To do this I need to pull out the total number of activities
in the TotalPLanned column as the number will change as the project
plan changes, which is why I do not want to use a fixed variable.

I suppose what I am after is a sort of DSum routine that will add up
all the numbers in the TotalPlanned columns but is itself the third
column of the query. If we named this AllupTotal that way I could
generate a fourth column which would be GlidePathFig: AllupTotal -
TotalPlanned

Is this possible or do I need to reat a separate query run by code to
dump the All up total figure into a Global Variable and call it in
the query??

You're not being clear at all ... "glide path"??
Is it a rolling sum you're after? If so, you should do it in a report, which
has the capability of performing rolling sums builtin.
If not, or, if you cannot use a report for some reason, you need to show me
what you want the result to look like instead of trying to describe how to
get to that desired result.

 

[likeyeah]

Bob,

What I want to end up with is a query which delivers the following. I have the first 2 columns from a Crosstab
Query, the third column is the sum of column 2. There are certain activities in the plan per week, therefore
Column 2 - Column 1 gives a decreasing indication of work package starting at 816 and ending at 0, i.e. Project
Completion.

The term Glidepath is used to represent such a trend, sometimes linear, sometimes stepped as in this one as when
you graph it, which is what I want to do, it is a line decreasing to zero.

I can do it quite easily in a report, aware of that, but it is not going to be used in a report, but as one curve
of a graph the other being the actual closure of activities so performance can easily be illustrated by looking at
the comparison on one graph.

The KeyeventWeek and TotalPlanned column, reside in a table geberated from a Crosstab query which rolls up the
data from four sections into a figure representing the sum of all four sections activity completion.

I was having a further play last night, and can probably do something like set a global variable by using a
calling a function using Dsum on this table and call the variable within the query as column 3, but I think if I
do that then I will not be able to use the column 3 - column 2 as it is calculating on the fly.

I was just enquiring as to whether anyone had a quick and easy solution already developed.



KeyEventWeek TotalPlanned Total Activities Activities To Completion
0802 4 816 812
0806 3 816 809
0915 26 816 783
0917 6 816 777
1005 1 816 776
1009 2 816 774
1013 102 816 672
1035 2 816 670
1050 177 816 493
1111 61 816 432
1128 8 816 424
1129 37 816 387
1139 126 816 261
1218 72 816 189
1209 47 816 142
1227 140 816 2
1301 1 816 1
1314 1 816 0

 

[Bob Barrows]

Bob,

What I want to end up with is a query which delivers the following.
I have the first 2 columns from a Crosstab Query, the third column is
the sum of column 2. There are certain activities in the plan per
week, therefore Column 2 - Column 1 gives a decreasing indication of
work package starting at 816 and ending at 0, i.e. Project
Completion.

KeyEventWeek TotalPlanned Total Activities Activities To Completion
0802 4 816 812
0806 3 816 809
0915 26 816 783

Thank gawd you showed the desired result. I was completely confused until I
saw that you had included it. I would forget about Dsum. This will take two
more queries (it could take one but it would be harder to write) - the first
calculates the total activities column and the second uses a correlated
subquery to calculate the 4th column:

Query1:
select KeyEventWeek, TotalPlanned,
select sum(TotalPlanned) FROM worktable) As TotalActivities
FROM worktable

Query2:
select KeyEventWeek, TotalPlanned,TotalActivities,
TotalActivities -
(SELECT Sum(TotalPlanned) from worktable as i
WHERE i.KeyEventWeek <= q.KeyEventWeek)
As ActivitiesToCompletion
FROM Query1 as q

 

[likeyeah]

Bob, thanks very much....

Tweaked a touch but queries below based on your answer.

The optimal way to speed up the process I think will be to run the crosstabs to collate the data and use a make
table to set the data to be read by Query 1, then query 2. This is only going to be a weekly run, So I will do it
in code from the reporting form, and put a message box up for the few seconds it will take.

This is the best way I have seen of doing this calculation, and I have other applications for it as well I think.

Regards

J


REM: Query 1

SELECT tbl_Evaluations_Planned.KeyEventWeek, tbl_Evaluations_Planned.TotalPlanned, (select sum(TotalPlanned) FROM
[tbl_Evaluations_Planned] ) AS TotalActivities
FROM tbl_Evaluations_Planned;



REM: Query 2


SELECT q.KeyEventWeek, q.TotalPlanned, q.TotalActivities, TotalActivities-(SELECT Sum(TotalPlanned) from
tbl_Evaluations_Planned as i
WHERE i.KeyEventWeek <= q.KeyEventWeek) AS ActivitiesToCompletion
FROM qry_Metric1_Evaluations_Graph AS q
ORDER BY q.KeyEventWeek;

 

[Bob Barrows]

Bob, thanks very much....

Tweaked a touch but queries below based on your answer.

The optimal way to speed up the process I think will be to run the
crosstabs to collate the data and use a make table to set the data to
be read by Query 1, then query 2. This is only going to be a weekly
run, So I will do it in code from the reporting form, and put a
message box up for the few seconds it will take.

This is the best way I have seen of doing this calculation, and I
have other applications for it as well I think.

Regards

J


REM: Query 1

SELECT tbl_Evaluations_Planned.KeyEventWeek,
tbl_Evaluations_Planned.TotalPlanned, (select sum(TotalPlanned) FROM
[tbl_Evaluations_Planned] ) AS TotalActivities
FROM tbl_Evaluations_Planned;



REM: Query 2


SELECT q.KeyEventWeek, q.TotalPlanned, q.TotalActivities,
TotalActivities-(SELECT Sum(TotalPlanned) from
tbl_Evaluations_Planned as i
WHERE i.KeyEventWeek <= q.KeyEventWeek) AS ActivitiesToCompletion
FROM qry_Metric1_Evaluations_Graph AS q
ORDER BY q.KeyEventWeek;

Here's a single-query version that might perform a little better:

SELECT t.KeyEventWeek, t.TotalPlanned, q.TotalActivities
, q.TotalActivities-(SELECT Sum(TotalPlanned) from
tbl_Evaluations_Planned as i
WHERE i.KeyEventWeek <= t.KeyEventWeek) AS ActivitiesToCompletion
FROM tbl_Evaluations_Planned As t,
(select sum(TotalPlanned)AS TotalActivities
FROM [tbl_Evaluations_Planned] ) AS q
ORDER BY t.KeyEventWeek;

As long as KeyEventWeek is indexed, it should perform pretty well unless you
have thousands of records.