Count Null's

[Guest]

I am importing an excel file (created by someone else) that have null values
in it. Can you tell me why the formula count(*) in the query does not work
to count all the records including null's. I am using 2003 version.

 

[Dirk Goldgar]

In

I am importing an excel file (created by someone else) that have null
values in it. Can you tell me why the formula count(*) in the query
does not work to count all the records including null's. I am using
2003 version.

It should. Please post the SQL of the query.

 

[Guest]

When I try to input count(*) I get an error message.

SELECT [MS Table].State, [MS Table].Type, [MS Table].Severity, [MS
Table].Target, [MS Table].Rel, Count([MS Table].Pr) AS CountOfPr
FROM [MS Table]
GROUP BY [MS Table].State, [MS Table].Type, [MS Table].Severity, [MS
Table].Target, [MS Table].Rel
HAVING ((([MS Table].State)<>"Built") AND (([MS Table].Type)="Bug") AND
(([MS Table].Target) Like "*6010*"))
ORDER BY [MS Table].Severity;

 

[Jeff Boyce]

Are you quite certain the values are "null", and not zero-length strings
("")?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Dirk Goldgar]

In

When I try to input count(*) I get an error message.

SELECT [MS Table].State, [MS Table].Type, [MS Table].Severity, [MS
Table].Target, [MS Table].Rel, Count([MS Table].Pr) AS CountOfPr
FROM [MS Table]
GROUP BY [MS Table].State, [MS Table].Type, [MS Table].Severity, [MS
Table].Target, [MS Table].Rel
HAVING ((([MS Table].State)<>"Built") AND (([MS Table].Type)="Bug")
AND (([MS Table].Target) Like "*6010*"))
ORDER BY [MS Table].Severity;

So that's not the SQL that's giving the error, then. What error
message?

Have you tried this:

SELECT [State], [Type], Severity, Target, Rel, Count(*) As N
FROM [MS Table]
GROUP BY [State], [Type], Severity, Target, Rel
HAVING
([State]<>"Built") AND
([Type]="Bug") AND
(Target Like "*6010*")
ORDER BY Severity;

 

[Guest]

Actually Jeff I am not positive what it is. In the workbook the format is
General. Does this mean it is a string and if it is what do I use for the
string instead of Count(*).

 

[Guest]

If they were zero-length strings wouldn't that mean they would be included in
the count? The fact that they are being excluded suggests they are Null. As
Dirk says COUNT(*) is needed here.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

When I try to input count(*) I get an error message.

What's the error message?

I'd move the criterion to the WHERE clause (which is applied before the
grouping) rather than the HAVING clause (applied after); and allow for the
possibility that State might be NULL. A not-equals clause will fail if the
field is null - NULL isn't equal to anything, but it's also not UNEQUAL to
anything.

SELECT [MS Table].State, [MS Table].Type, [MS Table].Severity, [MS
Table].Target, [MS Table].Rel, Count([MS Table].Pr) AS CountOfPr
FROM [MS Table]
GROUP BY [MS Table].State, [MS Table].Type, [MS Table].Severity, [MS
Table].Target, [MS Table].Rel
WHERE ((([MS Table].State)<>"Built" OR [MS Table].[State] IS NULL) AND (([MS
Table].Type)="Bug") AND (([MS Table].Target) Like "*6010*"))
ORDER BY [MS Table].Severity;


John W. Vinson [MVP]

 

[Guest]

As Dirk says COUNT(*) is needed here. Counting on a specific column will
exclude any Nulls in that column. As well as counting all rows it also makes
for better performance. Another point is that you have a HAVING clause
rather than a WHERE. The former operates on the data after grouping and is
usually used with aggregated values e.g. HAVING SUM(Sales) > 100000. To
simply restrict the query on the basis of values in the table's rows use the
latter:

SELECT [State], [Type], Severity, Target, Rel,
COUNT(*) AS CountOfRows
FROM [MS Table]
WHERE [State] <> "Built"
AND [Type] = "Bug"
AND Target Like "*6010*"
GROUP BY [State], [Type], Severity, Target, Rel
ORDER BY Severity;

To achieve this via the query designer rather than in SQL view you'd add the
State, Type and Target columns twice to the design grid and in one instance
of each select 'where' from the drop down list of the 'Total' row, and enter
values in the 'criteria' row of these 'where' columns. The COUNT(*) will
have to be entered in SQL view, however.

Ken Sheridan
Stafford, England