Syntax to Average a time duration (hh:mm:ss).

[Sid]

I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't include my first
field name as a part of an aggregate function". The error disappears when I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Duane Hookom]

SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime field or name
a field name.

 

[Sid]

Duane
I'm actually getting data from a subquery. I can do the Duration on the fly
in this or any query where its needed..
The Avg(Table.Duration) AS DurationAverage's syntax generates an error.
I'll try the Avg(on my time difference calculation) in my SQL.
Avg() may not like my hh:mm:ss value.
Thanks.

 

[Sid]

Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS DurationAvg

It gives the same error.
Sid

 

[Duane Hookom]

This might not be an issue if you stored the duration of time in a numeric
field as I suggested. You are attempting to Average a text string. You might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod 60,"\:00") &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to average the
value prior to formatting it.

--
Duane Hookom
MS Access MVP

Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS DurationAvg

It gives the same error.
Sid

SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime field or name
a field name.


--
Duane Hookom
MS Access MVP


when

 

[Sid]

Duane
I just got what you meant about naming a field "name", from your first
posting.
My actual field is not named name. I used that to help readers better
understand my scenario.

I guess my question is, if I have say 50 records all with different elapsed
time/Durations, How can I average (Add them together, Then divide/50) if
I'm trying to average the values BEFORE I have all 50 Duration values? It
looks as if your suggestion would try to average the elements (hrs, mins,
sec) of One duration, instead of the Aggregate. Unless it has the effect of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS Duration

Tried this, gives 'Wrong number of arguments' error(with or without the
first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00")) &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAverage

This is looking ominous. Eventually I'll have different groups, with the
average time indicated for each group.
Thanks
Sid

This might not be an issue if you stored the duration of time in a numeric
field as I suggested. You are attempting to Average a text string. You might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod 60,"\:00") &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to average the
value prior to formatting it.

--
Duane Hookom
MS Access MVP

Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS DurationAvg

It gives the same error.
Sid

SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime field

or
name

a field name.


--
Duane Hookom
MS Access MVP


I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't include my first
field name as a part of an aggregate function". The error

disappears
when

I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Duane Hookom]

I haven't heard a good reason for displaying this as minutes and seconds. If
your times are going to average less than 24 hours, then try:
Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss")

Please don't use field names with known issues especially if you are making
them up for "understanding".
--
Duane Hookom
MS Access MVP

Duane
I just got what you meant about naming a field "name", from your first
posting.
My actual field is not named name. I used that to help readers better
understand my scenario.

I guess my question is, if I have say 50 records all with different elapsed
time/Durations, How can I average (Add them together, Then divide/50) if
I'm trying to average the values BEFORE I have all 50 Duration values? It
looks as if your suggestion would try to average the elements (hrs, mins,
sec) of One duration, instead of the Aggregate. Unless it has the effect of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS Duration

Tried this, gives 'Wrong number of arguments' error(with or without the
first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00")) &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAverage

This is looking ominous. Eventually I'll have different groups, with the
average time indicated for each group.
Thanks
Sid

This might not be an issue if you stored the duration of time in a numeric
field as I suggested. You are attempting to Average a text string. You might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod 60,"\:00") &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to average the
value prior to formatting it.

--
Duane Hookom
MS Access MVP

Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS DurationAvg

It gives the same error.
Sid
SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime field or
name
a field name.


--
Duane Hookom
MS Access MVP


I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't include my
first
field name as a part of an aggregate function". The error disappears
when
I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Sid]

I'm really sorry about the 'name' thing...

I may not be seeing the logic, I want to understand, but what purpose would
be served by Averaging each set of TimeOn/TimeOff times? Wouldn't that
result be like: TimeOn(3:00) TimeOff(6:00) = 4:30?

I need to average a batch of DateDiff([TimeOn],[TimeOff]) Results(elapsed
time). As above, the difference/duration would be 03:00:00.

So I need to Average a group of times(time differences) like:
00:02:30, 00:20:21, 01:23:45 = (01:46:36)\3 = 00:35:32 Avg.

The good reason for my Minutes and Seconds is that some of my events may
have a duration of 5 seconds, 40 seconds, 12 Minutes, or 2 hours. Some of
my reports must display the exact number of seconds of an event. So If I
average three events, 17 seconds, 50 second, 20 second the result is 29
Seconds average.

At the moment, I'm trying to accurately convert my times to Seconds using
CLng(DateDiff("s",[TimeOn],[TimeOff])). Then do my average based on Seconds
Total divided by the Count[OfRecords]. Strangely, the seconds aren't always
accurate.

Thanks for your time.
Sid

I haven't heard a good reason for displaying this as minutes and seconds. If
your times are going to average less than 24 hours, then try:
Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss")

