Running Total Query Crashes Access 2003

[longestdrive]

Hi All
As usual I've run into a brick wall...
I'm trying to get a running total column in a query, using references found here, but each time I try and execute the query it crashes Access (version 2003). No error messages produced.

The query is based on another query that totals monthly records using "group by" gouping on the month. I'm using the Month (which is mm/yy) as my unique key for the running total.

Here's my query:

SELECT RptMonth, Planned, Actual, (SELECT Sum(qry_YTD.Planned) AS TotalPlanned FROM qry_YTD WHERE qry_YTD.RptMonth <= T1.RptMonth) AS TotalPlanned
FROM qry_YTD AS T1;

I haven't a clue where I'm going wrong - can anyone help please?

As a final note - I eventually want to use this report as the basis for a chart in a report - A column representing each months Planned and a line for the cumulative planned

Thanks

 

[genomon]

AS TotalPlanned

Without seeing the SQL behind the subquery, could it be Access doesn't like you using the same alias twice? Try changing one of the AS TotalPlanned to something else...

Ever notice how fast Windows runs? Me neither.

 

[longestdrive]

Hi

I changed the alias of one of them but still a crash.

Here's the sql for the underlying query:

SELECT qry__monthlyData.Month AS RptMonth, Sum(qry_monthlyData.[Planned in Month]) AS Planned, Sum(qry__monthlyData.[Delivered in Month]) AS Actual
FROM qry_monthlyData
WHERE (((qry_monthlyData.status)=2) AND ((qry_monthlyData.include_MIS)=True))
GROUP BY qry_monthlyData.Month
HAVING (((qry_monthlyData.Month) Between #4/1/2009# And #3/31/2010#) AND ((Sum(qry_campaignLvl_monthlyData.[Planned in Month]))>0))
ORDER BY qry_monthlyData.Month;

I'm going to try and test on a simpler query.

Ta