Average time from date column?

[GWB]

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

 

[KARL DEWEY]

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

 

[John Spencer]

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]

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?

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.

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

 

[KARL DEWEY]

Do you want to weight the number by the instance or by the minute, hour,
second?

--
Build a little, test a little.

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?

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.

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

 

[GWB]

John - that is when the the number 10 is entered. So if the next entry is at
8:34:23 pm then 10 has been active for 2 hours
To make it even harder, the day is divided into shifts, and that is how the
result must be shown
For a given date or date spread, what is the average number by shift

 

[Daryl S]

GWB -

It sounds like you want a time-weighted average. You will need to provide
more details on how you want this to work. From your example, the time is
not evenly divided (e.g. not an any exact minute or hour of the day). This
means you will need to calculate the time between each record for a day
before you can take the average. You will need to decide how to calculate
the time for dates that cross midnight (unless you only look at certain time
ranges each day). You also need to decide the significance for this value -
can we only look at minutes or do you need to calculate to the second? If
you only want the average number to be rounded to one decimal place (e.g. 7.4
versus 7.428674393 for an average), then you might be fine with using just
minutes or 10-minute intervals. Finally, does the number associated with a
time represent the 'end' of a period or the 'beginning' of a period. That
is, if I have the entries
6/19/2007 5:34:23 PM 10
6/19/2007 8:26:43 PM 5
Then do I assume that the value was 10 from midnight to 5:34:23 and then 5
from 5:34:23 to 8:26:43? Or do I assume the value 10 goes from 5:34:23 to
8:26:43 and then 5 after 8:26:43?

So, post back how you want this all to work, then we can help with how you
get it to work in Access...

 

[GWB]

Thank you Daryl.

The three shifts are: 3:00pm - 11:00 pm
11:00 pm - 7:00 am
7:00 am - 3:00pm

To the minute would be fine...

The number is associated with the beginning of a period.

Thank you to all for the help

 

[GWB]

I have the following columns:

"ID" which is an autonumber column

"Date" which a general date format column

"Shift" which shows which shift is selected - this corresponds with the
shift times mentioned in my previous post.

"ValueRight". the number field I need the weighted average of.

I want to be able to input a start date and a stop date into the query and
have the query return a table that shows date, shift and average value for
each day and shift within the start and stop date.

Thanks again for all the help

 

[Daryl S]

GWB -

I think you want to write a base query that can match up the start and end
times on one record, and also write a function to return the minutes for a
shift for each pair of date/times. I would also highly recommend you change
your [Date] fieldname to something else so you don't run into problems with
reserved words. In my query below I used [DateTimeValue] for the field name.

For the query, you will want something like this (use your table and field
names):
SELECT tblName.ID, tblName.DateTimeValue, tblName.ValueRight,
ShiftValuesForAveraging_1.DateTimeValue, tblName_1.ValueRight, tblName_1.ID
FROM tblName, tblName AS tblName_1
WHERE (((tblName_1.ID)=(select min([ID]) from [tblName] as [tblName_1] where
[tblName_1]![ID] > [tblName]![ID])))
ORDER BY tblName.ID;

For the function, you will want to pass in the starting date/time, ending
date/time, and shift, and pass out the number of minutes. You can use SELECT
CASE statements to set start and end times for the shift. If the start time
passed in is before the start time of the shift, then use the start time of
the shift in calculating the minutes between the times. Same for the end
time - it can't be after the end time of the shift. You will need to check
for the dates crossing midnight also. If no time falls within the shift,
then return zero minutes, otherwise return the number of minutes in the shift.

Your second query will be based on the first query (which has start and end
times and the value). It will call the function you created to return the
minutes based on the shift you select. This query should also multiply the
value by the number of minutes for that record. Then the third query will be
able to sum up the total number of minutes as well as the time-weighted
values (the value times the number of minutes), and the time-weighted average
is the sum of the time-weighted values divided by the sum of the time.

Whew! A lot to do, but it will work. Post questions as you work through
this.