Record count between 15 minute interval

[rajeevs]

I have a field in a table with field type as Date/Time and the format for the field is Short Time.
I need to get the count of records between 15 minutes interval.
Example :
Tbl Structure as
Fld1 as Text
Fld2 as Date/Time
Fld3
Fld4
etc

If the records are like below
rec1 00:12
rec2 00:14
rec3 00:16
rec4 00:32
rec5 00:31
rec6 00:45
I need to get the count of records between 00:00 to 00:15
00:15 to 00:30 like that for 24 hour period.
like a cross tab query result with column heading as
00:00-00:15 00:16-00:30
Pls Help
Thank you all

[Rabbit]

Convert the time into number of minutes, subtract 1, integer division by 15, and that will give you the 15-minute-index.

[rajeevs]

Thank you for the quick response. I am breaking my head to create the criteria you mentioned. Can you pls explain how to put the criteria and one more thing I would like to clarify that the result I am looking for is the count between the time period and i don't want to round the time to nearest 15 minutes to find the count.
Thank you once again

[Rabbit]

Int(([Number of Minutes] - 1) / 15)

That gives you the 15-minute-index that the time falls under.

1 minute to 15 minutes will be index 0
16 to 30 = 1
31 to 45 = 2
46 to 60 = 3
61 to 75 = 4

Group by this expression and it will do the count by 15 minute intervals.

[rajeevs]

Thank you so much Rabbit. It worked perfect. Only a small (very small) problem is that after grouping I need to convert the column heading manually to look like
00:00 - 00:15 00:16 - 00:30.
Once again thank you & Bytes team

[Rabbit]

No problem, good luck.

The (index + 1) times 15 will give you the end of the range. If you subtract 14 from the end of the range, it will give you the start of the range.

[rajeevs]

Thank you once again