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")
4 comments:
How would one use this in a regular Access SQL query (i.e. no Report)?
Zai- this is regular SQL. The other post was for reports.
Ah, I see... Can I ask a stupid question, then... How would I apply this to my csv-formatted data?
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;
Post a Comment