MS Access summing on 8-hours

Posted on 2013-01-07
Last Modified: 2013-01-07
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.
Question by:hgj1357
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Author Comment

ID: 38751171
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");
LVL 77

Accepted Solution

peter57r earned 500 total points
ID: 38751233
SELECT Format([Date-Time],"yyyy-mm-dd") AS MyTIme,iif(hour([Date-Time])<8, 1,iif(Hour([Date-Time])<16,2,3)) as MyPeriod, Sum(T_41s.Flow) AS SumOfFlow
FROM T_41s
GROUP BY Format([Date-Time],"yyyy-mm-dd"),iif(hour([Date-Time])<8, 1,iif(Hour([Date-Time])<16,2,3))
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38751255
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

Author Closing Comment

ID: 38751278
Does the job. Thanks
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38751297
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question