calculate average time

[kidrobot]

I need to calculate average time!! I'm having a lot of problems doing this. I have a field that I'm trying to average that shows up as for example, 00:20:45 which is 20 min and 45 seconds, which is the amount of time a user has been in our facility. I want to average this field by week, but the averages keep showing up as time such as 12:20:46... !!!help

 

[raskew]

Hi -

Would you provide an example of how you're calculating elapsed time.

Thanks - Bob

 

[kidrobot]

raskew. the elapsed time is already stored in the database, so what I'm trying to do is avaerage the elapsed time by week. I have a calendar table with WOY I joined with my main table so it is no problem pulling by week. When I pull elapsed time by average it just doesn't come out right.

 

[MSAccessRookie]

Can I see the formula that you are using? It sounds like you are using Tima/Date format and the Hours are being misinterpreted.

 

[raskew]

My point exactly! If you aren't privy to how elapsed time is calculated, at least show us the data type of the Elapsed Time field, examples of the field value and the formatting of this field.

Bob

 

[kidrobot]

I'm not at work so here is a sample query.. it's just a rough sketch...

Select Avg([Dwell_Time]) AS AvgDwell, WOY
From OldDate
Where date between #3/1/2008# and #6/30/2008

 

[raskew]

Please refer to my previous post, and include an explanation of "WOY" and how/where it's computed.

Bob

 

[kidrobot]

sorrry if i'm unclear I just dont have the database with me and I have to finish this query really early tomorrow morning, so i need to know how to do it now!

The elapsed time is formated as Date/Time, it shows up as 00:20:45 for an elapsed time of 20 minutes and 45 seconds.


Week of the year is from a calendar table which just converts date to week number.

 

[raskew]

Kidrobot -

We're being asked to help return a correct average. Problem being, we don't know what we're trying to average.

The elapsed time is formated as Date/Time

But what's the underlying field? Is it a string, a formatted number, a mal-intentioned attempt to display an elapsed time as a date, what??

Date/time data types are intended to store dates, not periods of elapsed time. If there's any confusion, take a look at this MSKB article that describes how Access stores dates/times: http://support.microsoft.com/kb/q130514/

Please help us out.

Best wishes - Bob

P.S.: What does Week of the Year have to do with anything?

 

[MSAccessRookie]

Is it possible that you could change your underlying field to a number that represents the minutes passed?. Numbers are very easy to average, and everything else should just fall into place. I believe that you can make the user think they are entering a time in the form (if that is necessary) by masking a text field.

 

[kidrobot]

if this helps... i remember elapsed time is formatted as a date/time fields as hh:mm:ss

Week of the Year doesnt really mean anything it's just the time ranges I need the data formated.

 

[MSAccessRookie]

if this helps... i remember elapsed time is formatted as a date/time fields as hh:mm:ss

Week of the Year doesnt really mean anything it's just the time ranges I need the data formated.

OK then,

My understanding is that Access Time/Date Format Rules will always convert 00:20:45 to 12:20:45, and there is nothing that I am aware of that can be done to change that. perhaps someone else has ideas.

I don't think Access supports Time() type fields (at least not in Access 2003 or before)

 

[kidrobot]

just curious what data type is best to calculate average for elapsed time?

sorry for being so retarded guys, I wish I had access to the database right now. thanks again for baring with me.

 

[MSAccessRookie]

just curious what data type is best to calculate average for elapsed time?

sorry for being so retarded guys, I wish I had access to the database right now. thanks again for baring with me.

Perhaps a number representing the number of minutes (or maybe even seconds) passed? Not sure. Maybe a Text Field to convert to a number might be easier

 

[raskew]

I have to finish this query really early tomorrow morning

If that's the case, you need to do some serious investigation. If the best you can do is describe the way elapsed times are formatted, but don't know what they represent, think you're in deep doo-doo.

Best chance is if someone takes a SWAG ("silly wild-assed guess") and, out of pure luck, hits the nail on the head.

Good Luck - Bob

 

[kidrobot]

OK then,

My understanding is that Access Time/Date Format Rules will always convert 00:20:45 to 12:20:45, and there is nothing that I am aware of that can be done to change that. perhaps someone else has ideas.

I don't think Access supports Time() type fields (at least not in Access 2003 or before)

what you stated is what happens to my field, it'll convert everything to 12:xx:xx ... should I convert this field to text or number?

 

[raskew]

... should I convert this field to text or number?

Well, it might depend on what this field represents. What is it??

Bob

 

[Mike375]

I don't know if this is of any help for now but who knows

A large part of my DB is for telemarketing. When a new record is moved to the Time() is set to a field in a new record in a Many table. When the call is completed and Next Prospect or Finish Session is clicked Time() goes into the record in another field.

My queries subtract the Enter Time from the Leave Time. That result is then multiplied by 86400....that is number of seconds in a day. That answer is then divided by 3600 and gives the total in hours. Obviously I also have another one for minutes.

Thus since I have simple number in a column I can have Ave, Max, Total on selected records.

It is while since I have tried it but I am fairly certain that if Now() is used instead of Time() that handles where the time span is before midnight and after midnight.

 

[raskew]

that if Now() is used instead of Time() that handles where the time span is before midnight and after midnight.

Mike - You're absolutely correct! Problem being that KidRobot isn't sharing what the field values represent and is tap-dancing with descriptions of formats, but not telling us the underlying field values. So, we're left to guess. I don't understand -- what's the deal? (Realize that many of you fellow-responders are much more sympathetic than I. Conversely, I think the OP needs to provide some valuable info and quit tap-dancing.)

Bob

 

[Mike375]

Bob,

I just checked...at least my memory is still working to some degree