?
Solved

MS Access summing on 8-hours

Posted on 2013-01-07
5
Medium Priority
?
332 Views
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.
0
Comment
Question by:hgj1357
[X]
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
5 Comments
 
LVL 2

Author Comment

by:hgj1357
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");
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 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))
0
 
LVL 48

Expert Comment

by:Dale Fye
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
0
 
LVL 2

Author Closing Comment

by:hgj1357
ID: 38751278
Does the job. Thanks
0
 
LVL 48

Expert Comment

by:Dale Fye
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

765 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