Calculate query's field with two result

[eugz]

Hi All
I try to create a query based on Table1 and fields Date and Result. Is it posible to get a query that calculate two amount of Result field when that field is null and not null?
That two values of Result field I will use to create monthly Pivot report in which each bar will display amonts Completed and NonCompleted result.
Thanks.

 

[Jon K]

It depends on what you mean by calculate two amount of Result field when that field is null and not null.

Assuming you have a Table1 like this:

Date		Result
28/9/2005	10
28/9/2005	
28/9/2005	15
29/9/2005	
29/9/2005	20

you can use this query:

SELECT [Table1].[Date],
-Sum(Not IsNull([Table1].[Result])) AS [Count Of NonNull Results],
-Sum(IsNull([Table1].[Result])) AS [Count Of Null Results]
FROM Table1
GROUP BY [Table1].[Date];


to return the daily numbers of Non-Null and Null Results like these:

		Count Of 		Count Of 
Date		NonNull Results		Null Results
28/9/2005		2			1
29/9/2005		1			1

Note
Date is a function name in Access. Better avoid using it as a field name.
.