Results 1 to 9 of 9
  1. #1
    NikoTheBowHunter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    58

    How to get a query to return results for null/0 value?

    I need some help getting a query to return results even if the search finds nothing. For example, my query is set up as this:



    Click image for larger version. 

Name:	query.jpg 
Views:	32 
Size:	43.2 KB 
ID:	30007

    Which returns results for any status set as Issue. The problem is that if a region doesn't have any records in issue status, they don't show up.

    Click image for larger version. 

Name:	results.jpg 
Views:	33 
Size:	14.5 KB 
ID:	30008

    As seen in the above picture, regions 4 and 5 are missing due to not having any records in the "issue" status. Is there a way to set up the query so all the regions show up regardless of the count?


    Thanks,
    Niko

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe in box under Issue, try Is Null

  3. #3
    NikoTheBowHunter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    58
    Didn't work, tried it under the "Issue" box and also tried creating another column like the "Issue" column but used Is Null, neither worked.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You want to create a table or query that lists out all of your regions individually.
    Then, create a Left Join query from this first table/query to your Inventory table, joining on the Reg field.
    Then, when returning the Inventory, use a calculated field using the NZ function to return 0 where they are not found.

    So let's say that we have a table named Regions with all the different regions in it.
    And the count query you show above has been named "qryInventory". Then we just join those two objects and add a calculated field like this (SQL View):
    Code:
    SELECT Regions.Reg, Nz([CountOfStatus],0)+0 AS TotalCount
    FROM Regions LEFT JOIN qryInventory ON Regions.Reg = qryInventory.Reg;
    Which will return a result that looks something like this:
    Code:
    Reg	TotalCount
    1	3
    2	0
    3	1
    4	0
    5	1
    6	0
    7	1
    MC	0

  5. #5
    NikoTheBowHunter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    58
    Interesting, never done something like this. I'm out of here for the weekend so I will give this a try when I return on Monday. Thank you for the suggestion! cheers~!

  6. #6
    NikoTheBowHunter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    58
    I've tried the above and don't thinking I'm doing it correctly as I cant get anything to come out like your example.

    I created a table and named it Regions - Single column with values of 1-8
    I have renamed my query to qryInventory

    Guess I cant figure out what you mean by joining the two. I tried creating a blank query and then went to SQL view and added your above code, gives me an error/parameter pop up box.

    I tried to edit qryInventory via the SQL view, but that just removes my parameters that were originally set, so doubt its meant to be done that way. Where am I going wrong?

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    The table you want to count statuses from is Inventory?

    You added a table Regions? (Btw. when you added it only becayse this query, then there was no need for this - you were adviced to create a saved query like qryRegions: SELECT DISTINCT reg FROM Inventory)

    The query to count "issue"'s will be (sorry, but I can't test it now):
    SELECT reg.reg, COUNt(reg.reg) AS IssueCnt FROM (Region reg LEFT JOIN Inventory inv ON inv.reg = reg.reg AND inv.Status = "Issue" ) GROUP BY reg.reg
    or
    SELECT reg.reg, COUNt(reg.reg) AS IssueCnt FROM (qryRegion reg LEFT JOIN Inventory inv ON inv.reg = reg.reg AND inv.Status = "Issue" ) GROUP BY reg.reg

    Update: replaced inv.reg with reg.reg in COUNT to avoid cases with Null values.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I've tried the above and don't thinking I'm doing it correctly as I cant get anything to come out like your example.
    I am uploading a sample database so you can see how it is done.
    Attached Files Attached Files

  9. #9
    NikoTheBowHunter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    58
    Started new thread as this one was more or less solved.
    Attached Thumbnails Attached Thumbnails qry.jpg   qryView.jpg  

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-04-2017, 02:50 AM
  2. Query to return blank or null items.
    By mulefeathers in forum Queries
    Replies: 3
    Last Post: 10-24-2013, 12:45 PM
  3. Union Query to return Null
    By dr4ke in forum Queries
    Replies: 8
    Last Post: 07-06-2012, 12:18 AM
  4. Count query return zero instead of null/blank
    By dhicks19 in forum Queries
    Replies: 1
    Last Post: 04-19-2012, 07:52 AM
  5. Query return 0 for null values
    By rachello89 in forum Access
    Replies: 4
    Last Post: 02-23-2012, 08:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums