Count events in several time frames

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table, AfterHours, which is used by staff to log events that happen.
How can I go about creating a query that will count the number of events
that happen between 5 and 5:29, 5:30 and 6, and so on until 7? The time in
which an event is logged in the Time field.

Thanks.
 
Select Count(EventID) From tblEvents WHERE EventDateTime Between #12/12/04
5:00:00am# and #12/12/04 5:29:59am#
 
Hi,


Int( 48 * DateTimeStamp) MOD 48


returns a value from 0 to 47 accordingly to the (n+1)-th half hour the data
time stamp event occurred. So, for an event occurring between 12:00 and
12:30, the expression will return 24 (since it is the 25th period of half
hour of a day).


SELECT COUNT(*), Int( 48 * DateTimeStamp) MOD 48 As period
FROM myTable
GROUP BY Int( 48 * DateTimeStamp) MOD 48


You can try to add

CDate( (Int( 48 * DateTimeStamp) MOD 48 ) / 48.0 ) As periodAsTime


to get the period not as a number form 0 to 47, but as a "time".


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top