Solved

Format SUM function

Posted on 2013-01-09
4
517 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 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