Running Total in Crosstab Query

[puttygutt]

I'm trying to sum a series of transactions by month, then continue to run the total from month to month. I created a regular select query with a sum statement:

SELECT ARTRANS.[Artrans ID], ARTRANS.[Year Due], ARTRANS.[Month Due], ARTRANS.Customer, ARTRANS.Facility, ARTRANS.[Transaction Date], ARTRANS.[Transaction Amount], ARTRANS.[Transaction Type], (SELECT Sum([Transaction Amount]) FROM [ARTRANS] AS [ARTRANS_1] WHERE [ARTRANS_1].[Year due] <= [ARTRANS].[YEAR due] and [artrans_1].[Month due] <= [artrans].[Month due]) AS RunningSum
FROM ARTRANS
WHERE (((ARTRANS.Customer)="10419") AND ((ARTRANS.Facility)="211") AND ((ARTRANS.[Transaction Type])<>"ADJUST"))
ORDER BY ARTRANS.[Year Due], ARTRANS.[Month Due];

Then, I created a crosstab query using this select query above as the input view. Here is the crosstab query:

TRANSFORM Sum([qryRunTotal-5].RunningSum) AS SumOfRunningSum
SELECT [qryRunTotal-5].[Year Due], Sum([qryRunTotal-5].RunningSum) AS [Total Of RunningSum]
FROM [qryRunTotal-5]
GROUP BY [qryRunTotal-5].[Year Due]
PIVOT [qryRunTotal-5].[Month Due];

When I run the crosstab query, I receive the following error message:

"The Microsoft Jet database engine does not recognize '[ARTRANS].[YEAR due]' as a valid field name or expression."

Any thoughts/recommendations as to how I can accomplish a running sum in a crosstab?

Thanks!

Peter

 

[SkipVought]

Hi,

Maybe this...

SELECT
  ARTRANS.[Artrans ID]
, ARTRANS.[Year Due]
, ARTRANS.[Month Due]
, ARTRANS.Customer
, ARTRANS.Facility
, ARTRANS.[Transaction Date]
, ARTRANS.[Transaction Amount]
, ARTRANS.[Transaction Type]
, (SELECT Sum([Transaction Amount]) 
   FROM [ARTRANS] AS [ARTRANS_1][b]
      ,  ARTRANS[/b]
   WHERE [ARTRANS_1].[Year due] <= [ARTRANS].[YEAR due]
     and [artrans_1].[Month due] <= [artrans].[Month due]
  ) AS RunningSum

FROM ARTRANS

WHERE (((ARTRANS.Customer)="10419")
  AND ((ARTRANS.Facility)="211")
  AND ((ARTRANS.[Transaction Type])<>"ADJUST"))

ORDER BY
  ARTRANS.[Year Due]
, ARTRANS.[Month Due];

Skip,

_[red][/red]

 

[puttygutt]

I figured it out -

I created two queries - a select query and a crosstab query. I use the select query as the view for the crosstab query. Here is the select query (I named it qryRunningTotals):

SELECT [Year Due] & IIf(Len([Month Due])=1,"0" & [Month Due],[Month Due]) AS DateKey, Sum(ARTRANS.[Transaction Amount]) AS MonthlyTotal, Format(DSum("[Transaction Amount]","ARTRANS","[Year Due] & IIf(Len([Month Due])=1,""0"" & [Month Due],[Month Due])<=" & [DateKey] & ""),"#,##0.00;(#,##0.00)") AS RunTot, ARTRANS.[Year Due], ARTRANS.[Month Due], ARTRANS.Customer, ARTRANS.Facility
FROM ARTRANS
GROUP BY [Year Due] & IIf(Len([Month Due])=1,"0" & [Month Due],[Month Due]), ARTRANS.[Year Due], ARTRANS.[Month Due], ARTRANS.Customer, ARTRANS.Facility
ORDER BY [Year Due] & IIf(Len([Month Due])=1,"0" & [Month Due],[Month Due]), ARTRANS.[Year Due], ARTRANS.[Month Due];

Here is the crosstab query using the above select query as the view/input query:

TRANSFORM Sum(qryRunningTotals.RunTot) AS SumOfRunTot
SELECT qryRunningTotals.[Year Due]
FROM qryRunningTotals
GROUP BY qryRunningTotals.[Year Due]
PIVOT qryRunningTotals.[Month Due];

Thanks!

Peter