Just noticed your PS about your report for week. You might find the
following of interest. I wrote it for a magazine column a few years back:
Using Access take a simple timesheet table :
CREATE TABLE TimeSheet
(EmployeeID LONG NOT NULL,
DateTimeIn DATETIME NOT NULL,
DateTimeOut DATETIME NOT NULL);
With rows covering one week:
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#09/30/2002 08:45:00#, #09/30/2002 13:00:00#);
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#09/30/2002 13:45:00#, #09/30/2002 18:00:00#);
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#10/01/2002 08:30:00#, #10/01/2002 12:45:00#);
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#10/01/2002 14:00:00#, #10/01/2002 18:05:00#);
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#10/02/2002 09:00:00#, #10/02/2002 13:10:00#);
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#10/02/2002 13:55:00#, #10/02/2002 17:40:00#);
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#10/03/2002 08:30:00#, #10/03/2002 13:00:00#);
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#10/03/2002 13:30:00#, #10/03/2002 17:20:00#);
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#10/04/2002 08:40:00#, #10/04/2002 13:05:00#);
INSERT INTO TimeSheet (EmployeeID, DateTimeIn, DateTimeOut) VALUES(1,
#10/04/2002 13:55:00#, #10/04/2002 17:25:00#);
Most Access users realize that date/time values are actually stored as a
double precision floating point number and its thus possible to do date/time
arithmetic very easily. They reason that simply subtracting the
DateTimeInValue from the DateTimeOut value in each row will give the time
worked. If the resulting value is formatted "hh:nn:ss" this will display the
result in hours, minutes and seconds (the seconds will always be zero of
course with every time-keeping system I've encountered, but could come into
play with a telephone billing system for instance). A query like this seems
to give the correct results:
SELECT EmployeeID,
FORMAT(DateTimeIn,"dddd dd mmmm yyyy AM/PM") AS DateWorked,
FORMAT(DateTimeOut - DateTimeIn,"hh:nn:ss") AS TimeWorked
FROM TimeSheet
ORDER BY DateTimeIn;
Producing:
EmployeeID DateWorked TimeWorked
--------------------------------------------------------------------------
1 Monday 30 September 2002 AM 04:15:00
1 Monday 30 September 2002 PM 04:15:00
1 Tuesday 01 October 2002 AM 04:15:00
1 Tuesday 01 October 2002 PM 04:05:00
1 Wednesday 02 October 2002 AM 04:10:00
1 Wednesday 02 October 2002 PM 03:45:00
1 Thursday 03 October 2002 AM 04:30:00
1 Thursday 03 October 2002 PM 03:50:00
1 Friday 04 October 2002 AM 04:25:00
1 Friday 04 October 2002 PM 03:30:00
It would seem logical that to get the hours worked for the whole week. all
that's necessary is to SUM the half-daily time differences and format the
result in the same way:
SELECT EmployeeID,
FORMAT(DateTimeIn,"ww") AS WeekNumber,
FORMAT(SUM(DateTimeOut - DateTimeIn),"hh:nn:ss") AS TimeWorked
FROM TimeSheet
GROUP BY EmployeeID, FORMAT(DateTimeIn,"ww");
which produces:
EmployeeID WeekNumber TimeWorked
--------------------------------------------------------------------------
1 40 17:00:00
Now this employee is not going to be too happy when they get their pay
cheque for week 40 as they only seem to have worked 17 hours. How come?
What many people don't realize is that there is no such thing in Access as a
time value independent of a date. Every date/time value is a unique point in
time. If the TimeWorked column was formatted as a full date/time it would be
30/12/1899 17:00:00. The date part has simply been lost in the formatting as
"hh:nn:ss". When the differences between the underlying floating point
numbers are summed they give the correct total time for the week in days
worked. If the formatting of the TimeWorked column is omitted this can be
seen:
SELECT EmployeeID,
FORMAT(DateTimeIn,"ww") AS WeekNumber,
SUM(DateTimeOut - DateTimeIn) AS TimeWorked
FROM TimeSheet
GROUP BY EmployeeID, FORMAT(DateTimeIn,"ww");
produces:
EmployeeID WeekNumber TimeWorked
--------------------------------------------------------------------------
1 40 1.70833333332848
i.e. approx 1.7 days have been worked in the week ( that's 1.7 * 24 hours,
not 1.7 * 7.5 or whatever the nominal working day for the organisation is).
How then do we get the total time worked in the week expressed as hours,
minutes and seconds? What I usually do, however, when working with large
units (of any kind, not just time) which have to be broken down into smaller
units is to first convert the value into the smallest unit required and then
calculate the larger units and remainders from that. In this case the
following function is used , which takes the value in seconds as its argument
and returns a string in the format "hh:nn:ss":
Function SecsToHours(lngSecs As Long) As String
Const SECONDS_IN_DAY As Long = 86400
Dim lngWholeDays As Long, lngSeconds As Long
If lngSecs < SECONDS_IN_DAY Then
SecsToHours = Format(lngSecs / SECONDS_IN_DAY, "hh:nn:ss")
Else
lngWholeDays = Int(lngSecs / SECONDS_IN_DAY)
lngSeconds = lngSecs Mod SECONDS_IN_DAY
SecsToHours = ((lngWholeDays * 24) + Format(lngSeconds /
SECONDS_IN_DAY, "hh")) & _
Format(lngSeconds / SECONDS_IN_DAY, ":nn:ss")
End If
End Function
In a query the DateDiff function is used to get the times in seconds and
this value is passed into the function:
SELECT EmployeeID,
FORMAT(DateTimeIn,"ww") AS WeekNumber,
SecsToHours(SUM(DateDiff("s",DateTimeIn, DateTimeOut))) AS
TimeWorked
FROM TimeSheet
GROUP BY EmployeeID, FORMAT(DateTimeIn,"ww");
to give:
EmployeeID WeekNumber TimeWorked
