Subquery for running sum blues.

[Jaazaniah]

Ok, this has been a challenge I've been trying off and on for about a
month now to solve. The speed issues related to domain aggregate
functions sent me looking for better methods. Over the past month or
so, I've been trying to impliment the subquery method posted various
times to this and other groups. The reason for wanting query-level
running sum is so that a graph in the report can use the data. This is
the SQL for of what I've been trying:

SELECT [Month], (SELECT SUM([CUS]) FROM [OwnerByMonth] T2
WHERE T2.[Month] <= T1.[Month]) AS RunningSum
FROM [OwnerByMonth] T1
ORDER BY [Month]

[Month] is a calculated Date field (using CDate() to avoid Variant)
for which there is only 1 entry for each month of the period in
question. OwnerByMonth is a cross-tab query if that makes any
difference. At anyrate, I get the following error when I try to
execute this query:

The Microsoft Jet database engine does not recognise 'T1.[Month]' as a
valid field name or expression.

What am I missing?

 

[Gary Walter]

save results of your crosstab query
to a temp table, then run "running sum"
query on the temp table.

good luck,

gary