Please don't use field names with known issues especially if you are making
them up for "understanding".
--
Duane Hookom
MS Access MVP

Duane
I just got what you meant about naming a field "name", from your first
posting.
My actual field is not named name. I used that to help readers better
understand my scenario.

I guess my question is, if I have say 50 records all with different elapsed
time/Durations, How can I average (Add them together, Then divide/50) if
I'm trying to average the values BEFORE I have all 50 Duration values? It
looks as if your suggestion would try to average the elements (hrs, mins,
sec) of One duration, instead of the Aggregate. Unless it has the

effect

of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS Duration

Tried this, gives 'Wrong number of arguments' error(with or without the
first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00")) &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAverage

This is looking ominous. Eventually I'll have different groups, with the
average time indicated for each group.
Thanks
Sid

This might not be an issue if you stored the duration of time in a numeric
field as I suggested. You are attempting to Average a text string. You might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod 60,"\:00") &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to average the
value prior to formatting it.

--
Duane Hookom
MS Access MVP


Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS DurationAvg

It gives the same error.
Sid
SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime

field
or

name
a field name.


--
Duane Hookom
MS Access MVP


I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't include my
first
field name as a part of an aggregate function". The error disappears
when
I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Duane Hookom]

Possibly the issue is to make sure that in the totals query, the Total: for
this column should be "Expression".
This is the SQL that should work:
SELECT Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss") AS AverageTime
FROM Task;
If you try this and it doesn't work then come on back with 4 records of
TimeOff and TimeOn values and what you would expect for a result.

--
Duane Hookom
MS Access MVP

I'm really sorry about the 'name' thing...

I may not be seeing the logic, I want to understand, but what purpose would
be served by Averaging each set of TimeOn/TimeOff times? Wouldn't that
result be like: TimeOn(3:00) TimeOff(6:00) = 4:30?

I need to average a batch of DateDiff([TimeOn],[TimeOff]) Results(elapsed
time). As above, the difference/duration would be 03:00:00.

So I need to Average a group of times(time differences) like:
00:02:30, 00:20:21, 01:23:45 = (01:46:36)\3 = 00:35:32 Avg.

The good reason for my Minutes and Seconds is that some of my events may
have a duration of 5 seconds, 40 seconds, 12 Minutes, or 2 hours. Some of
my reports must display the exact number of seconds of an event. So If I
average three events, 17 seconds, 50 second, 20 second the result is 29
Seconds average.

At the moment, I'm trying to accurately convert my times to Seconds using
CLng(DateDiff("s",[TimeOn],[TimeOff])). Then do my average based on Seconds
Total divided by the Count[OfRecords]. Strangely, the seconds aren't always
accurate.

Thanks for your time.
Sid

I haven't heard a good reason for displaying this as minutes and

seconds.

If
your times are going to average less than 24 hours, then try:
Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss")

Please don't use field names with known issues especially if you are making
them up for "understanding".
--
Duane Hookom
MS Access MVP

Duane
I just got what you meant about naming a field "name", from your first
posting.
My actual field is not named name. I used that to help readers better
understand my scenario.

I guess my question is, if I have say 50 records all with different elapsed
time/Durations, How can I average (Add them together, Then divide/50) if
I'm trying to average the values BEFORE I have all 50 Duration values? It
looks as if your suggestion would try to average the elements (hrs, mins,
sec) of One duration, instead of the Aggregate. Unless it has the

effect

of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS Duration

Tried this, gives 'Wrong number of arguments' error(with or without the
first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00")) &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAverage

This is looking ominous. Eventually I'll have different groups, with the
average time indicated for each group.
Thanks
Sid


This might not be an issue if you stored the duration of time in a numeric
field as I suggested. You are attempting to Average a text string. You
might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod 60,"\:00") &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to average the
value prior to formatting it.

--
Duane Hookom
MS Access MVP


Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS DurationAvg

It gives the same error.
Sid
SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime field
or
name
a field name.


--
Duane Hookom
MS Access MVP


I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't

include
my

first
field name as a part of an aggregate function". The error
disappears
when
I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Sid]

SELECT Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss") AS AverageTime
produces a 'wrong number of arguments' error. Is CLng the only way to
convert Time to Seconds?

Possibly the issue is to make sure that in the totals query, the Total: for
this column should be "Expression".
This is the SQL that should work:

FROM Task;

If you try this and it doesn't work then come on back with 4 records of
TimeOff and TimeOn values and what you would expect for a result.

--
Duane Hookom
MS Access MVP

I'm really sorry about the 'name' thing...

I may not be seeing the logic, I want to understand, but what purpose would
be served by Averaging each set of TimeOn/TimeOff times? Wouldn't that
result be like: TimeOn(3:00) TimeOff(6:00) = 4:30?

