Access 2013 query has blanks when I need to display a zero

[jpreator]

I have a query that is counting the number of records that fall between two dates as specified in a form using two combo boxes. The query is grouped by two categories, value stream and department. I need to return a count for each department. It works great if there is a record, but I need to show zero if there are no records. I'm combining this with two other similar queries to make a report which uses the values to calculate another value so I need zeroes not blanks. Here is the sql:

SELECT Count(qKaizen.Rejection) AS CountOfRejection, qKaizen.Department, qKaizen.ValueStream
 
FROM qDepartments LEFT JOIN qKaizen ON qDepartments.DeptName = qKaizen.Department
 
WHERE (((qKaizen.Rejection) Between Nz([Forms]![fKaizenTurns]![tStartDate],#4/1/2015#) And Nz([Forms]![fKaizenTurns]![tEndDate],Date()+1)))
 
GROUP BY qKaizen.Department, qKaizen.ValueStream;

Please help!

[zmbd]

would you mind posting a generic set of results, say, two records?
Use the [CODE/] button to enclose your table and spaces to format the columns (not the tab key, the tabs won't stay :) )

My first thought here is a UNION clause pulling DISTINCT 0,null,null; however, I'm not sure of the formatting and what you would most likely see in the query for a zero return is

[CountOfRejection][Department][ValueStream]
[      0         ][    ?     ][     ?     ]

Which is not what I suspect you are after.

The vba function NZ() in the SQL can slow things down on large records... you might consider:
iif([Forms]![fKaizenTurns]![tStartDate] Is Null,#4/1/2015#,[Forms]![fKaizenTurns]![tStartDate])

another consideration is the use of the Between, you can miss some data depending how the data set is entered. you should consider using the inequality:
>=[startdate] < [enddate]+1[

This has cause me some grief in the past because our lab is a 7d/24h operation and the lab results have a date+time stamp and the form I was using at the time to pull reports didn't take that into consideration.. . really, one would think that the Between would pull from midnight to midnight for the indicated dates :( . (MVP Allen Browne shows an example of such a construct in the first half here: Limiting a Report to a Date Range I don't see any reason to use the huge function in the second 1/2 of the post for your query.)

[jpreator]

If I understood what you were asking here is an example of how the report turns out. It leaves blanks and so I cannot get the turns calculation to calculate.

[Value Stream] [DeptName] [Rej][Comp]/[Sub]=[Turns]
[Centerfire  ] [ CFAA   ] [   ][  1 ]/[ 2 ]=[ 0.5 ]
[            ] [ CFMP   ] [ 3 ][ 14 ]/[ 3 ]=[ 4.7 ]
[            ] [ Plating] [   ][  1 ]/[   ]=[     ]
 

[jforbes]

I think the following should work for you:

SELECT NZ(Count(qKaizen.Rejection), 0) AS CountOfRejection, qKaizen.Department, qKaizen.ValueStream

[jpreator]

Thank you both for your replies, that seems to have done the trick.

[zmbd]

J:
I had thought of that ( NZ(Count(qKaize n.Rejection), 0) ) however, I've had that fail on me a few times when I use a named field and only sometimes works with NZ(Count(*), 0).

Funny thing... I just went back in to one of my production databases to see how I was handling a similar situation (this is live SQL in one of my databases, one nice thing about the normalized data, nothing but generic numbers!):

SELECT tbl_batch.batcher_ID
   , tbl_batch.batch_point
   , Count(tbl_batch.Date_tested) 
AS CountOfDate_tested
FROM tbl_batch
GROUP BY tbl_batch.batcher_ID
   , tbl_batch.batch_point;
 

Part of the actual data set (This is actually a temp table that is used later in a crosstab - inherited this database nightmare):

PK      batcher_ID      batch_point     Date_tested
971      1               1               10/26/2015
972      1               2    
973      1               3               10/26/2015
989      1               4               10/27/2015
990      1               4               10/28/2015
991      1               4               10/30/2015
987      2               1    
998      2               2    
999      2               3    
1010     2               3    
1511     2               3    
1512     3               5    
1513     3               5    
1514     3               5    
1515     3               4    
1516     3               5

However, I use the query to determine pending batches so this is my actual return...

batcher_ID     batch_point     CountOfDate_tested
1               1               1
1               2               0
1               3               1
1               4               3
2               1               0
2               2               0
2               3               0
3               4               0
3               5               0

Now when I put a conditional to search for test results between two dates then I get tend to get the null record row and I have code to check for no record in the report.

I've gone in and check two of my other databases and they have this same construct and results...

Could it be the join in OP's post that is causing the issue?

[jforbes]

Z,

In my experience, it's best not to Count against a column that can contain Nulls, because nulls wont be included in the count. Also the * operator can cause extra columns to be returned, slowing things down, not always, but it can. So experience has taught to count the primary key whenever possible.

I'm not sure what the difference is between what you and jpreator are doing, but I would imagine it has something to do with SQL initializing the variable for the Count.

...Again, experience has guided me towards counting a PK, I've forgotten the specific cases, but I imagine something like this has happened to me before. =)

[zmbd]

...Again, experience has guided me towards counting a PK, I've forgotten the specific cases, but I imagine something like this has happened to me before. =)

Good point about using the primary key for the count. In my case I needed both missing and entered dates... if it had returned a null instead of a number it wouldn't have been an issue for me.

but I would imagine it has something to do with SQL initializing the variable for the Count.

It would be great to know what was happening here instead of what might be a fluke that could break my databases later :-( !
That may be a topic for a new thread; however, if we can figure this out, it might be the solution to jpreator question?!

Also the * operator can cause extra columns to be returned

The asterisk... unless on a very limited table/query - I too avoid it like the plague that it can be :)