Summarize TIME

[kostaskir]

Hello everybody !

I have a problem that is quite unusual for me.
I want to summarize time ! Sound good Huh ?!

A have 1200 rows in Access 2007, which contain a field called "Time". With data type is Date/Time and Mask : hh:mm:ss.

I am trying to design a query which will give me the sum of hours.

For Example: 720:54:29 Hours:Minutes:Seconds

In advance thank you very much !!!

 

[raskew]

Hi -

We're going to need a little more information.

...which contain a field called "Time".

Firstoff, you need to rename that field. 'Time' is a reserved word in Access (as is 'Date') meaning it's the name of a built-in function and should not be used as a field name.

Despite how it's formatted, does this field also contain a date?

I am trying to design a query which will give me the sum of hours

What hours? How are you identifying those times you wish to total?

Bob

 

[kostaskir]

1. The name of the field is "cd_time". This has to do with total time on a music cd.

2. The field it does not contain a date. This is only time.

I used mask to change the format of the field. And it looks like that: 00:00:00.

I hoped I helped you...

Thanx.

 

[kostaskir]

1. The name of the field is "cd_time". All is about the total time of a Music CD.

2. The field it does not contain date. I used mask. 00:00:00. I don't know if this is the correct way to do this.

Thank you.

 

[raskew]

Hi -

A date/time data type field is not appropriate for storing elapsed times.

Take a look at Allen Browne's post here, as well as his article here.

HTH - Bob

 

[kostaskir]

Thank you for the links. But I am not looking for elapsed time or to use the Datediff function.

Take a look.

value A: 01:54:25
value B: 00:12:01
value C: 02:45:56

The 3 values supposed it is time: hh:mm:ss.

Now what I want is a query that calculates the following result: value A + value B + value C: 04:51:22.

Something like that actually.



Thanx.

 

[raskew]

Please describe an incidence where you'd add true times. IMHO, that makes no sense, e.g. noon + 2:00 PM. What is that?

Again, I suspect these are elapsed time.

Bob

Added:

Carrying my off-hand example:
noon + 2:00 PM, (adding true times, not elapsed times) would equate to:
12:00:00 + 14:00:00 = 01:02:00
I'm pretty sure that's not what you're after.

 

[kostaskir]

I guess you are right Bob.

I need to summarize elapsed times. After all I want to add times of Music CDs. And really I am not sure if I have to do it with data type "Time" or with data type "Number".

I have 1500 CDs. For every CD I want to know the "elapsed" time. hh:mm:ss.

At the end for every genre, I would like to know the total time: For example: For the the genre Rock : 458 hours, 34 minutes and 23 seconds.

Something like that.

What would you do ?

Thanx in advance Bob !

 

[raskew]

OK -

We're talking 'elapsed times'. Despite how they may be displayed, they need to be stored as numbers. Since you're interested in hours:minutes:seconds, you'll need to store these elapsed times as # of seconds.

How you arrive at the elapsed times becomes the problem. If you already have them as hours:minutes:seconds (as shown in your example), you can do this:
x = #00:00:00#
y = #01:54:25# 'one of your examples
dur = datediff("s", x, y)
? dur
6865 'this is the duration expressed in seconds, and this is what you'll want to store

If you have a bunch of durations stored, you can do a simple totals query to arrive at the sum of seconds.

Here's how you'd convert your # of seconds to a hours:minutes:seconds display: (from the debug (immediate) window:

totduration = 11448
totdisplay = totduration \ 3600 & " hours " & (totduration mod 3600)\60 & " minutes " & totduration mod 60 & " seconds"
? totdisplay
3 hours 10 minutes 48 seconds

...to verify that this is correct:
check = (3 * 3600) + (10 * 60) + 48
? check
11448

Please post back with your questions.

Bob

 

[kostaskir]

Thank you so much for your help.

Now, I will try to follow your instructions.

I will let you know

Thanx.

 

[JohnLee]

Hi,

Reading your post I have something that might be helpful along the lines of the calculating time in the way you are looking for. Have a look at this snap shot of my DB and have a look at how I've got to total timings aspect for each record.

John

 

[raskew]

JohnLee -

Thanks for that. It's an ideal example. And, this query should make it clear what I've tried to get accross:

SELECT
    tblFFStats2.lngStatsID
  , tblFFStats2.dtmTotalTime
  , DateDiff("s",#12/30/1899#,[dtmTotaltime]) AS x
  , [x]\3600 & " hours " & ([x] Mod 3600)\60 & " minutes " & [x] Mod 60 & " seconds" AS y
FROM
   tblFFStats2;

Best wishes - Bob

Note: Access converted #00:00:00# (which is what I originally plugged into the query) to #12/30/1899#.

 

[kostaskir]

Thank you Both , JohnLee & Raskew !!!

I did it, and it works fine !

Thanks.

 

[JohnLee]

Glad to have been of help.

John

 

[zashmore]

OK -

We're talking 'elapsed times'. Despite how they may be displayed, they need to be stored as numbers. Since you're interested in hours:minutes:seconds, you'll need to store these elapsed times as # of seconds.

How you arrive at the elapsed times becomes the problem. If you already have them as hours:minutes:seconds (as shown in your example), you can do this:
x = #00:00:00#
y = #01:54:25# 'one of your examples
dur = datediff("s", x, y)
? dur
6865 'this is the duration expressed in seconds, and this is what you'll want to store

If you have a bunch of durations stored, you can do a simple totals query to arrive at the sum of seconds.

Here's how you'd convert your # of seconds to a hours:minutes:seconds display: (from the debug (immediate) window:

totduration = 11448
totdisplay = totduration \ 3600 & " hours " & (totduration mod 3600)\60 & " minutes " & totduration mod 60 & " seconds"
? totdisplay
3 hours 10 minutes 48 seconds

...to verify that this is correct:
check = (3 * 3600) + (10 * 60) + 48
? check
11448

Ok I've done this and worked ok.

However, I need to be able to Sum it all together in a report footer.

What's the formula for adding it all together?