I need to average a batch of DateDiff([TimeOn],[TimeOff]) Results(elapsed
time). As above, the difference/duration would be 03:00:00.

So I need to Average a group of times(time differences) like:
00:02:30, 00:20:21, 01:23:45 = (01:46:36)\3 = 00:35:32 Avg.

The good reason for my Minutes and Seconds is that some of my events may
have a duration of 5 seconds, 40 seconds, 12 Minutes, or 2 hours. Some of
my reports must display the exact number of seconds of an event. So If I
average three events, 17 seconds, 50 second, 20 second the result is 29
Seconds average.

At the moment, I'm trying to accurately convert my times to Seconds using
CLng(DateDiff("s",[TimeOn],[TimeOff])). Then do my average based on Seconds
Total divided by the Count[OfRecords]. Strangely, the seconds aren't always
accurate.

Thanks for your time.
Sid

I haven't heard a good reason for displaying this as minutes and

seconds.

If
your times are going to average less than 24 hours, then try:
Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss")

Please don't use field names with known issues especially if you are making
them up for "understanding".
--
Duane Hookom
MS Access MVP


Duane
I just got what you meant about naming a field "name", from your first
posting.
My actual field is not named name. I used that to help readers better
understand my scenario.

I guess my question is, if I have say 50 records all with different
elapsed
time/Durations, How can I average (Add them together, Then

divide/50)
if

I'm trying to average the values BEFORE I have all 50 Duration

values?
It

looks as if your suggestion would try to average the elements (hrs, mins,
sec) of One duration, instead of the Aggregate. Unless it has the effect
of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS Duration

Tried this, gives 'Wrong number of arguments' error(with or without the
first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00")) &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAverage

This is looking ominous. Eventually I'll have different groups,

with
the

average time indicated for each group.
Thanks
Sid


This might not be an issue if you stored the duration of time in a
numeric
field as I suggested. You are attempting to Average a text string. You
might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod 60,"\:00") &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to

average
the

value prior to formatting it.

--
Duane Hookom
MS Access MVP


Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS DurationAvg

It gives the same error.
Sid
SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime field
or
name
a field name.


--
Duane Hookom
MS Access MVP


I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't include
my
first
field name as a part of an aggregate function". The error
disappears
when
I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Duane Hookom]

Post the entire SQL of your query. Also, I asked if you would make the
effort to include 4 records and an expected result.

I have taken the time to create a table with your field names, add 40
records with random time values, create and test the query (which works in
my test mdb), and posting the solution.

I have no idea why you think you need CLng().

--
Duane Hookom
MS Access MVP

SELECT Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss") AS AverageTime
produces a 'wrong number of arguments' error. Is CLng the only way to
convert Time to Seconds?

Possibly the issue is to make sure that in the totals query, the Total: for
this column should be "Expression".
This is the SQL that should work:

FROM Task;

If you try this and it doesn't work then come on back with 4 records of
TimeOff and TimeOn values and what you would expect for a result.

--
Duane Hookom
MS Access MVP

I'm really sorry about the 'name' thing...

I may not be seeing the logic, I want to understand, but what purpose would
be served by Averaging each set of TimeOn/TimeOff times? Wouldn't that
result be like: TimeOn(3:00) TimeOff(6:00) = 4:30?

I need to average a batch of DateDiff([TimeOn],[TimeOff]) Results(elapsed
time). As above, the difference/duration would be 03:00:00.

So I need to Average a group of times(time differences) like:
00:02:30, 00:20:21, 01:23:45 = (01:46:36)\3 = 00:35:32 Avg.

The good reason for my Minutes and Seconds is that some of my events may
have a duration of 5 seconds, 40 seconds, 12 Minutes, or 2 hours.

Some

of

If

I

average three events, 17 seconds, 50 second, 20 second the result is 29
Seconds average.

At the moment, I'm trying to accurately convert my times to Seconds using
CLng(DateDiff("s",[TimeOn],[TimeOff])). Then do my average based on Seconds
Total divided by the Count[OfRecords]. Strangely, the seconds aren't always
accurate.

Thanks for your time.
Sid

I haven't heard a good reason for displaying this as minutes and seconds.
If
your times are going to average less than 24 hours, then try:
Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss")

Please don't use field names with known issues especially if you are
making
them up for "understanding".
--
Duane Hookom
MS Access MVP


Duane
I just got what you meant about naming a field "name", from your first
posting.
My actual field is not named name. I used that to help readers better
understand my scenario.

I guess my question is, if I have say 50 records all with different
elapsed
time/Durations, How can I average (Add them together, Then divide/50)
if
I'm trying to average the values BEFORE I have all 50 Duration values?
It
looks as if your suggestion would try to average the elements (hrs,
mins,
sec) of One duration, instead of the Aggregate. Unless it has the
effect
of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS Duration

