Trying to return zeroes in between statement

[alphamisanthrope]

Please help, y'all.

Here's my statement:
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 each hour of the day
tblStay has two columns, timeIn, timeOut

I, for the life of me, cannot get a zero to return. I've tried Nz all
kinds of ways and can't get it to go.

Any ideas?

 

[Marshall Barton]

Here's my statement:
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 each hour of the day
tblStay has two columns, timeIn, timeOut

I, for the life of me, cannot get a zero to return. I've tried Nz all
kinds of ways and can't get it to go.

Try using:

SELECT tblTime.Hour, Count(*) AS HowMany
FROM tblTime LEFT JOIN tblStay
ON tblTime.Hour >= [tblStay].[TimeIn]
And tblTime.Hour <= [tblStay].[TimeOut]
GROUP BY tblTime.Hour

 

[alphamisanthrope]

Thanks for your help, but there's a problem,

That statement put a 1 when a 0 should be.

 

[Marshall Barton]

Thanks for your help, but there's a problem,

That statement put a 1 when a 0 should be.

Arrggghhhh. The count was supposed to be
Count(tblStay.TimeIn)

 

[alphamisanthrope]

Arrggghhhh. The count was supposed to be
Count(tblStay.TimeIn)

That did it! Bless you. Now I just need some Rogaine for all the hair
I've pulled out.

Thanks again.

 

[Marshall Barton]

That did it! Bless you. Now I just need some Rogaine for all the hair
I've pulled out.

Thanks again.

You're welcome.

Do you really think Rogaine will do the job? If it will,
I'll have to run out and buy a couple of palettes of the
stuff ;-)