My query is not giving me the right count with try to total columns

[stateemk]

I am trying to run two queries. One query is doing a count to figure out how many total sales there were each year based on the assessor. That query is working fine. On the other query, I'm trying to get a count of the property type grouped together by year and assessor. The problem with this query is that somtimes the property type is blank and that's okay, but the count is not counting the blanks, so my numbers aren't matching up in the two queries. For instance, say I've got 5 sales in 2005 that do not have a property type listed. The query will pick up that there is a blank property type, but in the count column, it has zero instead of 5. This is really hard to explain without showing, so let me know if you need me to explain further. I'm at a loss on how to get my numbers to match up.

[ajalwaysus]

@stateemk
Could you please post the code in your query so we can actually see what you are doing?

-AJ

[stateemk]

Here's the query that is not giving me the right count. When the "Property Type" is null, it will show zero in the Count column.

SELECT [Residential Sale].[County Link], [Residential Sale].[Sale Year], [Residential Sale].[Property Type], [Residential Sale].[Source Assessor], Count([Residential Sale].[Property Type]) AS [CountOfProperty Type]
FROM [Residential Sale]
GROUP BY [Residential Sale].[County Link], [Residential Sale].[Sale Year], [Residential Sale].[Property Type], [Residential Sale].[Source Assessor]
HAVING ((([Residential Sale].[County Link]) Is Not Null) AND (([Residential Sale].[Property Type])="ml" Or ([Residential Sale].[Property Type])="ms" Or ([Residential Sale].[Property Type])="sf" Or ([Residential Sale].[Property Type])="va" Or ([Residential Sale].[Property Type])="vl" Or ([Residential Sale].[Property Type]) Is Null) AND (([Residential Sale].[Source Assessor])="assessor" Or ([Residential Sale].[Source Assessor])="appraiser" Or ([Residential Sale].[Source Assessor])="mls" Or ([Residential Sale].[Source Assessor])="buyer" Or ([Residential Sale].[Source Assessor])="seller" Or ([Residential Sale].[Source Assessor]) Is Null))
ORDER BY [Residential Sale].[County Link], [Residential Sale].[Sale Year] DESC , [Residential Sale].[Property Type], [Residential Sale].[Source Assessor];

[stateemk]

Sorry, I meant to include this in my previous post. When the "Property Type" is null, it will show zero in the CountOf Property Type column even though there are one or more records with a null property type.

[ajalwaysus]

You don't have to count on the [Property Type] field, if you have an ID field or something like that, that you know will NEVER be null, then your count will be correct. The count function counts the rows regardless on what it is on as long as it's never null.

Let me know if this works,
-AJ

[NeoPa]

When wishing to count all rows, as opposed to counting the number of occurrences of a particular field across all the rows, you should use the aterisk (*).

Your first line would then be (formatted so that it can be read) :

SELECT [Residential Sale].[County Link],
       [Residential Sale].[Sale Year],
       [Residential Sale].[Property Type],
       [Residential Sale].[Source Assessor],
       Count(*) AS [CountOfProperty Type]

I wouldn't use the name [CountOfProperty Type] either, as that doesn't mean not what you want.