Thursday, January 24, 2008

Grouping records in a query by 15 minute intervals

I recently posted on Grouping records on date/time values in an Access report. But what if you want to group on time in a query? Here's an expression you could use in a query for grouping records into fifteen minute intervals. It uses DatePart() to get the minutes part of the time and then uses Int() and multiplication to cut off to the nearest 15 minutes. Then it uses DatePart again to get the hours part and sticks them together.

MyInterval: Format(DatePart("h",[MyTime]),"00") & ":" & Format(Int(DatePart("n",[MyTime])/15)*15,"00")


zai said...

How would one use this in a regular Access SQL query (i.e. no Report)?

Stephen said...

Zai- this is regular SQL. The other post was for reports.

zai said...

Ah, I see... Can I ask a stupid question, then... How would I apply this to my csv-formatted data?

zai said...

I've solved my problem like this. Maybe it will help someone.

-set up MS SQL Server on the machine
-use the free program WinSQL
-import the CSV data and run a query like the one below.

SELECT DATEADD(hour,DATEDIFF(hour,0,[date_field]),0) AS HourlyAverage,
AVG([field1]) AS [field1avg],
FROM [table_name]
GROUP BY DATEADD(hour,DATEDIFF(hour,0,[date_field]),0)
ORDER BY HourlyAverage;