Tried this, gives 'Wrong number of arguments' error(with or

without
the

first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00")) &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAverage

This is looking ominous. Eventually I'll have different groups, with
the
average time indicated for each group.
Thanks
Sid


This might not be an issue if you stored the duration of time in a
numeric
field as I suggested. You are attempting to Average a text

string.
You

might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod 60,"\:00") &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to average
the
value prior to formatting it.

--
Duane Hookom
MS Access MVP


Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAvg

It gives the same error.
Sid
SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime
field
or
name
a field name.


--
Duane Hookom
MS Access MVP


I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't include
my
first
field name as a part of an aggregate function". The error
disappears
when
I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Sid]

From my original post:
I had a record set that consists of Groups, Names, and
Times:
Group Name TimeOn TimeOff
Task1 Joe Smith GeneralDate GeneralDate
Task2 Steve Wells GeneralDate GeneralDate
Task1 Ed Wilson GeneralDate GeneralDate
Task2 Jane Jones GeneralDate GeneralDate

I simply wanted to average the GeneralDate time difference/duration(elapsed
time) as follows:
Task1 00:03:48(hh:mm:ss)
Task2 00:07:12

Since Avg() always gave syntax errors, my code ended up:
1. Convert time difference to seconds:
SELECT qryTempRptQry.ID, qryTempRptQry.TimeOn, qryTempRptQry.TimeOff,
qryTempRptQry.LogDate, qryTempRptQry.EmpName, qryTempRptQry.Status,
qryTempRptQry.Groups, qryTempRptQry.Wing, DateDiff("s",[TimeOn],[TimeOff])
AS Seconds2
FROM qryTempRptQry
ORDER BY TimeOn
Then I:
2. Count record lines in each report group(by EmpName):
Control source: =Count([TimeOn]).
3. [SumSeconds] from each record, on report.
4. [SumSeconds]\[RecordCount] to get average seconds.
5. Convert seconds to hh:mm:ss format.

There. Simple, reliable, no further Avg() ulcers.

Post the entire SQL of your query. Also, I asked if you would make the
effort to include 4 records and an expected result.

I have taken the time to create a table with your field names, add 40
records with random time values, create and test the query (which works in
my test mdb), and posting the solution.

I have no idea why you think you need CLng().

--
Duane Hookom
MS Access MVP

SELECT Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss") AS AverageTime
produces a 'wrong number of arguments' error. Is CLng the only way to
convert Time to Seconds?

Possibly the issue is to make sure that in the totals query, the

Total:
for

this column should be "Expression".
This is the SQL that should work:
FROM Task;
If you try this and it doesn't work then come on back with 4 records of
TimeOff and TimeOn values and what you would expect for a result.

--
Duane Hookom
MS Access MVP


I'm really sorry about the 'name' thing...

I may not be seeing the logic, I want to understand, but what purpose
would
be served by Averaging each set of TimeOn/TimeOff times? Wouldn't that
result be like: TimeOn(3:00) TimeOff(6:00) = 4:30?

I need to average a batch of DateDiff([TimeOn],[TimeOff]) Results(elapsed
time). As above, the difference/duration would be 03:00:00.

So I need to Average a group of times(time differences) like:
00:02:30, 00:20:21, 01:23:45 = (01:46:36)\3 = 00:35:32 Avg.

The good reason for my Minutes and Seconds is that some of my events may
have a duration of 5 seconds, 40 seconds, 12 Minutes, or 2 hours.

Some

of
my reports must display the exact number of seconds of an event. So

If

I
average three events, 17 seconds, 50 second, 20 second the result is 29
Seconds average.

At the moment, I'm trying to accurately convert my times to Seconds using
CLng(DateDiff("s",[TimeOn],[TimeOff])). Then do my average based on
Seconds
Total divided by the Count[OfRecords]. Strangely, the seconds aren't
always
accurate.

Thanks for your time.
Sid

I haven't heard a good reason for displaying this as minutes and
seconds.
If
your times are going to average less than 24 hours, then try:
Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss")

Please don't use field names with known issues especially if you are
making
them up for "understanding".
--
Duane Hookom
MS Access MVP


Duane
I just got what you meant about naming a field "name", from your first
posting.
My actual field is not named name. I used that to help readers better
understand my scenario.

I guess my question is, if I have say 50 records all with different
elapsed
time/Durations, How can I average (Add them together, Then divide/50)
if
I'm trying to average the values BEFORE I have all 50 Duration values?
It
looks as if your suggestion would try to average the elements (hrs,
mins,
sec) of One duration, instead of the Aggregate. Unless it has the
effect
of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS Duration

