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.

 

[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.