Sum of Time

[1987nac1987]

Sorry if this one has been done to death, it seems so glaringly pathetic, but I've yet to find an answer that fits!

I'm just putting together a query where I want to add up all the 'time' fields based on the grouping of another criteria. How do I achieve this?

And also where do I type the answer, I gather I need to format the 'totals' coulmn, but I'm not sure how.

Sorry to be a pain!

 

[John Big Booty]

If your total is less than 24 hours you could use;

format( sum( ... ), "hh:nn:ss" )

If however your total is more than 24 hours use;

format( Int(24*sum( ...)), "0") & format( sum(...) , ":nn:ss" )

 

[1987nac1987]

I'm yet again showing my Access ignorance here but;

If in the query I have a column called, say, 'TimeCode', how in that query do I get it to find the records that have that code, then only display the total of the respective 'TimeTaken' attributes?

And also, where do I type your time formatting bit?

 

[John Big Booty]

OK, I'm presuming that you already have a query that is summing your time, but it is returning an apparent nonsense value, this value is in fact your time as decimals of days.

With your query in design view put one of the codes into the top row of a new column in your query, replacing the three full stops (ellipsis) with the name of the field being summed. You will also need to select Expression from the drop down list in the Sum row in this new column.

Given that you may not now if your result is going to be more or less than 24 hours you could use the following;

IIf(Timesum <1,format( sum(Timesum), "hh:nn:ss" ),format( Int(24*sum( Timesum)), "0") & format( sum(Timesum) , ":nn:ss" ))

 

[namliam]

Given that you may not now if your result is going to be more or less than 24 hours you could use the following;

IIf(Timesum <1,format( sum(Timesum), "hh:nn:ss" ),format( Int(24*sum( Timesum)), "0") & format( sum(Timesum) , ":nn:ss" ))

why make this distinction John??
Using only:
format( Int(24*sum( Timesum)), "0") & format( sum(Timesum) , ":nn:ss" )

Will do just fine, for < 24 hours or > 24 hours no worries...
Also the INT can be removed, though it doesnt hurt to explicitly make it an integer... the format does it too...

 

[John Big Booty]

My bad, didn't think hard enough about code I found elsewhere