Prev: Combine multiple tables with different date ranges
Next: Select, update query--same query, different result
From: GWB on 12 Apr 2010 10:13
I have a column that is in general date format, 6/19/2007 5:34:23 PM.
In another column I have a value between 10 and around 50. What I have to do
is get the average of this number by time in a day.

So if the number is 10 all day, then the average is 10. If the number is
changed during the day, say its 5 for half the day then 10 for the other half
of the day, then the number I'm looking for is 7.5.
If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day
then the number would be around 8.5..... if you follow what I'm trying to say.

So far I have been able to get the average number only using a query, but I
dont know how to put the time into the equation...

Thanks for any help

From: KARL DEWEY on 12 Apr 2010 10:51
>>I dont know how to put the time into the equation...
I do not understand your question. You laid out the method like this --
Date_Action: CVDate(DateValue([YourDateField]))
GROUP BY

Daily_Avg: Sum([YourNumberField])/Count([YourNumberField])
EXPRESSION


--
Build a little, test a little.


"GWB" wrote:

> I have a column that is in general date format, 6/19/2007 5:34:23 PM.
> In another column I have a value between 10 and around 50. What I have to do
> is get the average of this number by time in a day.
>
> So if the number is 10 all day, then the average is 10. If the number is
> changed during the day, say its 5 for half the day then 10 for the other half
> of the day, then the number I'm looking for is 7.5.
> If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day
> then the number would be around 8.5..... if you follow what I'm trying to say.
>
> So far I have been able to get the average number only using a query, but I
> dont know how to put the time into the equation...
>
> Thanks for any help
>
From: John Spencer on 12 Apr 2010 12:08
So, what does a time of 5:34:23 PM mean?
Does it mean that
== the value field has been 10 from midnight to that time
OR
== the value field has been 10 from the prior entry (even on another day) to
that time.

Are you assuming 24 hours of production for each day? OR is there some start
and stop time.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

GWB wrote:
> I have a column that is in general date format, 6/19/2007 5:34:23 PM.
> In another column I have a value between 10 and around 50. What I have to do
> is get the average of this number by time in a day.
>
> So if the number is 10 all day, then the average is 10. If the number is
> changed during the day, say its 5 for half the day then 10 for the other half
> of the day, then the number I'm looking for is 7.5.
> If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day
> then the number would be around 8.5..... if you follow what I'm trying to say.
>
> So far I have been able to get the average number only using a query, but I
> dont know how to put the time into the equation...
>
> Thanks for any help
>
From: GWB on 12 Apr 2010 12:31
Yes - right now I'm getting an average of my numberfield for the day I select.

I'll try to explain in a different way.
number entered into the number field is entered at 7:00 am is a value of 5
Number entered at 7:01 am is 10
What is the average number for the day?

"KARL DEWEY" wrote:

> >>I dont know how to put the time into the equation...
> I do not understand your question. You laid out the method like this --
> Date_Action: CVDate(DateValue([YourDateField]))
> GROUP BY
>
> Daily_Avg: Sum([YourNumberField])/Count([YourNumberField])
> EXPRESSION
>
>
> --
> Build a little, test a little.
>
>
> "GWB" wrote:
>
> > I have a column that is in general date format, 6/19/2007 5:34:23 PM.
> > In another column I have a value between 10 and around 50. What I have to do
> > is get the average of this number by time in a day.
> >
> > So if the number is 10 all day, then the average is 10. If the number is
> > changed during the day, say its 5 for half the day then 10 for the other half
> > of the day, then the number I'm looking for is 7.5.
> > If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day
> > then the number would be around 8.5..... if you follow what I'm trying to say.
> >
> > So far I have been able to get the average number only using a query, but I
> > dont know how to put the time into the equation...
> >
> > Thanks for any help
> >
From: KARL DEWEY on 12 Apr 2010 12:52
Do you want to weight the number by the instance or by the minute, hour,
second?

--
Build a little, test a little.


"GWB" wrote:

> Yes - right now I'm getting an average of my numberfield for the day I select.
>
> I'll try to explain in a different way.
> number entered into the number field is entered at 7:00 am is a value of 5
> Number entered at 7:01 am is 10
> What is the average number for the day?
>
> "KARL DEWEY" wrote:
>
> > >>I dont know how to put the time into the equation...
> > I do not understand your question. You laid out the method like this --
> > Date_Action: CVDate(DateValue([YourDateField]))
> > GROUP BY
> >
> > Daily_Avg: Sum([YourNumberField])/Count([YourNumberField])
> > EXPRESSION
> >
> >
> > --
> > Build a little, test a little.
> >
> >
> > "GWB" wrote:
> >
> > > I have a column that is in general date format, 6/19/2007 5:34:23 PM.
> > > In another column I have a value between 10 and around 50. What I have to do
> > > is get the average of this number by time in a day.
> > >
> > > So if the number is 10 all day, then the average is 10. If the number is
> > > changed during the day, say its 5 for half the day then 10 for the other half
> > > of the day, then the number I'm looking for is 7.5.
> > > If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day
> > > then the number would be around 8.5..... if you follow what I'm trying to say.
> > >
> > > So far I have been able to get the average number only using a query, but I
> > > dont know how to put the time into the equation...
> > >
> > > Thanks for any help
> > >
 |  Next  |  Last
Pages: 1 2
Prev: Combine multiple tables with different date ranges
Next: Select, update query--same query, different result