Average min:sec

[Guest]

I need to calculate average response times for a list

alarmdate arrivaldate Expr1
9/16/2004 12:51:10 AM , 9/16/2004 12:56:08 AM=0:4:58
10/26/2004 5:39:41 PM , 10/26/2004 5:45:04 PM=0:5:23
10/28/2004 6:21:09 PM , 10/28/2004 6:29:30 PM=0:8:21
10/28/2004 10:26:49 PM , 10/28/2004 10:34:04 PM=0:7:15
11/2/2004 7:15:45 AM , 11/2/2004 7:28:09 AM=0:12:24


How do I average H:N:S over a list.

 

[Douglas J. Steele]

Use the DateDiff function to give you the response time in seconds. Average
the number of seconds. Write a function to convert from seconds to h:m:s
format.

 

[Paul Overway]

A UDF is really not necessary...you could just use Avg in the query and then
CDate the result...i.e.,

SELECT Avg(TheDate) AS TheAverage, CDate([TheAverage]) AS AvgDate
FROM tblWhatever

 

[Douglas J. Steele]

If the average is a number of seconds, how will CDate give you anything
useful?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A UDF is really not necessary...you could just use Avg in the query and
then CDate the result...i.e.,

SELECT Avg(TheDate) AS TheAverage, CDate([TheAverage]) AS AvgDate
FROM tblWhatever

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Use the DateDiff function to give you the response time in seconds.
Average the number of seconds. Write a function to convert from seconds
to h:m:s format.

 

[Paul Overway]

The Date data type includes seconds. So, by averaging the elapsed times
you'd get just what the OP was looking for as a date data type which can be
displayed as time. So, in this particular case, the SQL would be:

SELECT Avg(Exp1) AS TheAverage, CDate([TheAverage]) AS AvgResponse
FROM tblWhatever


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

If the average is a number of seconds, how will CDate give you anything
useful?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A UDF is really not necessary...you could just use Avg in the query and
then CDate the result...i.e.,

SELECT Avg(TheDate) AS TheAverage, CDate([TheAverage]) AS AvgDate
FROM tblWhatever

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Use the DateDiff function to give you the response time in seconds.
Average the number of seconds. Write a function to convert from seconds
to h:m:s format.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I need to calculate average response times for a list

alarmdate arrivaldate Expr1
9/16/2004 12:51:10 AM , 9/16/2004 12:56:08 AM=0:4:58
10/26/2004 5:39:41 PM , 10/26/2004 5:45:04 PM=0:5:23
10/28/2004 6:21:09 PM , 10/28/2004 6:29:30 PM=0:8:21
10/28/2004 10:26:49 PM , 10/28/2004 10:34:04 PM=0:7:15
11/2/2004 7:15:45 AM , 11/2/2004 7:28:09 AM=0:12:24


How do I average H:N:S over a list.

 

[Douglas J. Steele]

Ah, I see. You're recommending treating a Date field as a duration,
something I always advise against.

Of course, your solution doesn't actually solve the original problem, since
the OP needs to calculating the duration.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Date data type includes seconds. So, by averaging the elapsed times
you'd get just what the OP was looking for as a date data type which can
be displayed as time. So, in this particular case, the SQL would be:

SELECT Avg(Exp1) AS TheAverage, CDate([TheAverage]) AS AvgResponse
FROM tblWhatever


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

If the average is a number of seconds, how will CDate give you anything
useful?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A UDF is really not necessary...you could just use Avg in the query and
then CDate the result...i.e.,

SELECT Avg(TheDate) AS TheAverage, CDate([TheAverage]) AS AvgDate
FROM tblWhatever

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Use the DateDiff function to give you the response time in seconds.
Average the number of seconds. Write a function to convert from seconds
to h:m:s format.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I need to calculate average response times for a list

