Average a Start Time

[MazeWorX]

I want to average a start time but this problem is giving that same kind of pain in the forehead that you get when you eat ice cream too fast.

I have a record set containing employee start times

eg.
8:00
8:15
8:05 etc.

I want to get the average start time .... seems simple enough I have been able to loop through the record sets successfully summing the hours converting them to min and averaging them as a double .. the issue is writing them to a table as a time format. If i pass an integer it drops the mins. As a double i get unexpected results .. not even close

The table field is a Date:Short time format
I have tried FormatDateTime when writing to the field
any suggestions?

Thanks in advance MaZeWorX

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[SkipVought]

Hi,

Select EmpID, Format(Avg([StartTime]),"hh:nn")
From [YourTable]
Group By EmpID

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MazeWorX]

Problem has been solved with code see below for those who may be interested. it converts your time to seconds ... then do what you will with it. In my case I sum and average it then passes it to SecondsToTime and converts it back to time

Public Function TimeToSeconds(dteTime As Date) As Double
TimeToSeconds = ((Hour(dteTime) * 3600) + (Minute(dteTime) * 60) + Second(dteTime))
End Function

Public Function SecondsToTime(ByVal dSeconds As Double) As String
SecondsToTime = Format(DateAdd("s", dSeconds, "00:00:00"), "HH:mm:ss")
End Function

Public Sub TestTime()

Dim TimeAsDouble As Double
Dim TimeAsString As String

TimeAsDouble = TimeToSeconds(Time())
TimeAsString = SecondsToTime(TimeAsDouble)

Debug.Print "Time: "; Time()
Debug.Print "TimeAsDouble: "; TimeAsDouble
Debug.Print "TimeAsString: "; TimeAsString

End Sub

Thanks to John at another board that will go unnamed
Thanks also to Skip. I can use that bit of code further into this project.



Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[dhookom]

I want to get the average start time .... seems simple enough

If your request is accurate, I'm not sure why you made it so difficult. Skip's solution is much simpler and more efficient.

Dates and Times are numbers. You can average them.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

Im looping through 2 record sets at the same time and writing to a third with vba. this is the reason for the solution I chose. If it were a situation that i needed to simply write a query skips solutions is the one I would choose however in my case I am using vba to populate a template with averaged data in all fields for a specific work unit with specific routes attached to the unit. The idea is to populate a continuous form with averaged actual data combined with other data including predicted volumes. the 'Tool' will enable me to predict productivity for tommorrow or next week giving us the ability to plan and react before 'today' comes. Getting the average start time was only one small piece of the puzzle. The other pieces require vba so since i was already there .... Unfortunatly I was unable to post all of the code involved .. if i had it would of better explained the choice I made

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]

 

[dhookom]

You haven't convinced me of the value of converting values back and forth and returning at String from SecondsToTime().

If you are in VBA then you could use:

DAvg("StartTime", "[YourTable]","EmployeeID=" & lngEmpID)

Duane
Hook'D on Access
MS Access MVP