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?

BB -

Look at the nz function, which will replace a null with any value you want
(in this case zero):

nz([Integrate Query].Total,0)

--
Daryl S


"BB" wrote:

> 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?
>
>
>
>
>