alarmdate arrivaldate Expr1
9/16/2004 12:51:10 AM , 9/16/2004 12:56:08 AM=0:4:58
10/26/2004 5:39:41 PM , 10/26/2004 5:45:04 PM=0:5:23
10/28/2004 6:21:09 PM , 10/28/2004 6:29:30 PM=0:8:21
10/28/2004 10:26:49 PM , 10/28/2004 10:34:04 PM=0:7:15
11/2/2004 7:15:45 AM , 11/2/2004 7:28:09 AM=0:12:24


How do I average H:N:S over a list.

 

[Paul Overway]

Actually, it does solve his problem...because he has already calculated the
elapsedtime/duration as indicated by Expr1 and that result is a double/date
data type. He just wanted to know the average Expr1.

Why would you favor using a different data type for an elapsed time?
Unless you're talking elapsed times less than a second, it makes no
difference in terms of precision to use a data type other than date. I'd
agree if you needed precision less than a second, or actually storing the
result (which he shouldn't be doing), but in many cases it isn't called for.
The result MAY need to be converted to get the desired display format, but
in this case it wouldn't unless the elapsed times exceed 24 hours. Even if
the result DID need to be converted, it is simple math to display the result
as elapsed time in seconds, minutes, hours, or days.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Ah, I see. You're recommending treating a Date field as a duration,
something I always advise against.

Of course, your solution doesn't actually solve the original problem,
since the OP needs to calculating the duration.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Date data type includes seconds. So, by averaging the elapsed times
you'd get just what the OP was looking for as a date data type which can
be displayed as time. So, in this particular case, the SQL would be:

SELECT Avg(Exp1) AS TheAverage, CDate([TheAverage]) AS AvgResponse
FROM tblWhatever


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

If the average is a number of seconds, how will CDate give you anything
useful?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



A UDF is really not necessary...you could just use Avg in the query and
then CDate the result...i.e.,

SELECT Avg(TheDate) AS TheAverage, CDate([TheAverage]) AS AvgDate
FROM tblWhatever

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


message Use the DateDiff function to give you the response time in seconds.
Average the number of seconds. Write a function to convert from
seconds to h:m:s format.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I need to calculate average response times for a list

alarmdate arrivaldate Expr1
9/16/2004 12:51:10 AM , 9/16/2004 12:56:08 AM=0:4:58
10/26/2004 5:39:41 PM , 10/26/2004 5:45:04 PM=0:5:23
10/28/2004 6:21:09 PM , 10/28/2004 6:29:30 PM=0:8:21
10/28/2004 10:26:49 PM , 10/28/2004 10:34:04 PM=0:7:15
11/2/2004 7:15:45 AM , 11/2/2004 7:28:09 AM=0:12:24


How do I average H:N:S over a list.

 

[Douglas J. Steele]

It's precisely because of the fact that durations of greater than 24 hours
cannot be properly displayed that I (and many others) do not recommend
storing durations in Date fields. An Access date field is really only
intended to be used for a timestamp (i.e.: a date and time), not a time-only
value nor a time duration. This is pretty obvious when you look at how the
values are stored internally.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually, it does solve his problem...because he has already calculated
the elapsedtime/duration as indicated by Expr1 and that result is a
double/date data type. He just wanted to know the average Expr1.

Why would you favor using a different data type for an elapsed time?
Unless you're talking elapsed times less than a second, it makes no
difference in terms of precision to use a data type other than date.
I'd agree if you needed precision less than a second, or actually storing
the result (which he shouldn't be doing), but in many cases it isn't
called for. The result MAY need to be converted to get the desired display
format, but in this case it wouldn't unless the elapsed times exceed 24
hours. Even if the result DID need to be converted, it is simple math to
display the result as elapsed time in seconds, minutes, hours, or days.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Ah, I see. You're recommending treating a Date field as a duration,
something I always advise against.

Of course, your solution doesn't actually solve the original problem,
since the OP needs to calculating the duration.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Date data type includes seconds. So, by averaging the elapsed times
you'd get just what the OP was looking for as a date data type which can
be displayed as time. So, in this particular case, the SQL would be:

SELECT Avg(Exp1) AS TheAverage, CDate([TheAverage]) AS AvgResponse
FROM tblWhatever


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


If the average is a number of seconds, how will CDate give you anything
useful?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



A UDF is really not necessary...you could just use Avg in the query and
then CDate the result...i.e.,

SELECT Avg(TheDate) AS TheAverage, CDate([TheAverage]) AS AvgDate
FROM tblWhatever

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


message Use the DateDiff function to give you the response time in seconds.
Average the number of seconds. Write a function to convert from
seconds to h:m:s format.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I need to calculate average response times for a list

alarmdate arrivaldate Expr1
9/16/2004 12:51:10 AM , 9/16/2004 12:56:08 AM=0:4:58
10/26/2004 5:39:41 PM , 10/26/2004 5:45:04 PM=0:5:23
10/28/2004 6:21:09 PM , 10/28/2004 6:29:30 PM=0:8:21
10/28/2004 10:26:49 PM , 10/28/2004 10:34:04 PM=0:7:15
11/2/2004 7:15:45 AM , 11/2/2004 7:28:09 AM=0:12:24


How do I average H:N:S over a list.

 

[Paul Overway]

We already have two Date data types. We're both just doing math on those
stored values. In cases where the duration is greater than 24 hours, our
CALCULATED results are going to be the same. Its just simple math. Again,
if you're going to store the calculated result, you have a point. But
calculated results shouldn't be stored in a normalized table anyway.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

It's precisely because of the fact that durations of greater than 24 hours
cannot be properly displayed that I (and many others) do not recommend
storing durations in Date fields. An Access date field is really only
intended to be used for a timestamp (i.e.: a date and time), not a
time-only value nor a time duration. This is pretty obvious when you look
at how the values are stored internally.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually, it does solve his problem...because he has already calculated
the elapsedtime/duration as indicated by Expr1 and that result is a
double/date data type. He just wanted to know the average Expr1.

Why would you favor using a different data type for an elapsed time?
Unless you're talking elapsed times less than a second, it makes no
difference in terms of precision to use a data type other than date. I'd
agree if you needed precision less than a second, or actually storing the
result (which he shouldn't be doing), but in many cases it isn't called
for. The result MAY need to be converted to get the desired display
format, but in this case it wouldn't unless the elapsed times exceed 24
hours. Even if the result DID need to be converted, it is simple math to
display the result as elapsed time in seconds, minutes, hours, or days.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Ah, I see. You're recommending treating a Date field as a duration,
something I always advise against.

Of course, your solution doesn't actually solve the original problem,
since the OP needs to calculating the duration.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The Date data type includes seconds. So, by averaging the elapsed
times you'd get just what the OP was looking for as a date data type
which can be displayed as time. So, in this particular case, the SQL
would be:

SELECT Avg(Exp1) AS TheAverage, CDate([TheAverage]) AS AvgResponse
FROM tblWhatever


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


message If the average is a number of seconds, how will CDate give you
anything useful?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message A UDF is really not necessary...you could just use Avg in the query
and then CDate the result...i.e.,

SELECT Avg(TheDate) AS TheAverage, CDate([TheAverage]) AS AvgDate
FROM tblWhatever

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


message Use the DateDiff function to give you the response time in seconds.
Average the number of seconds. Write a function to convert from
seconds to h:m:s format.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I need to calculate average response times for a list

alarmdate arrivaldate Expr1
9/16/2004 12:51:10 AM , 9/16/2004 12:56:08 AM=0:4:58
10/26/2004 5:39:41 PM , 10/26/2004 5:45:04 PM=0:5:23
10/28/2004 6:21:09 PM , 10/28/2004 6:29:30 PM=0:8:21
10/28/2004 10:26:49 PM , 10/28/2004 10:34:04 PM=0:7:15
11/2/2004 7:15:45 AM , 11/2/2004 7:28:09 AM=0:12:24


How do I average H:N:S over a list.