Convert Null Counts to zeros

  • Thread starter Thread starter kahaymon
  • Start date Start date
K

kahaymon

I'm trying to count fields, but when I have a null value, there is no zeros.

This is my query
SELECT Count([KH Date Parameter Complaint Query].[Auto Number]) AS HK
FROM [KH Date Parameter Complaint Query]
GROUP BY [KH Date Parameter Complaint Query].HK
HAVING ((([KH Date Parameter Complaint Query].HK)=Yes));
 
You got a couple of terms wrong.
I'm trying to count fields -- you do not count FIELDS but records.
You are counting records where HK = Yes therefore none will be null.
You have a field in [KH Date Parameter Complaint Query] named HK but are
trying to use an alias name of 'HK' at the same time. That is asking for
trouble.
 
kahaymon said:
I'm trying to count fields, but when I have a null value, there is no zeros.

This is my query
SELECT Count([KH Date Parameter Complaint Query].[Auto Number]) AS HK
FROM [KH Date Parameter Complaint Query]
GROUP BY [KH Date Parameter Complaint Query].HK
HAVING ((([KH Date Parameter Complaint Query].HK)=Yes));
Click to expand...


First, change the HAVING clause to a WHERE clause.

Second, if you want to count all the records, use Count(*)

Third, you can not group by a field's alias name.

Fourth, you can not have a GROUP BY query that only has an
aggregate calculated field.

Seems to me that you should back up and provide more details
about your table, its fields and what you are trying to
accomplish. "count fields" doesn't have a lot of meaning.
 
Okay. I have a column in my table for every time there is a complaint about
housekeeping "HK", it is a check box. I have a column for each record that
is an auto number. In this query I'm grouping by the HK Column and counting
the auto numbers. I want a count of zero when there are no checks in HK
Column.


Marshall Barton said:
kahaymon said:
I'm trying to count fields, but when I have a null value, there is no zeros.

This is my query
SELECT Count([KH Date Parameter Complaint Query].[Auto Number]) AS HK
FROM [KH Date Parameter Complaint Query]
GROUP BY [KH Date Parameter Complaint Query].HK
HAVING ((([KH Date Parameter Complaint Query].HK)=Yes));
Click to expand...


First, change the HAVING clause to a WHERE clause.

Second, if you want to count all the records, use Count(*)

Third, you can not group by a field's alias name.

Fourth, you can not have a GROUP BY query that only has an
aggregate calculated field.

Seems to me that you should back up and provide more details
about your table, its fields and what you are trying to
accomplish. "count fields" doesn't have a lot of meaning.
Click to expand...
 
The auto number field is irrelevant in this problem. Try
using:

SELECT COUNT(*) As CountOfHK
FROM [KH Date Parameter Complaint Query]
WHERE HK

Note you can use Count(*) in the query design grid by
setting the Field row to:
CountOfHK: COUNT(*)
and, if you really need the Totals row, select Expression.
This way you do not have to obscure things by counting the
autonumber field.
--
Marsh
MVP [MS Access]

Okay. I have a column in my table for every time there is a complaint about
housekeeping "HK", it is a check box. I have a column for each record that
is an auto number. In this query I'm grouping by the HK Column and counting
the auto numbers. I want a count of zero when there are no checks in HK
Column.


Marshall Barton said:
kahaymon said:
I'm trying to count fields, but when I have a null value, there is no zeros.

This is my query
SELECT Count([KH Date Parameter Complaint Query].[Auto Number]) AS HK
FROM [KH Date Parameter Complaint Query]
GROUP BY [KH Date Parameter Complaint Query].HK
HAVING ((([KH Date Parameter Complaint Query].HK)=Yes));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top