Re: Syntax to Average a time duration (hh:mm:ss).
From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: 04/29/04
- Next message: fredg: "Re: Printing Queries Properties"
- Previous message: Brian C: "Test for no records returned"
- In reply to: Sid: "Re: Syntax to Average a time duration (hh:mm:ss)."
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 29 Apr 2004 18:57:26 -0500
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
"Sid" <nospam@hotmail.com> wrote in message
news:1091v52j84pq820@corp.supernews.com...
> 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?
>
> "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
> news:#DDCmFZLEHA.2716@tk2msftngp13.phx.gbl...
> > 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
> >
> >
> > "Sid" <nospam@hotmail.com> wrote in message
> > news:1090oe939vros71@corp.supernews.com...
> > > 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
> > >
> > > "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
> > > news:eZ40TEYLEHA.1644@TK2MSFTNGP09.phx.gbl...
> > > > 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
> > > >
> > > >
> > > > "Sid" <nospam@hotmail.com> wrote in message
> > > > news:10907fepaqv54b2@corp.supernews.com...
> > > > > 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
> > > > >
> > > > >
> > > > > "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
> > > > > news:usqXaiULEHA.2532@TK2MSFTNGP10.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > "Sid" <nospam@hotmail.com> wrote in message
> > > > > > news:108vd96toooe8c4@corp.supernews.com...
> > > > > > > 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" <duanehookom@NO_SPAMhotmail.com> wrote in
message
> > > > > > > news:#AKIu7MLEHA.1388@TK2MSFTNGP09.phx.gbl...
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > > > "Sid" <nospam@hotmail.com> wrote in message
> > > > > > > > news:108u46i8sli112b@corp.supernews.com...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: fredg: "Re: Printing Queries Properties"
- Previous message: Brian C: "Test for no records returned"
- In reply to: Sid: "Re: Syntax to Average a time duration (hh:mm:ss)."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
