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.

 

[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

 

[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