no zeroes returned

[alphamisanthrope]

Firstly, here's the query:
SELECT tblTime.Hour, Count(*) AS HowMany
FROM tblStay, tblTime
WHERE tblTime.Hour Between [tblStay].[TimeIn] And [tblStay].[TimeOut]
GROUP BY tblTime.Hour;

tblTime is just a list of every hour
tblStay has two columns timeIn, timeOut

The query works, I get a head count by hour, but I can't get zeroes to
return when Null. The hour is question is skipped. I tried Nz in every
conceivable place. I've tried a couple of if statements, but no luck

See what you think.

 

[Guest]

Save your "Firstly" query and then use it in a new query that does an outer
join on tblTime.

 

[Marshall Barton]

Firstly, here's the query:
SELECT tblTime.Hour, Count(*) AS HowMany
FROM tblStay, tblTime
WHERE tblTime.Hour Between [tblStay].[TimeIn] And [tblStay].[TimeOut]
GROUP BY tblTime.Hour;

tblTime is just a list of every hour
tblStay has two columns timeIn, timeOut

The query works, I get a head count by hour, but I can't get zeroes to
return when Null. The hour is question is skipped. I tried Nz in every
conceivable place. I've tried a couple of if statements, but no luck

See what you think.

Please don't post the same question multiple times. Check
for other answers in other groups.