Cummulative total query for chart

[RECrerar]

Hi,

I know there are several posts already on cummulative totals, but I am quite new to Access so am having trouble relating them to my project so thought I would start my own.

Basically, I have a database containing information on several projects, for each project there are two lots of weekly data, projected hours and actual hours. I need to be able to produce charts of the cumulative totals of these values either for individual projects or totalled across several projects.

I currently have the following SQL in a query which produces the cumulative totals by week and project.

SELECT a.Week, a.ProjectID, 
(Select Sum(ProjectedHours) from  [qryPandAByWeekAndProject]  
             where  [ProjectID] = a.ProjectID
             and [Week] <= a.Week) AS Predicted, 
(Select Sum(WeeklyTabs) from  [qryPandAByWeekAndProject]  
             where  [ProjectID] = a.ProjectID
             and [Week] <= a.Week) AS Actual
FROM qryPandAByWeekAndProject AS a
ORDER BY a.ProjectID, a.Week;

However I would like to modify it so that by default it calculates the cumulative weekly totals across all projects and then I can limit the projects for which it does this via code. I have a form with a list box and basically when the user clicks the [Generate Totals] button I want it to use the ID of the project(s) selected to limit the query.

I have tried simply removing the [ProjectID] = a.ProjectID line from the SQL to try and stop it sorting them by project ID but this does not work. If anyone could show me how to modify this it would be appreciated.

Also. It is likely that when the database if fully populated there will be a lot of records and I have read that using queries a subqueries to generate the totals in very inefficient and that it would be much better to do this all in code. I really don't know how to do this, so if anyone could help with how to put the code together it would really help.

Thanks
Robyn

 

[redneckgeek]

I currently have the following SQL in a query which produces the cumulative totals by week and project.


However I would like to modify it so that by default it calculates the cumulative weekly totals across all projects...
I have tried simply removing the [ProjectID] = a.ProjectID line from the SQL to try and stop it sorting them by project ID but this does not work.

Can you post your SQL that "didn't work"? What about it doesn't work?

Also. It is likely that when the database if fully populated there will be a lot of records and I have read that using queries a subqueries to generate the totals in very inefficient and that it would be much better to do this all in code. I really don't know how to do this, so if anyone could help with how to put the code together it would really help.

BAH! A properly written query against a well designed and properly indexed database shouldn't be all that slow. It's things like domain aggregate functions and overuse of IIF's that slow you down. Data mining/analysis is a large part of my job. I write complex queries every day, and never have to wait more than 5 seconds for one to execute - most are instantaneous.

 

[RECrerar]

Re: Cumulative total query for chart

I tried a couple of variations on the SQL, either by just removing the sections relating to the project ID or by replacing these by the week field, I was either getting the results still separtated by project ID (that is I would get multiple instance of the same week) or, I would get single instance of the week, but the value given would be the value it was supposed to be multiplied by however many records there were for that week, very odd.
Another peculiarity I was seeing was the cumulative value incresing (I didn't check to see how accurate the increase was) for all the records except for the final 3 which suddenly decreased - obviously wrong.

Anyway

So I have got around this by creating an intermediate query that sums the relative records by week, thus eliminating the Project ID factor and then running the cummulative total query from these results. This works fine with the following SQL:

SELECT a.Week AS Expr1, Sum((Select Sum(SumOfProjectedHours) from  [qryTotalsByWeek]  
             where  [Week] <= a.Week)) AS Predicted, Sum((Select Sum(SumOfWeeklyTabs) from  [qryTotalsByWeek]  
             where  [Week]<= a.Week)) AS Actual
FROM qryTotalsByWeek AS a
GROUP BY a.Week
ORDER BY a.Week;

The query's are actually running much faster now, I think the slowness was really due to me trying to make it do stuff that it couldn't actually do. Now with the splitting it into two very simple queries everything is running much smoother