Tried this, gives 'Wrong number of arguments' error(with or without
the
first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00")) &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAverage

This is looking ominous. Eventually I'll have different groups, with
the
average time indicated for each group.
Thanks
Sid


This might not be an issue if you stored the duration of time

in

a

numeric
field as I suggested. You are attempting to Average a text string.
You
might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod 60,"\:00") &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to average
the
value prior to formatting it.

--
Duane Hookom
MS Access MVP


Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAvg

It gives the same error.
Sid
SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime
field
or
name
a field name.


--
Duane Hookom
MS Access MVP


I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't
include
my
first
field name as a part of an aggregate function". The error
disappears
when
I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Duane Hookom]

Sorry, but you just lost me. There should be no reason to count and sum and
calculate when Avg works for me. Also, this is the first mention of control
sources plus you still didn't provide actual records. It makes zero
difference what format your date is displayed.

If you are satisfied with your current solution then great. If not, I am
still looking for sample data from you rather than "GeneralDate".

--
Duane Hookom
MS Access MVP
--

From my original post:
I had a record set that consists of Groups, Names, and
Times:
Group Name TimeOn TimeOff
Task1 Joe Smith GeneralDate GeneralDate
Task2 Steve Wells GeneralDate GeneralDate
Task1 Ed Wilson GeneralDate GeneralDate
Task2 Jane Jones GeneralDate GeneralDate

I simply wanted to average the GeneralDate time difference/duration(elapsed
time) as follows:
Task1 00:03:48(hh:mm:ss)
Task2 00:07:12

Since Avg() always gave syntax errors, my code ended up:
1. Convert time difference to seconds:
SELECT qryTempRptQry.ID, qryTempRptQry.TimeOn, qryTempRptQry.TimeOff,
qryTempRptQry.LogDate, qryTempRptQry.EmpName, qryTempRptQry.Status,
qryTempRptQry.Groups, qryTempRptQry.Wing, DateDiff("s",[TimeOn],[TimeOff])
AS Seconds2
FROM qryTempRptQry
ORDER BY TimeOn
Then I:
2. Count record lines in each report group(by EmpName):
Control source: =Count([TimeOn]).
3. [SumSeconds] from each record, on report.
4. [SumSeconds]\[RecordCount] to get average seconds.
5. Convert seconds to hh:mm:ss format.

There. Simple, reliable, no further Avg() ulcers.

Post the entire SQL of your query. Also, I asked if you would make the
effort to include 4 records and an expected result.

I have taken the time to create a table with your field names, add 40
records with random time values, create and test the query (which works in
my test mdb), and posting the solution.

I have no idea why you think you need CLng().

--
Duane Hookom
MS Access MVP

SELECT Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss") AS AverageTime
produces a 'wrong number of arguments' error. Is CLng the only way to
convert Time to Seconds?

Possibly the issue is to make sure that in the totals query, the Total:
for
this column should be "Expression".
This is the SQL that should work:
FROM Task;
If you try this and it doesn't work then come on back with 4 records of
TimeOff and TimeOn values and what you would expect for a result.

--
Duane Hookom
MS Access MVP


I'm really sorry about the 'name' thing...

I may not be seeing the logic, I want to understand, but what purpose
would
be served by Averaging each set of TimeOn/TimeOff times? Wouldn't that
result be like: TimeOn(3:00) TimeOff(6:00) = 4:30?

I need to average a batch of DateDiff([TimeOn],[TimeOff])
Results(elapsed
time). As above, the difference/duration would be 03:00:00.

So I need to Average a group of times(time differences) like:
00:02:30, 00:20:21, 01:23:45 = (01:46:36)\3 = 00:35:32 Avg.

The good reason for my Minutes and Seconds is that some of my even

ts
may

have a duration of 5 seconds, 40 seconds, 12 Minutes, or 2 hours. Some
of
my reports must display the exact number of seconds of an event.

So
If

I
average three events, 17 seconds, 50 second, 20 second the result

is
29

Seconds average.

At the moment, I'm trying to accurately convert my times to Seconds
using
CLng(DateDiff("s",[TimeOn],[TimeOff])). Then do my average based on
Seconds
Total divided by the Count[OfRecords]. Strangely, the seconds aren't
always
accurate.

Thanks for your time.
Sid

I haven't heard a good reason for displaying this as minutes and
seconds.
If
your times are going to average less than 24 hours, then try:
Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss")

Please don't use field names with known issues especially if you are
making
them up for "understanding".
--
Duane Hookom
MS Access MVP


Duane
I just got what you meant about naming a field "name", from your
first
posting.
My actual field is not named name. I used that to help readers
better
understand my scenario.

I guess my question is, if I have say 50 records all with different
elapsed
time/Durations, How can I average (Add them together, Then
divide/50)
if
I'm trying to average the values BEFORE I have all 50 Duration
values?
It
looks as if your suggestion would try to average the elements (hrs,
mins,
sec) of One duration, instead of the Aggregate. Unless it has the
effect
of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS Duration

