Looping Through Time

[Randy]

Hello NG

Help - I have a table of records timestamped. I want to return a count
of records but the count of records I want is the most records in a 1 hour
span. how do I write a Query that spans increments of time. Roughly I want
to do something like this

Dim db as database
dim rs as recordset
dim varCount as Long
Dim x as Long???DATE???
Dim y as Long
Dim z as String

Set db = CurrentDb
varCount = 0
y = 60 Increment of Span
z = "n" for Minutes (Or "s" Seconds Or "h" Hours)

For x = 12:00:00 AM to 11:59:59 PM Step 1 Minute
set rs = db.openrecordset (SELECT Count(Records) As RecCount FROM
RecordTable WHERE RecordTime Between x And DateAdd(z,y,x))
If rs!RecCount>varCount Then varCount = rs!RecCount
Next x

varCount = Greatest number of records in y(z) timeframe for day

How can this be done???

TIAFAH
Randy

 

[Wolfgang Kais]

Hello Randy.

Help - I have a table of records timestamped. I want to return
a count of records but the count of records I want is the most
records in a 1 hour span. how do I write a Query that spans
increments of time. Roughly I want to do something like this

Dim db as database
dim rs as recordset
dim varCount as Long
Dim x as Long???DATE???
Dim y as Long
Dim z as String

Set db = CurrentDb
varCount = 0
y = 60 Increment of Span
z = "n" for Minutes (Or "s" Seconds Or "h" Hours)

For x = 12:00:00 AM to 11:59:59 PM Step 1 Minute
set rs = db.openrecordset (SELECT Count(Records) As RecCount FROM
RecordTable WHERE RecordTime Between x And DateAdd(z,y,x))
If rs!RecCount>varCount Then varCount = rs!RecCount
Next x

varCount = Greatest number of records in y(z) timeframe for day

How can this be done???

In a query, you could calculate a field value that can be used for
grouping like Format$(RecordTime,"dddddd hh:nn") for a minute
grouping. For each such group, calculate the count of records
(count(*)). Sort the results descending by the count.