AVG not working on col heading value in crosstab

[Rebecca]

Here is my crosstab query that is attempting to create a
column with the average of my hours columns.
What is happening is that the average works if each hours
column has values, but if some of the columns have no
hours the divisor for the average calculation is being
reduced by one for each column with no hours. I need all
columns factored in. E.g. if I have three hours columns
and they contain 6,3,0 I want the average to be 3 not 4.5
as it comes out now. I can't use a fixed divisor because
the number of hours columns depends on the input date
parameters. Help!

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

 

[Michel Walsh]

HI,


It may depends of the context. Assuming the values are present, but with
a NULL value, I would try

..., AVG( Nz( SumHours, 0 ) AS AvgHours, ...


Hoping it may help,
Vanderghast, Access MVP