Tried this, gives 'Wrong number of arguments' error(with or without
the
first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00")) &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAverage

This is looking ominous. Eventually I'll have different groups,
with
the
average time indicated for each group.
Thanks
Sid


This might not be an issue if you stored the duration of

time

in

a
numeric
field as I suggested. You are attempting to Average a text string.
You
might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod 60,"\:00") &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to
average
the
value prior to formatting it.

--
Duane Hookom
MS Access MVP


Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAvg

It gives the same error.
Sid
SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a datetime
field
or
name
a field name.


--
Duane Hookom
MS Access MVP


I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't
include
my
first
field name as a part of an aggregate function". The error
disappears
when
I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Sid]

I may just leave it as is for now. I tried Avg() in every way you, a few
others, and the Access Web suggested but I get an error (posted in previous
replies) stating that my ID field was not a part of my aggregate function.
As I delete the field name( in the error), it just listed the next field in
order in the error. I can update this project later when I learn the magic
behind Avg() in Access 97. The controls & code behind my report sections
were a new twist for me too, I just grabbed the most convenient tool I could
find to get things working. I'll continue to experiment with Avg()...
As for a sample of data, here are two sets:
TimeOn: 4/13/2003 8:30:22 AM. TimeOff: 4/13/2003 8:39:30 AM
8/2/2003 1:21:43 PM 8/2/2003 1:26:54 PM
These are samples of my General Dates stored in my table.
Their elapsed times are: 0:09:08 & 0:05:11 respectively.
The Average elapsed time is: 0:07:09

Thanks.

Sorry, but you just lost me. There should be no reason to count and sum and
calculate when Avg works for me. Also, this is the first mention of control
sources plus you still didn't provide actual records. It makes zero
difference what format your date is displayed.

If you are satisfied with your current solution then great. If not, I am
still looking for sample data from you rather than "GeneralDate".

--
Duane Hookom
MS Access MVP
--

From my original post:
I had a record set that consists of Groups, Names, and
Times:
Group Name TimeOn TimeOff
Task1 Joe Smith GeneralDate GeneralDate
Task2 Steve Wells GeneralDate GeneralDate
Task1 Ed Wilson GeneralDate GeneralDate
Task2 Jane Jones GeneralDate GeneralDate

I simply wanted to average the GeneralDate time difference/duration(elapsed
time) as follows:
Task1 00:03:48(hh:mm:ss)
Task2 00:07:12

Since Avg() always gave syntax errors, my code ended up:
1. Convert time difference to seconds:
SELECT qryTempRptQry.ID, qryTempRptQry.TimeOn, qryTempRptQry.TimeOff,
qryTempRptQry.LogDate, qryTempRptQry.EmpName, qryTempRptQry.Status,
qryTempRptQry.Groups, qryTempRptQry.Wing, DateDiff("s",[TimeOn],[TimeOff])
AS Seconds2
FROM qryTempRptQry
ORDER BY TimeOn
Then I:
2. Count record lines in each report group(by EmpName):
Control source: =Count([TimeOn]).
3. [SumSeconds] from each record, on report.
4. [SumSeconds]\[RecordCount] to get average seconds.
5. Convert seconds to hh:mm:ss format.

There. Simple, reliable, no further Avg() ulcers.

Post the entire SQL of your query. Also, I asked if you would make the
effort to include 4 records and an expected result.

I have taken the time to create a table with your field names, add 40
records with random time values, create and test the query (which

works

in

my test mdb), and posting the solution.

I have no idea why you think you need CLng().

--
Duane Hookom
MS Access MVP


SELECT Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss") AS AverageTime
produces a 'wrong number of arguments' error. Is CLng the only way to
convert Time to Seconds?

Possibly the issue is to make sure that in the totals query, the Total:
for
this column should be "Expression".
This is the SQL that should work:
FROM Task;
If you try this and it doesn't work then come on back with 4

records
of

TimeOff and TimeOn values and what you would expect for a result.

--
Duane Hookom
MS Access MVP


I'm really sorry about the 'name' thing...

I may not be seeing the logic, I want to understand, but what purpose
would
be served by Averaging each set of TimeOn/TimeOff times? Wouldn't
that
result be like: TimeOn(3:00) TimeOff(6:00) = 4:30?

I need to average a batch of DateDiff([TimeOn],[TimeOff])
Results(elapsed
time). As above, the difference/duration would be 03:00:00.

So I need to Average a group of times(time differences) like:
00:02:30, 00:20:21, 01:23:45 = (01:46:36)\3 = 00:35:32 Avg.

The good reason for my Minutes and Seconds is that some of my

even

ts

result

is

29
Seconds average.

At the moment, I'm trying to accurately convert my times to Seconds
using
CLng(DateDiff("s",[TimeOn],[TimeOff])). Then do my average

