# MS Access Query Question - Time Chart

How do I calculate total time?

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
have you tried using the Sum() function in a query?
Commented:
It's probably going to be a little more complicated than that.

It appears that the elapsed time is stored as a Date data type.  The Date data type represents a point in time, NOT elapsed time.  So if your sum ends up as more than 24 hours, you end up with a date value.  So, the result could look like:

12/31/1899 04:30 AM

if your total is 28.5 hours.

OR

1/11/1900 04:30 AM

if your total is 52.5 hours.

You would need to take the days part of the Date field and multiply them by 24.  Just remember, you can't put them back into a Date data type or you'll end up with a date again.

So, your choice is to change the data type to integer or long integer and store minutes which are easy to convert to hours and sum or you can  define the data as the Currency data type (but display as standerd not currency) and then you would be storing hours.fraction  OR you could define two columns and store hours in 1 and minutes in the other.  This third option will be easiest to use for data entry because you can use two text boxes with a label containing a colon between them so it looks like a time field.

Let us know what you want to do and we'll help with the expression you need.
Commented:
Sum([Time to Abandon) * 86400 will be the total number of seconds
CIOCommented:
Sum the values in a query:

``````Select Sum([Time to Abandon]) From Table1
``````
If the sum may exceed one day, use the function below:

``````Select FormatHourMinuteSecond(Sum([Time to Abandon])) From Table1
``````

``````Public Function FormatHourMinuteSecond( _
ByVal datTime As Date, _
Optional ByVal strSeparator As String = ":") _
As String

' Returns count of days, hours, minutes, and seconds of datTime
' converted to hours, minutes, and seconds as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03:55# + #20:01:24#
'   returns: 30:05:19
'
' 2014-06-17. Cactus Data ApS, CPH.

Dim strHour       As String
Dim strMinuteSec  As String
Dim strHours      As String

strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
strMinuteSec = Right("0" & CStr(Minute(datTime)), 2) & strSeparator & Right("0" & CStr(Second(datTime)), 2)
strHours = strHour & strSeparator & strMinuteSec

FormatHourMinuteSecond = strHours

End Function
``````
/gustav

Experts Exchange Solution brought to you by