Rolling 3 Month Average

[John]

I have been working on this all day and can't seem to make it work. I have a
crosstab query that shows a cost code and description and monthly period
cost. I added an average field at the end of all the values that looks like
this in the query grid:

AverageCost: Avg(IIf([ACst]-[PP_ACst]=0,Null,[ACst]-[PP_ACst]))

This is working great, but now I would also like an average cost based on
the last three months. I think this is a task for a subquery, but I can't
seem to get the syntax correct. In the query grid I put:

3MoSum: (SELECT SUM([tblDupe]![ACst]-[tblDupe]![PP_ACst]) tblMonthly AS
tblDupe WHERE [tblDupe]![act] = [tblMonthly]![act] and [tblDupe]![FnDte] <=
dateadd("m",-3,datevalue([forms]![frmProjectCost]![cmbCurMon]))) / 3

The field 'ACst' is the total actual cost to date, and the field 'PP_ACst'
is the total actual cost to date last month. The difference is the period
cost, for which I need an average.

Any help would be appreciated...

 

[John]

Here's the cross tab SQL:

PARAMETERS [forms]![frmProjectCost]![cmbCurMon] DateTime;
TRANSFORM First(IIf([ACst]-[PP_ACst]=0,Null,[ACst]-[PP_ACst])) AS PeriodCost
SELECT tblXTab_CMIS.ActID, Last(tblXTab_CMIS.ActTitle) AS ActivityTitle,
Last(tblXTab_CMIS.BCst) AS BudgetedCost, Sum([ACst]-[PP_ACst]) AS ActualCost,
Last(tblXTab_CMIS.PCT) AS [Percent],
Avg(IIf([ACst]-[PP_ACst]=0,Null,[ACst]-[PP_ACst])) AS AverageCost
FROM tblMonthly LEFT JOIN tblXTab_CMIS ON tblMonthly.act = tblXTab_CMIS.ActID
WHERE (((tblMonthly.FnDte)=[forms]![frmProjectCost]![cmbCurMon]))
GROUP BY tblXTab_CMIS.ActID
PIVOT tblXTab_CMIS.Period;

There was an error in my last post: 'tblMonthly' should have been
'tblXTab_CMIS'. This correction didn't fix my problem. I still get an
error:

Syntax error. in query expression '(SELECT sum(tblDupe.ACst -
tblDupe.PP_ACst)/3 FROM tblXTab_CMIS AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte >= dateadd("m",-3,tblDupe.FnDte) ORDER BY
tblDupe.FnDte Desc, tblDupe.act)'.

The above is what I have now. I tried moving the "/3" to afte the sum
statement. ??? Still no luck.