intersecting times

[Ernst Guckel]

Hello,

We have a database that we are tracking transaction times throughout the
day. We have the day divided into dayparts.

tblDayParts: DayPartID,DayPartStart,DayPartStop

There is also a table with employees schedules in it.

tblSchedules: ScheduleID,EmpID,ScheduleDate,StartTime,StopTime
tblEmployees: EmpID,EmpFirst,EmpLast

What I am looking to do is determine transaction time averages for each
employee based on what occured during their shift. A custom function would
do the trick but i have no idea where to start.

I need total average, average for particulat month, and average for
particular daypart. Any help would be greatly appriciated...

Thanks,
Ernst.

 

[DaveT]

I would start by looking at the KB articles:
How to store, calculate, and compare Date/Time data in Microsoft Access
http://support.microsoft.com/kb/210276/en-us

Functions for calculating and for displaying Date/Time values in Access
http://support.microsoft.com/kb/210604/en-us

 

[Ernst Guckel]

Ok... here's the problem that I face. I know how to compare two values. I
know how to average a set of values. What I don't know how to do is average
a set of values within a comparison. In excel I would create an averageif so
to speak. But in access I have a lot of trouble accessing a set of values at
the same time...

Ernst.

 

[Ernst Guckel]

Ok... so I have managed to create a query that returns an average... I am a
little stumped on how I can load the value into a control on a form... here
is the sql:

this does not seem to work:
Function SOSAverage(iDayPart As Integer, iEmpID As Integer) As Date

Dim sSQL As String

sSQL = "SELECT TOP 1 tblEmployees.EmpID, tblEmployees.EmpFirst,
tblEmployees.EmpLast, tblSchedules.ScheduleDate, " _
& "tblSchedules.StartTime, tblSchedules.StopTime,
Avg(tblSpeedOfService.SOSTotal) AS AvgOfSOSTotal " _
& "FROM tblSpeedOfService, tblEmployees INNER JOIN tblSchedules ON
tblEmployees.EmpID = tblSchedules.EmpID " _
& "GROUP BY tblEmployees.EmpID, tblEmployees.EmpFirst,
tblEmployees.EmpLast, tblSchedules.ScheduleDate, " _
& "tblSchedules.StartTime, tblSchedules.StopTime,
tblSpeedOfService.SOSDate, timeoverlap(1,[ScheduleDate], " _
& "[StartTime],[StopTime]), tblSpeedOfService.DayPartID HAVING
(((tblEmployees.EmpID) = " & iEmpID & ") And " _
& "((tblSpeedOfService.SOSDate) = [ScheduleDate]) And
((TimeOverlap(1, [ScheduleDate], [StartTime], " _
& "[StopTime])) = -1) And ((tblSpeedOfService.DayPartID) = " &
iDayPart & "1)) ORDER BY tblEmployees.EmpLast, " _
& "tblSchedules.ScheduleDate;"

SOSAverage = DoCmd.RunSQL(sSQL)

End Function