based

on

Seconds
Total divided by the Count[OfRecords]. Strangely, the seconds aren't
always
accurate.

Thanks for your time.
Sid

I haven't heard a good reason for displaying this as minutes and
seconds.
If
your times are going to average less than 24 hours, then try:
Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss")

Please don't use field names with known issues especially if

you
are

making
them up for "understanding".
--
Duane Hookom
MS Access MVP


Duane
I just got what you meant about naming a field "name", from your
first
posting.
My actual field is not named name. I used that to help readers
better
understand my scenario.

I guess my question is, if I have say 50 records all with
different
elapsed
time/Durations, How can I average (Add them together, Then
divide/50)
if
I'm trying to average the values BEFORE I have all 50 Duration
values?
It
looks as if your suggestion would try to average the elements
(hrs,
mins,
sec) of One duration, instead of the Aggregate. Unless it

has
the

effect
of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS Duration

Tried this, gives 'Wrong number of arguments' error(with or
without
the
first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00")) &
Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAverage

This is looking ominous. Eventually I'll have different groups,
with
the
average time indicated for each group.
Thanks
Sid


This might not be an issue if you stored the duration of

time

in
a
numeric
field as I suggested. You are attempting to Average a text
string.
You
might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod

60,"\:00")

&

Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object is to
average
the
value prior to formatting it.

--
Duane Hookom
MS Access MVP


Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod

60,"\:00")

&

Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00")) AS
DurationAvg

It gives the same error.
Sid
message
SELECT Table.Task, Avg(Table.Duration) AS DurationAverage
FROM Table
Group By Table.Task
ORDER BY Task;

Personally, I would not store a duration of time in a
datetime
field
or
name
a field name.


--
Duane Hookom
MS Access MVP


I have a record set that consists of Groups, Names, and
TimeDurations like:
(hh:mm:ss)
Task1 Joe Smith 00:02:23
Task2 Steve Wells 00:04:23
Task1 Ed Wilson 00:05:12
Task2 Jane Jones 00:10:00

I simply want to average the times as follows:
Task1 00:03:48
Task2 00:07:12

SQL attempt:
SELECT Table.Task, Table.Name, Table.Duration
Avg(Table.Duration) AS DurationAverage
FROM Table
ORDER BY Task

Every syntax I have tried gives an error, "That I didn't
include
my
first
field name as a part of an aggregate function". The error
disappears
when
I
remove my Avg( ) though. Do I need a format?
Any suggestions appreciated.
Sid

 

[Duane Hookom]

I just place your four values into my table and created a query with the
following sql which returns 00:07:09
SELECT Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss") AS AverageTime
FROM Task
GROUP BY Task.Include;

If you have these values in a report and want to average them in a group or
report header or footer, then you can create a text box
Control Source:=Avg([TimeOff]-[TimeOn])
Format: hh:nn:ss
If the above text box is in the report footer with only the two record you
provided, the result will display as 0:07:09
--
Duane Hookom
MS Access MVP

I may just leave it as is for now. I tried Avg() in every way you, a few
others, and the Access Web suggested but I get an error (posted in previous
replies) stating that my ID field was not a part of my aggregate function.
As I delete the field name( in the error), it just listed the next field in
order in the error. I can update this project later when I learn the magic
behind Avg() in Access 97. The controls & code behind my report sections
were a new twist for me too, I just grabbed the most convenient tool I could
find to get things working. I'll continue to experiment with Avg()...
As for a sample of data, here are two sets:
TimeOn: 4/13/2003 8:30:22 AM. TimeOff: 4/13/2003 8:39:30 AM
8/2/2003 1:21:43 PM 8/2/2003 1:26:54 PM
These are samples of my General Dates stored in my table.
Their elapsed times are: 0:09:08 & 0:05:11 respectively.
The Average elapsed time is: 0:07:09

Thanks.

Sorry, but you just lost me. There should be no reason to count and sum and
calculate when Avg works for me. Also, this is the first mention of control
sources plus you still didn't provide actual records. It makes zero
difference what format your date is displayed.

If you are satisfied with your current solution then great. If not, I am
still looking for sample data from you rather than "GeneralDate".

--
Duane Hookom
MS Access MVP
--

From my original post:
I had a record set that consists of Groups, Names, and
Times:
Group Name TimeOn TimeOff
Task1 Joe Smith GeneralDate GeneralDate
Task2 Steve Wells GeneralDate GeneralDate
Task1 Ed Wilson GeneralDate GeneralDate
Task2 Jane Jones GeneralDate GeneralDate

I simply wanted to average the GeneralDate time difference/duration(elapsed
time) as follows:
Task1 00:03:48(hh:mm:ss)
Task2 00:07:12

