Trying to return zeroes in between statement

  • Thread starter Thread starter alphamisanthrope
  • Start date Start date
A

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?
 
alphamisanthrope said:
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.
Click to expand...


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
 
Thanks for your help, but there's a problem,

That statement put a 1 when a 0 should be.
 
alphamisanthrope said:
Thanks for your help, but there's a problem,

That statement put a 1 when a 0 should be.
Click to expand...


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

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

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

Thanks again.
Click to expand...


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 ;-)
 
Back
Top