The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
How do I add a bunch of time values in Access?
1ddqd
Registered User regular
Registered User regular
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:
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.
[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.
1ddqd on
0
Posts
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")
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.
Where Lapsed time is your Login field.
Make this an invisible TxBox named Seconds:
Make this Invisible as Seconds1
Set this as your Average txbox, make this one visible
So that it grabs the correct values, modify :
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
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.
Read my edit and make sure to error check. Good luck man
None of this would be necessary if Avg(Login) worked.
Exactly. This is Access 2007, I can only hope 2010 fixes it.