Calculations between records.

[Victic31]

Hi,
Sorry about the odd sounding title of this question.
Whilst I have been using Access for a number a years, I can only class
myself as a novice when is comes to programming access so please bear with me.

I have a table which records data from employee timesheets. Each day being a
seperate record. Whilst creating a calculated field to show Duty time for a
particular day is straight forward, I am now stuck.
I need to be able to show Off Duty time. The number of days, hours and
minutes between End_of_Duty on one day, and Start_of_Duty on the next day.

This has got me completely stuck and it's driving me up the wall. No doubt
the solution is relatively simple (but not for me).

Any help with this would be very much appreciated.

Many thanks in advance

 

[KARL DEWEY]

Search on Ranking in a Group to number the query records sequencialy.

Then create a query that has the above query twice in the space above the
grid shown in design view. Access will add a sufix the the name of second
instance like this -- MyQuery_1
Join them on the employee identification information.
Set criteria of MyQuery_1.Rank to [MyQuery].[Rank] +1 so that you are using
the next record of the second query copy to compare with the first record of
the first query.
Then you can compare the DateTime's of the two records.

 

[Steve Sanford]

It would help to know the table name and the field names plus some data and
expected results.....


You can do this using a query/subquery and the DateDiff() function. For an
example of the query, see Allen Browne's page at:

http://allenbrowne.com/subquery-01.html#AnotherRecord


I created a table named "TimeTable" with a structure of:

TT_ID AutoNumber, PK
Emp_ID_FK Number, long (to Emp table)
Start_of_Duty Date/Time
End_of_Duty Date/Time

I entered test data of:

TT_ID,Emp_ID_FK,Start_of_Duty,End_of_Duty
1, 1,1/1/2009 8:00:00,1/1/2009 18:00:00
2, 1,1/2/2009 8:00:00,1/2/2009 17:30:00
3, 1,1/3/2009 9:30:00,1/3/2009 17:45:00
4, 2,1/1/2009 8:00:00,1/1/2009 18:00:00
5, 2,1/2/2009 8:00:00,1/2/2009 17:30:00
6, 2,1/3/2009 9:30:00,1/4/2009 17:45:00
7, 3,1/1/2009 10:00:00,1/1/2009 16:00:00
8, 3,1/2/2009 7:00:00,1/2/2009 18:30:00

Then I created this query:

SELECT TimeTable.Emp_ID_FK, TimeTable.Start_of_Duty, TimeTable.End_of_Duty,
(SELECT TOP 1 TT.End_of_Duty FROM TimeTable AS TT WHERE TT.Emp_ID_fk =
TimeTable.Emp_ID_fk AND TT.Start_of_Duty < TimeTable.Start_of_Duty ORDER BY
TT.Emp_ID_FK, TT.Start_of_Duty DESC ) AS PriorValue,
DateDiff("h",[start_of_duty],[end_of_duty]) AS HrsWorked,
DateDiff("h",[priorvalue],[start_of_duty]) AS HrsOff
FROM TimeTable
ORDER BY TimeTable.Emp_ID_FK, TimeTable.Start_of_Duty;

The results (in days) are (last two columns of the query):

HrsWorked HrsOff
10
9 14
8 16
10
9 14
32 16
6
11 15


If you want parts of an hour (ie 10.5 hours), you will need to calculate
the number of minutes and convert to decimal hours or hours & minutes or use
the format function. See

http://msdn.microsoft.com/en-us/library/dd569711.aspx

This is for A2K7, but can be used in A2K3.

NOTE: In the code, there is a variable named "month". This is a reserved
word in Access. I would suggest using a "v" (for variable) as a prefix for
all variables in the code to prevent the use of reserved words. (Ex. vYears,
vMonth, vWeeks, ...)


HTH

 

[KenSheridan via AccessMonster.com]

The following function will return the difference between two date/time
values, with the option of showing the result as hours and days, or the total
hours, the latter being the default (in both cases with minutes and seconds).
Paste the function into any standard module in the database:

Public Function TimeDuration( _
varFrom As Variant, _
varTo As Variant, _
Optional blnShowDays As Boolean = False)

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double

If Not IsNull(varFrom) And Not IsNull(varTo) Then
dblDuration = varTo - varFrom

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If
End If

End Function


You can then call it in a query like so:

SELECT EmployeeID,Start_of_Duty,End_of_Duty,
TimeDuration(Start_of_Duty,End_of_Duty) As DutyTime,
TimeDuration(End_of_Duty,
(SELECT Start_of_Duty
FROM TimeSheet AS TS2
WHERE TS2.EmployeeID = TS1.EmployeeID
AND End_of_Duty =
(SELECT MIN(End_of_Duty)
FROM TimeSheet AS TS3
WHERE TS3.EmployeeID = TS1.EmployeeID
AND TS3.End_of_Duty > TS1.End_of_Duty)),TRUE)
AS OffTime
FROM Timesheet AS TS1
ORDER BY EmployeeID, Start_of_Duty;

The duty time is simply the time duration between the start and end times
each day of course. The off time is computed by passing the end time and the
start time of the next day worked by the employee in question into the
function. The start time for the following day is returned by the first
subquery, which is correlated with the outer query on EmployeeID and is
itself restricted by the second subquery which returns the earliest (MIN)
start time and is again correlated with outer query on Employee ID and on the
end time being later than the outer query's end time.

Ken Sheridan
Stafford, England