Time comparison query

  • Thread starter Thread starter red skelton via AccessMonster.com
  • Start date Start date
R

red skelton via AccessMonster.com

Hi,
Wondering if anyone can help me as I think I'm going mad but this is what I'm
trying to do with a query. I have two times, one a recall time (a specific
time when someone was told to come to work) , and the other time, report time
(when they actually arrived). What Ive been trying to do, without success is
run a query at the 60,90 and 180 minute points, to see how many people have
arrived but having no luck. Both times are stored in a table called
"tblRecall2" Any suggestions will be greatly appreciated.
VR,
Red
 
Datediff should do it. I think that's what you are asking.

SELECT Count(*) FROM tblRecall2 WHERE DateDiff("n", [RecallTime],
[ReportTime]) < 60
SELECT Count(*) FROM tblRecall2 WHERE DateDiff("n", [RecallTime],
[ReportTime]) BETWEEN 61 AND 90
etc...

You can also put all of this in one query by using IID
SELECT Sum(IIF(DateDiff("n", [RecallTime], [ReportTime]) < 60, 0 , 1))
AS Below60, Sum(IIF(DateDiff("n", [RecallTime], [ReportTime]) > 60 AND
DateDiff("n", [RecallTime], [ReportTime]) <=90 , 0 , 1)) AS
Between60And90, etc.

I assume you have multiple days in this table, so you'll have to add to
your where clause something to restrict the count to today like
RecallDate = Now()

Ray D'Andrade
http://www.raydandrade.com
 
Thanks for the idea. Will give it a try and let you know how I get along.
Its appreciated.
Red
 
Sir,
Many Thanks your Query worked a treat. Thanks again,
Red
Datediff should do it. I think that's what you are asking.

SELECT Count(*) FROM tblRecall2 WHERE DateDiff("n", [RecallTime],
[ReportTime]) < 60
SELECT Count(*) FROM tblRecall2 WHERE DateDiff("n", [RecallTime],
[ReportTime]) BETWEEN 61 AND 90
etc...

You can also put all of this in one query by using IID
SELECT Sum(IIF(DateDiff("n", [RecallTime], [ReportTime]) < 60, 0 , 1))
AS Below60, Sum(IIF(DateDiff("n", [RecallTime], [ReportTime]) > 60 AND
DateDiff("n", [RecallTime], [ReportTime]) <=90 , 0 , 1)) AS
Between60And90, etc.

I assume you have multiple days in this table, so you'll have to add to
your where clause something to restrict the count to today like
RecallDate = Now()

Ray D'Andrade
http://www.raydandrade.com
Click to expand...
 
Back
Top