Including Null Values in Count Query

[Matty]

I'm trying to run a query that counts the number of clients for Bill Reference Code, and all that is grouped by HOTID. All my counted numbers are good, except I want to also show the records that have zeroes as well. I'm making a couple other queries like this, and they all have to be set up the same, with all the HOTIDs listed, and all the Bill Reference Codes listed underneath each HOTID. my sql looks like this:

SELECT CLIENT.HOTID, [Bill Ref Codes].[Bill Ref Code] AS bref, Count(*) AS CountOfclientid
FROM [Bill Ref Codes] INNER JOIN ([true complete client list no bill ref - start] INNER JOIN CLIENT ON [true complete client list no bill ref - start].clientid = CLIENT.CLIENTID) ON [Bill Ref Codes].[Bill Ref Code] = [true complete client list no bill ref - start].bref
GROUP BY CLIENT.HOTID, [Bill Ref Codes].[Bill Ref Code]
HAVING ((([Bill Ref Codes].[Bill Ref Code])<>"S-01" And ([Bill Ref Codes].[Bill Ref Code])<>"X-88" And ([Bill Ref Codes].[Bill Ref Code])<>"X-99"))
ORDER BY CLIENT.HOTID, [Bill Ref Codes].[Bill Ref Code];


I dont know much about SQL (I made this in query builder), but any help would be greatly appreciated. I'm thinking it has something to do with the joins, but i have no clue...

 

[willem]

There is a difference between null and 0.
Null is an unknown value or unavailable input. Five +null = null.
Null propagates. The statistical functions in SQL exclude the null values.

 

[Pat Hartman]

The query as it is written will never return any row with a null value in [Bill Ref Code]. There are two things preventing this.
1. [Bill Ref Code] is used to join to another table. Null values never match other null values. The test - If A = B - will be false if both (or either) A and B contain nulls. The only true result is obtained by using the IsNull() function or the Is Null condition in SQL.
2. Following the same logic a test for X <> "A" will also not return any rows containing nulls because nulls were not specificly tested for.

So, rebuild the query eliminating the join to the [Bill Ref Codes] table and also eliminate the value tests or include a positive test for null values in [Bill Ref Code]. You'll get a longer list than you want, but it will include the null values.

Also, for future reference and because I just can't pass up an opportunity to lecture

, it is poor practice to include spaces and other special characters in object names.

 

[willsaunders]

Haven'e read your question through properly but I have read the replies. Have you thought about using the Nz function to convert null's to zero length strings or another value of your choice. A text value maybe able to be compared and counted?

Let me know how you get on,

Will.