Solved

Format SUM function

Posted on 2013-01-09
4
514 Views
Last Modified: 2013-01-09
Greetings,

I have a database that has a field which contains seconds.  I am doing a Group By and using SUM on the seconds field.  I need to format the SUM results into HH:MM:SS.

Select Sum([Field1]),
Sum([Seconds]) AS MyDuration
Group By [Field3]
Where [Field4] = 'ABC'

MyDuration I need in HH:MM:SS

Thanks so much
0
Comment
Question by:jchauncey60
  • 2
4 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 38760782
Try this:


SELECT q.SumOfField1, Format(Int(q.[MyDuration]/3600),"00") & ":" & Format(Int((q.[MyDuration]-(Int(q.[MyDuration]/3600)*3600))/60),"00") & ":" & Format(((q.[MyDuration] Mod 60)),"00") AS ElapsedTime
FROM
(
Sum([Field1]) AS SumOFField1,
Sum([Seconds]) AS MyDuration
Group By [Field3] 
Where [Field4] = 'ABC'
) q

Open in new window

0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 38760804
If that value (Sum([Seconds]) is not going to exceed 24 hours, you could also do:

Format(Sum([Seconds])/86400, "hh:nn:ss")

dividing the sum by 86400 (the number of seconds in a day) will give you the decimal equivalent of a time value as stored in the database.
0
 

Author Comment

by:jchauncey60
ID: 38760824
Solution #2 works for this problem.  Solution #1 is the best for generic applications. Thanks for the quick response.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38760954
Glad to help out
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

861 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now