My query is not giving me the right count with try to total columns | Member | | Join Date: Aug 2009
Posts: 46
| | |
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.
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 253
| | | re: My query is not giving me the right count with try to total columns
Quote:
Originally Posted by stateemk  This is really hard to explain without showing, so let me know if you need me to explain further. Could you please post the code in your query so we can actually see what you are doing?
-AJ
| | Member | | Join Date: Aug 2009
Posts: 46
| | | re: My query is not giving me the right count with try to total columns
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];
| | Member | | Join Date: Aug 2009
Posts: 46
| | | re: My query is not giving me the right count with try to total columns
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.
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 253
| | | re: My query is not giving me the right count with try to total columns
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
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: My query is not giving me the right count with try to total columns
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.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
