Prev: Indexing 2 sides of a relationship
Next: I Can't Do This
From: BB on 15 Apr 2010 08:39
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?





From: vanderghast on 15 Apr 2010 08:54
TRANSFORM AVG( Nz( [Integrate Query].Total , 0 )) AS AvgOfTotal
SELECT ...



Vanderghast, Access MVP


"BB" <BB(a)discussions.microsoft.com> wrote in message
news:BFBC3C36-9084-412B-8E92-5779D3B175FE(a)microsoft.com...
> 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?
>
>
>
>
>

From: Daryl S on 15 Apr 2010 09:36
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?
>
>
>
>
>
 | 
Pages: 1
Prev: Indexing 2 sides of a relationship
Next: I Can't Do This