I have written a query which eventually returns via a crosstab a list of completions of activities
by financial week number as in:

Week Activities Completed
1043 23
1044 6
1045 18
1046 12
1047 22

To geberate a chart frm this showing the progress rate, would need to calculate the totalk number,
in this case for example 23+6+18+12+22 = 81

So a progress graph (or glidepath as we call it) would show:

1043 81
1044 58
1045 52
1046 34
1047 22
1048 0

Which would give you the estimate to completion along a trendline in a chart, or better still, use a
target line in parallel with the data if the forecast outturn is not linear.

I have done a fair bit of googling and looking through various forums, but this appears to be an
area that is not really covered that well. I suppose one method would be to generate a table, load
in one record at a time and retain the values, do the calc and save it to a new glidepath table, but
that seems a little long winded. Is there a simple method of writing a query to do this on the fly
to use as the basis for a chart?

Thanks. J




--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

On 18/07/2011 17:40:50, schwallyhoo wrote:
This might give you a start

http://www.fabalou.com/Access/Querie...TotalQuery.asp

Phil

On Mon, 18 Jul 2011 11:40:50 -0500, schwallyhoo <sumone (AT) hotmail (DOT) com>
wrote:

The following is in SQL Server 2008 Express. It will not give
you the final 1048 0 line, but will otherwise give the output you
want. You could union it with max(WeekNr)+1 0, but I do not know
enough about your problem domain.

***** Start of Code *****
use tempdb
go

drop table ActivityCount
go

create table ActivityCount
(
WeekNr int not null,
ActCount int not null
)
go

insert into ActivityCount
(WeekNr,ActCount)
values
(1043,23),
(1044,6),
(1045,18),
(1046,12),
(1047,22)
go

select
WeekNr,
(
select sum(ActCount)
from ActivityCount as Sub
where Main.WeekNr<=Sub.WeekNr
) as ToDo
from ActivityCount as Main
order by WeekNr
go
***** End of Code *****

HTH.

Sincerely,

Gene Wirchenko

I tweaked the text below, and raised a query in Access SQL using the statements.

select
WeekNr,
(
select sum(ActCount)
from ActivityCount as Sub
where Main.WeekNr<=Sub.WeekNr
) as ToDo
from ActivityCount as Main
order by WeekNr


Results look promising up to now, I need as you infer to carry over the actual max activities, as
this will be a week on week reduction to a target curve (or more aptly described a target staircase
as it is not linear) and that would be the start point as opposed to the count of activities in the
SQL. I will have a tweak and see what I come up with. I can actually use a make table query to
generate a table, then delete for each function, and use the same query to pull the data, as long as
I use alias to generate the same fieldnames for the data table.

Thanks very much for assistance..

J


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -