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 > > >