short time addition

[smayshar]

I have a table with a short time fild that indicate how many hh and nn some thing was on
what will be the sql to add a long list of this values ?
like:

01:50
10:20
02:30
-----
14:40
thaks

 

[PHV]

You may try something like this:
SELECT (24*Int(Sum([myTime]))+Format(Sum([myTime),'h')) & Format(Sum([myTime],':nn') AS TotalTime
FROM [myTable]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

? Format(#01:50# + #10:20# + #02:30#, "Short Time")
14:40

which just illustrates that there is no need for the seperate addition of the hours and minutes portion, just the formatting of the results.


On the other hand, summing a "long list" of such values may (almost certainly will) exceed the capicity of the "short time" format, so the display of such a sum must be carefully considered.





MichaelRed

 

[PHV]

MichaelRed, my suggestion was for handling TotalTime greater than 24 hours.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[smayshar]

thanks for the fast reply
PHV I tried to put the sql statement in a query but was not accepted by the jet syntax error
MichaelRed what if it is unknown amount af values ?

 

[MichaelRed]

PHV

I guess, then, that the "something like" was supposed to be a clue?

My point is that there is no need to format or modfy the summation of the individual values of the series. I would also consider that the subpart(s) of your expression may easily return (rounding) errors.




MichaelRed

 

[smayshar]

any sugestions how to summerise short time ?

 

[MichaelRed]

smayshar,

Date/Time values in Ms. A. are stored as Floating point (double) values, with the 'whole number' portion representing the number (count) of days from #12/30/1899# and decimal portion representing the portion (fraction) of a day(if any). When adding time values, and applying format(s), if the summed values exceed the format capacity, Ms. A. simply displays the requested portion, as in:

WorkingTime
12:45
1:20
4:16
14:44
12:12
16:23
18:19
21:12

TotTim
5:11

which is derived from:

SELECT Sum(CDate([WorkingTime])) AS TotTim
FROM tblWrkTime
WITH OWNERACCESS OPTION;

with the "short time" format applied to the field

As opposed to:

4.21597222222
[code]
without the formatting

The "4." portion represents the four(4) days, while the ".2159 ..." portion represents the 5 Hours and a few (11?) minutes, as seen via:

[code]
? Format(4.21597222222, "Short Time")
05:11

MichaelRed

 

[JonFer]

Here is a slight modification of PHV's SQL. There was a missing parentheses:

SELECT ( 24*Int(Sum([myTime]))+Hour(Sum([myTime)) ) & Format(Sum([myTime]),':nn') AS TotalTime
FROM [myTable]


John

http://www.interautomateconsulting.com

 

[PHV]

Sorry for the typo:
SELECT (24*Int(Sum([myTime]))+Format(Sum([myTime]),'h')) & Format(Sum([myTime]),':nn') AS TotalTime

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244