I'm going to go out on a limb and actually disagree with MMcDonal.
That is, I *THINK* that all you want is this:
Code:
SELECT DATEADD( 'n',
5 * ( DATEDIFF('n',#08:00#,TimeValue(yourTimeField)) \ 5 ),
#08:00# ) AS FiveMinuteStart,
SUM(someFieldToBeAdded) AS Total,
COUNT(*) AS HowMany
FROM yourTable
WHERE TimeValue(yourTimeField) BETWEEN #08:00# AND #19:59:59#
GROUP BY DATEADD( 'n',
5 * ( DATEDIFF('n',#08:00#,TimeValue(yourTimeField)) \ 5 ),
#08:00# )
ORDER BY 1
By the by, I used TIMEVALUE() there to ensure this worked even if
yourTimeField contains both date and time.
Now...
That code assumes that you only want *aggregate* values for each reporting period. That is SUM()s and/or COUNT()s [or other aggregates, such as AVG(), MIN(), MAX()] for data during the period.
If you simply meant that you wanted to break the report at 5 minute intervals, then that's a simpler task. But I still would not perform one query per 5 minute chunk, as MMcDonal suggested.
(And by the by...since this is Access, clearly you would *NOT* use
WHERE [TimeField] BETWEEN 'Start Time' and 'End Time'
as MMcDonal suggested. You'd need to use
WHERE [TimeField] BETWEEN #Start Time# and #End Time#
similar to what I showed, above.
**************
But I guess the fundamental question here is: Just WHAT are you reporting?? Do you need just aggregates, as I show? Or do you need to show full data, just with breaks at each 5 minute interval?
FINAL COMMENT: No matter which you need, *IF* your table happens to have *NO DATA* during *ANY* 5 minute period, AND if you want to so indicate in your report (that is, show the time but then no data), THEN indeed you have to resort to MMcDonal's scheme *OR* (much much more efficiently) add a separate table of 5 minute periods to your DB. That's easy to do and will produce much more efficient overall performance.
Reporting on Time intervals
