How to convert Null values to zero when create an average query

  • Thread starter Thread starter BB
  • Start date Start date
B

BB

This is my crosstab query

Items Worked April 2010 March 2010 Average

Reports 2 4
3
Tables 5
5
Files 2 2
2

As you can see, for "Tables" the query is giving me an average of "5"
instead "2.5" because is not counting the null value date. How can I fix
that? the SQL query so far is this:

TRANSFORM Avg([Integrate Query].Total) AS AvgOfTotal
SELECT [Integrate Query].[Items Worked], Avg([Integrate Query].Total) AS
[Total Of Total]
FROM [Integrate Query]
GROUP BY [Integrate Query].[Items Worked]
PIVOT [Integrate Query].[Date Worked By Month];

how can I change it?
 
TRANSFORM AVG( Nz( [Integrate Query].Total , 0 )) AS AvgOfTotal
SELECT ...



Vanderghast, Access MVP
 
Back
Top