469,072 Members | 1,844 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,072 developers. It's quick & easy.

How to Average time in MS Access 2003

I need to Average time data that has been entered. What do I need to do in the query?
Dec 26 '13 #1
13 6446
12,516 Expert Mod 8TB
Use the average function: Avg().
Dec 26 '13 #2
I know that. But when I Avg date/time entries I get a very low number (0.291666666666667). is there something I need to put in the criteria to make it show correctly?
Dec 26 '13 #3
12,516 Expert Mod 8TB
Use CDate() to convert the number to a date time value.
Dec 26 '13 #4
5,400 Expert Mod 4TB
You need to be more specific as to how your data is being entered.
Say I Work from 8am to 4pm every day I average 8 hours a day; however, to calculate that information depends on how the information is stored in the data tables. If it is simply stored in a field type cast numeric(double) 8.0 then you can simply use an agregate query to do the average. However, if you are storing the information in date/time fields then you need to calculate the time difference and then the average.

This sounds very much like a homework question, we try not to provide code for such; thus, you will need to post you SQL either for the calculated field or the entire SQL script.

Please remember to format such script using the [CODE/] button.
Dec 26 '13 #5
32,154 Expert Mod 16PB
Converting the form of the data (Using CDate()) does nothing to the data itself (in this case), but it will have the effect of telling the SQL engine to change the default format of the data to a date/time based one. Although this (or something very similar) can also be achieved by using the Format property of the column, or even the Format() function itself, these other approaches have limitations in some scenarios.

What you are seeing is that date/time data is being passed into the Avg() function but as the return value for this function is not, itself, defined as date/time, the SQL engine treats it as a general number.
Dec 26 '13 #6
so is there anyway to avg a series of time data? data inputed as date/time. I am inputing the actual time a task is started and then in another field the time it was finished. So we want to avg all the start times and avg all the finished times.
Dec 26 '13 #7
12,516 Expert Mod 8TB
Use DateDiff to calculate the different in minutes between the two tasks and then average that value. That will give you the average number of minutes it took to complete a task.
Dec 26 '13 #8
I dont need to calculate the avg number of minutes it takes to do the task. We are calcuating the average of the actual time.

for example 12/1 7:00am
12/2 7:15am
12/3 7:20am
12/4 7:00am

then figure out what the avg time for those 4 values.
Dec 26 '13 #9
5,400 Expert Mod 4TB
So you want what?
07:09 AM
or 9 minutes
or what?
Dec 26 '13 #10
7:09 AM is what I am looking for
Dec 26 '13 #11
5,400 Expert Mod 4TB
And this is a beautiful homework question.

Rabbit in post #8 has the first part of the answer for you.

Once you have the average elapsed minutes then add that to the root time (lowest hour)

Please post your code/SQL, if you would, kindly format the posted script using the [CODE/] button.
Dec 26 '13 #12
12,516 Expert Mod 8TB
If you want an average starting time, then you will need to 0 out the integer part of the date so that it all starts on the same date with only the time varying. Then you can take the average of that.

You want to 0 out the interger part of a date because the a date is stored as a decimal with the fraction representing time and the integer representing number of days elapsed.
Dec 26 '13 #13
32,154 Expert Mod 16PB
Another way to do that is to use TimeValue() of the start date/times within the Avg() function call.

NB. It would have been much easier to give a proper answer to your question right from the off if you'd taken the effort to ask your question properly at the start. Your first post was not even half of the story we subsequently learned. Please do not follow this pattern in future questions. If so, they are likely to be deleted immediately before so many people's time is wasted on getting to the point that should be the start point.
Dec 26 '13 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by GregM | last post: by
28 posts views Thread by Neil Ginsberg | last post: by
49 posts views Thread by Mell via AccessMonster.com | last post: by
17 posts views Thread by Mell via AccessMonster.com | last post: by
9 posts views Thread by Paul H | last post: by
9 posts views Thread by prakashwadhwani | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.