Crosstab Query

[Kirk P.]

I'm building a crosstab query using the query grid. Everything works fine up
to this point - here's the SQL generated by Access:

TRANSFORM Sum([Prompt Bud YTD]-[Prompt Fcst YTD]) AS [YTD Var]
SELECT tblCombinedData.BusArea, Sum([Prompt Bud YTD]-[Prompt Fcst YTD]) AS
[Grand Total]
FROM tblCombinedData
WHERE (((tblCombinedData.Type)="ALLOC") AND ((tblCombinedData.[Line Item Cd
AC3])="OPM") AND ((tblCombinedData.Year)=2004))
GROUP BY tblCombinedData.Type, tblCombinedData.[Line Item Cd AC3],
tblCombinedData.Year, tblCombinedData.BusArea
PIVOT tblCombinedData.EntityCd In ("NSP-MN","NSP-WI","PSCO","SPS","Other");

Problem is, I want to display another column header called Total Utility,
which would sum the expression [Prompt Bud YTD]-[Prompt Fcst YTD] for
EntityCd's ("NSP-MN","NSP-WI","PSCO","SPS"). I've tried adding this to the
criteria, but I'm getting a SQL error. The error says I cannot have an
aggregate function in WHERE clause - it seems to be something to do with the
criteria I specified, which is In ("NSP-MN","NSP-WI","PSCO","Other").

Any ideas?

 

[[MVP] S.Clark]

Create another query, based on the Crosstab, and add in the other summary
columns.