 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / January 2006Query to Count, but return items that are null
Hello - Didn't know how to describe this in the subject, but I need a query that counts the number of items in certain categories, but if that category is blank, I want it to return a zero. I need it for a report. All I seem to be able to get is the count of records that are greater than zero, and I need a listing of all categories regardless of whether they're zero (or null), along with how many in each category. Any suggestions will be appreciated!  SignatureSandy >Didn't know how to describe this in the subject, but I need a query that >counts the number of items in certain categories, but if that category is [quoted text clipped - 3 lines] >zero, and I need a listing of all categories regardless of whether they're >zero (or null), along with how many in each category. You will need a table that has a record for each possible category. Then you can use an outer join to the data you are counting. SignatureMarsh MVP [MS Access] Thanks for responding, Barton. I used the following query: SELECT tblDeclaration.Declaration, Count(*) AS NumOfDecl FROM tblDeclaration Left JOIN tblCalls ON tblDeclaration.DeclarationID = tblCalls.DeclarationID GROUP BY tblDeclaration.Declaration; It still isn't returning any rows that are less than 1. Any idea what I'm doing wrong? Also, I have to manage to use another column from tblCalls - CallDate, in addition to getting Count, because I need the count of each Declaration between two dates. [By the way, tblCalls is the Main table.] What am I doing wrong in the above query? SignatureSandy > >Didn't know how to describe this in the subject, but I need a query that > >counts the number of items in certain categories, but if that category is [quoted text clipped - 7 lines] > category. Then you can use an outer join to the data you > are counting. >I used the following query: > [quoted text clipped - 7 lines] >CallDate, in addition to getting Count, because I need the count of each >Declaration between two dates. [By the way, tblCalls is the Main table.] I think the issue is that you are using Count(*). That will count all the rows, regardless of their content. Try changing it to: . . ., Count(tblCalls.DeclarationID) AS NumOfDecl SignatureMarsh MVP [MS Access] Thanks, Marshall! SignatureSandy > >I used the following query: > > [quoted text clipped - 12 lines] > changing it to: > . . ., Count(tblCalls.DeclarationID) AS NumOfDecl I just tried the following - SELECT tblDeclaration.Declaration, Count(*) AS NumOfDecl FROM qryOne RIGHT JOIN tblDeclaration ON qryOne.DeclarationID = tblDeclaration.DeclarationID GROUP BY tblDeclaration.Declaration qryOne is a regular join between tblCalls and tblDeclaration. I ran a query using tomorrow's date, just to see if it would return 23 rows, all with 0 and it returned 23 rows, but each with a 1 as the NumOfDecl. [There are no entries yet for tomorrow.] What could be causing that? SignatureSandy > >Didn't know how to describe this in the subject, but I need a query that > >counts the number of items in certain categories, but if that category is [quoted text clipped - 7 lines] > category. Then you can use an outer join to the data you > are counting. Count counts all the non-null values. Therefore Count(*) will return a count of the total number of aggregated rows. Since you are forcing a row to be returned, the count will be 1 Try Count(tblDeclaration.DeclarationID) since that value can be null >I just tried the following - > [quoted text clipped - 24 lines] >> category. Then you can use an outer join to the data you >> are counting. Thanks, John! SignatureSandy > Count counts all the non-null values. > Therefore Count(*) will return a count of the total number of aggregated [quoted text clipped - 30 lines] > >> category. Then you can use an outer join to the data you > >> are counting. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.