How do I add a bunch of time values in Access?

1ddqd

I have a report that needs to be generated in a monthly format. The values need to be averaged across multiple dates. Here's the basic table design:

[U]Date[/U]______|_[U]Name[/U]_______|_[U]Login[/U]____|
7/1/2009     Agent 1       8:34:02
7/2/2009     Agent 1       8:41:11
7/3/2009     Agent 1       8:15:35
7/4/2009     Agent 1       8:26:22
7/5/2009     Agent 1       8:36:06
7/1/2009     Agent 2       8:15:09  
7/2/2009     Agent 2       8:23:46
7/3/2009     Agent 2       8:12:11
7/4/2009     Agent 2       8:44:04
7/5/2009     Agent 2       8:35:07

Every time an agent logs into our system, it generates a total of his hours logged in. This is formatted as time in the database. When I generate the report, though, it can't calculate the time, I can only count records and values, not sum or average like I need to.

Aumni

EDIT - Read the last line.

Give me a sec...

1ddqd

Aumni wrote: »

EDIT - Read the last line.

Give me a sec...

I thought Dateadd would work, but that's not the function that I need apparently :-/

Aumni

1ddqd wrote: »

Aumni wrote: »

EDIT - Read the last line.

Give me a sec...

I thought Dateadd would work, but that's not the function that I need apparently :-/

To get the average time you have to work magic.

Get the total seconds:

=(Sum(Left([lapsedtime],2))*3600)+(Sum(Mid([lapsedtime],4,2))*60)+(Sum(Right([Lapsedtime],2)))

Then get the average.

=Format(Int([Seconds1]/3600),"00") & ":" & Format(Int(([Seconds1]-(Int([Seconds1]/3600)*3600))/60),"00") & ":" & Format((([Seconds1] Mod 60)),"00")

1ddqd

Aumni wrote: »

...magic...

So I should be able to put this in the control (text box) for the report? For the proper control (Login Duration)?

I tried that, and when I run the report, it starts by asking for the start/end date like I told it to. I then get a dialog asking for "Seconds1" - I left it blank and the time comes out like this:

::

Should I make another column in the table with this formula, that reads the login duration?

Aumni

1ddqd wrote: »

Aumni wrote: »

...magic...

So I should be able to put this in the control (text box) for the report? For the proper control (Login Duration)?

I tried that, and when I run the report, it starts by asking for the start/end date like I told it to. I then get a dialog asking for "Seconds1" - I left it blank and the time comes out like this:

::

Should I make another column in the table with this formula, that reads the login duration?

Ahh crap, one sec. I gotta find where Seconds1 comes from. My code is sloppy.

Aumni

1ddqd wrote: »

Aumni wrote: »

...magic...

So I should be able to put this in the control (text box) for the report? For the proper control (Login Duration)?

I tried that, and when I run the report, it starts by asking for the start/end date like I told it to. I then get a dialog asking for "Seconds1" - I left it blank and the time comes out like this:

::

Should I make another column in the table with this formula, that reads the login duration?

Where Lapsed time is your Login field.

Make this an invisible TxBox named Seconds:

=(Sum(Left([lapsedtime],2))*3600)+(Sum(Mid([lapsedtime],4,2))*60)+(Sum(Right([Lapsedtime],2)))

Make this Invisible as Seconds1

=[Seconds]/Count([LapsedTime])

Set this as your Average txbox, make this one visible

=Format(Int([Seconds1]/3600),"00") & ":" & Format(Int(([Seconds1]-(Int([Seconds1]/3600)*3600))/60),"00") & ":" & Format((([Seconds1] Mod 60)),"00")

So that it grabs the correct values, modify :

=(Sum(Left([lapsedtime],2))*3600)+(Sum(Mid([lapsedtime],4,2))*60)+(Sum(Right([Lapsedtime],2)))

this is based on a 00:00:00 formatted time field. Since yours is 0:00:00 you may have to change it to sum(hour(login))*3600+ (sum(min(Login)*60+Sum(seconds(login))

I'm at work right now so I can't really put too much time in testing, sorry mate [/B]

1ddqd

Aumni wrote: »

Where Lapsed time is your Login field.

Make this an invisible TxBox named Seconds:
=(Sum(Left([lapsedtime],2))*3600)+(Sum(Mid([lapsedtime],4,2))*60)+(Sum(Right([Lapsedtime],2)))

Make this Invisible as Seconds1
=[Seconds]/Count([LapsedTime])

Set this as your Average txbox, make this one visible
=Format(Int([Seconds1]/3600),"00") & ":" & Format(Int(([Seconds1]-(Int([Seconds1]/3600)*3600))/60),"00") & ":" & Format((([Seconds1] Mod 60)),"00")

PERFECT. Now if only there was a function that completely understood time formats properly and was able to comprehend sum/average formulas.

Stupid time!

The results:


Blurred some names because I've seen some people checking these forums.

Aumni

1ddqd wrote: »

Aumni wrote: »

Where Lapsed time is your Login field.

Make this an invisible TxBox named Seconds:
=(Sum(Left([lapsedtime],2))*3600)+(Sum(Mid([lapsedtime],4,2))*60)+(Sum(Right([Lapsedtime],2)))

Make this Invisible as Seconds1
=[Seconds]/Count([LapsedTime])

Set this as your Average txbox, make this one visible
=Format(Int([Seconds1]/3600),"00") & ":" & Format(Int(([Seconds1]-(Int([Seconds1]/3600)*3600))/60),"00") & ":" & Format((([Seconds1] Mod 60)),"00")

PERFECT. Now if only there was a function that completely understood time formats properly and was able to comprehend sum/average formulas.

Stupid time!

Read my edit and make sure to error check. Good luck man

1ddqd

Checked, looks like it worked great. If I could, I'd throw you 1,000,000,000 rep points

Aumni

1ddqd wrote: »

Checked, looks like it worked great. If I could, I'd throw you 1,000,000,000 rep points

None of this would be necessary if Avg(Login) worked.

1ddqd

Aumni wrote: »

1ddqd wrote: »

Checked, looks like it worked great. If I could, I'd throw you 1,000,000,000 rep points

None of this would be necessary if Avg(Login) worked.

Exactly. This is Access 2007, I can only hope 2010 fixes it.