asked on

MS Access summing on 8-hours

I have a data table with a time stamp (general date format: 1/7/2013 8:49:04 AM) which has a data point for every minute.

I need a sum query to sum on 8-hour periods.

hgj1357

ASKER

This is how I sum on a daily resolution:

SELECT Format([Date-Time],"yyyy-mm-dd") AS MyTIme, Sum(T_41s.Flow) AS SumOfFlow
FROM T_41s
GROUP BY Format([Date-Time],"yyyy-mm-dd");

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

If you add another table (tbl_Times) to your database, with fields (Title, StartTime, EndTime) and values:

Title StartTime EndTime
00-08 #00:00:00# #08:00:00#
08-16 #08:00:00# #16:00:00#
16-24 #16:00:00# #24:00:00#

Then you could do something like:

SELECT Format([Date-Time], "yyyy-mm-dd") & " " & T.Title as Period
SUM(T_41s.Flow) as SumOfFlow
FROM T_41s
INNER JOIN tbl_Times as T
ON TimeValue(T_41s.[Date-Time]) >= T.StartTime
AND TimeValue(T_41s.[Date-Time]) < T.EndTime
GROUP BY Format([Date-Time], "yyyy-mm-dd") & " " & T.Title

hgj1357

ASKER

Does the job. Thanks

Dale Fye

If you want to show all of the time ranges for each date, even if no data exists for that range. Then you could modify my SQL above Like:

SELECT Format([Date-Time], "yyyy-mm-dd") & " " & T.Title as Period
SUM(iif(TimeValue([Date-Time]) >= T.StartTime AND
TimeValue([Date-Time]) < T.EndTime, T_41s.Flow, 0)) as SumOfFlow
FROM T_41s, tbl_Times as T
GROUP BY Format([Date-Time], "yyyy-mm-dd") & " " & T.Title