Since Avg() always gave syntax errors, my code ended up:
1. Convert time difference to seconds:
SELECT qryTempRptQry.ID, qryTempRptQry.TimeOn, qryTempRptQry.TimeOff,
qryTempRptQry.LogDate, qryTempRptQry.EmpName, qryTempRptQry.Status,
qryTempRptQry.Groups, qryTempRptQry.Wing, DateDiff("s",[TimeOn],[TimeOff])
AS Seconds2
FROM qryTempRptQry
ORDER BY TimeOn
Then I:
2. Count record lines in each report group(by EmpName):
Control source: =Count([TimeOn]).
3. [SumSeconds] from each record, on report.
4. [SumSeconds]\[RecordCount] to get average seconds.
5. Convert seconds to hh:mm:ss format.

There. Simple, reliable, no further Avg() ulcers.

Post the entire SQL of your query. Also, I asked if you would make the
effort to include 4 records and an expected result.

I have taken the time to create a table with your field names, add 40
records with random time values, create and test the query (which

works

in
my test mdb), and posting the solution.

I have no idea why you think you need CLng().

--
Duane Hookom
MS Access MVP


SELECT Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss") AS AverageTime
produces a 'wrong number of arguments' error. Is CLng the only

way

to

convert Time to Seconds?

Possibly the issue is to make sure that in the totals query, the
Total:
for
this column should be "Expression".
This is the SQL that should work:
FROM Task;
If you try this and it doesn't work then come on back with 4 records
of
TimeOff and TimeOn values and what you would expect for a result.

--
Duane Hookom
MS Access MVP


I'm really sorry about the 'name' thing...

I may not be seeing the logic, I want to understand, but what
purpose
would
be served by Averaging each set of TimeOn/TimeOff times? Wouldn't
that
result be like: TimeOn(3:00) TimeOff(6:00) = 4:30?

I need to average a batch of DateDiff([TimeOn],[TimeOff])
Results(elapsed
time). As above, the difference/duration would be 03:00:00.

So I need to Average a group of times(time differences) like:
00:02:30, 00:20:21, 01:23:45 = (01:46:36)\3 = 00:35:32 Avg.

The good reason for my Minutes and Seconds is that some of my

even

ts
may
have a duration of 5 seconds, 40 seconds, 12 Minutes, or 2 hours.
Some
of
my reports must display the exact number of seconds of an

event.
So

If
I
average three events, 17 seconds, 50 second, 20 second the

result

is
29
Seconds average.

At the moment, I'm trying to accurately convert my times to Seconds
using
CLng(DateDiff("s",[TimeOn],[TimeOff])). Then do my average

based

on
Seconds
Total divided by the Count[OfRecords]. Strangely, the seconds
aren't
always
accurate.

Thanks for your time.
Sid

I haven't heard a good reason for displaying this as minutes and
seconds.
If
your times are going to average less than 24 hours, then try:
Format(Avg([TimeOff]-[TimeOn]),"hh:nn:ss")

Please don't use field names with known issues especially if you
are
making
them up for "understanding".
--
Duane Hookom
MS Access MVP


Duane
I just got what you meant about naming a field "name",

from
your

first
posting.
My actual field is not named name. I used that to help readers
better
understand my scenario.

I guess my question is, if I have say 50 records all with
different
elapsed
time/Durations, How can I average (Add them together, Then
divide/50)
if
I'm trying to average the values BEFORE I have all 50 Duration
values?
It
looks as if your suggestion would try to average the elements
(hrs,
mins,
sec) of One duration, instead of the Aggregate. Unless it has
the
effect
of
a 'running average'.
I'm just trying to understand.

This is my original SQL for calculating duration:
(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00") AS
Duration

Tried this, gives 'Wrong number of arguments' error(with or
without
the
first Avg):
Avg(DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) Mod

60,"\:00"))

&

Format(Avg(DateDiff("s",[TimeOn],[TimeOff]) Mod

60,"\:00"))

AS

DurationAverage

This is looking ominous. Eventually I'll have different groups,
with
the
average time indicated for each group.
Thanks
Sid


This might not be an issue if you stored the duration of time
in
a
numeric
field as I suggested. You are attempting to Average a text
string.
You
might
want to try:
Format(Avg(DateDiff("n",[TimeOn],[TimeOff]) ) Mod

60,"\:00")

&
Format(Avg(DateDiff("s",[TimeOn],[TimeOff])) Mod 60,"\:00"))

I'm not sure if I got the above correct but the object

is

to

average
the
value prior to formatting it.

--
Duane Hookom
MS Access MVP


Duane
I tried not using the Avg() on my Duration like this:

Avg(Format(DateDiff("n",[TimeOn],[TimeOff]) Mod

60,"\:00")

&
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod

60,"\:00"))

AS Names,
and