Need AVG of SUM column in Crosstab query, How?

[Rebecca]

Here is the SQL for my Crosstab query. All is working fine
except the AVG column appears to show the average before
the rows are grouped instead of after. I want the average
to reflect the average of the summed hours. Anyone know
how to fix this?

PARAMETERS [From-Sunday] DateTime, [To-Saturday] DateTime;
TRANSFORM Sum(qryHrsExtended.hours) AS SumOfhours
SELECT qryHrsExtended.dept, qryHrsExtended.engineer,
qryHrsExtended.category, Avg(qryHrsExtended.hours) AS
AvgOfhours, Sum(qryHrsExtended.hours) AS SumOfhours1
FROM qryHrsExtended
GROUP BY qryHrsExtended.dept, qryHrsExtended.engineer,
qryHrsExtended.category
ORDER BY qryHrsExtended.dept, qryHrsExtended.engineer,
qryHrsExtended.category
PIVOT qryHrsExtended.weekending;

 

[[MVP] S. Clark]

Don't fall into the "Gotta do it in ONE query" trap.

Anytime that I need to perform complex calculations, I will start by
creating another table. Then, populate that table with the data needed for
the crosstab or report. The way that data is stored, in a normalized
fashion, may not always be the best for displaying.

Thus, you may need to move the summed data into the temp table, then run the
crosstab query against the temp table, to get the avg of the summed.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-