rigby said:
Hi
I need to bring up some totals in a query based on 2 dates which i would
like to be prompted to enter for the query to lookup. There is only one date
column with a number of entries for each date. Each entry has a 'Duty' too
which I would like to have specified too. So, for instance, I would like to
be asked from which date, to which date , and for what duty, and then to get
a result of another column. ??? make any sense? I have posted anther question
earlier but my boss wishes for a different query now.
I have a logsheet with 'Date' 'Time on Duty' 'Time off Duty' 'Hours Worked'
'Duty'. the logsheet has numerous entries as each logsheet contains a number
of days of records each for a different officer and the duties change every
few days. So i would like to be able to lookup between certain dates, the
total amount of hours worked, doing a specific duty. i want to maybe find out
how many hours were worked between 01 december '05 and the 10th december '05
doing 'in port' duties. etc.
Thank you for your time.
Kind regards
Rigby
Rigby
Tables:
Note: MS Access "DATETIME" data types store both date and time, so
there is no need for three separate "Date" and "Time" columns.
Also, hours worked can be calculated as needed from the beginning
and end of the duty period, and should not be stored.
Descriptions & DDL to follow:
Description:
Employees:
EmployeeID INTEGER -- PK
FName TEXT(48)
LName TEXT(48)
DDL:
CREATE TABLE Employees
(EmployeeID INTEGER
,FName TEXT(48)
,LName TEXT(48)
,CONSTRAINT pk_Employees
PRIMARY KEY (EmployeeID)
)
Description:
Duties:
DutyID INTEGER -- PK
Duty TEXT(48)
DDL:
CREATE TABLE Duties
(DutyID INTEGER
,Duty TEXT(48)
,CONSTRAINT pk_Duties
PRIMARY KEY (DutyID)
)
Description:
Logsheet:
LogsheetID INTEGER -- PK
EmployeeID INTEGER -- FK to Employees
DutyID INTEGER -- FK to Duties
OnDuty DATETIME
OffDuty DATETIME
DDL:
CREATE TABLE Logsheet
(LogsheetID INTEGER
,EmployeeID INTEGER
,DutyID INTEGER
,OnDuty DATETIME
,OffDuty DATETIME
,CONSTRAINT pk_Logsheet
PRIMARY KEY (LogsheetID)
,CONSTRAINT fk_Logsheet_Employees_EmployeeID
FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)
,CONSTRAINT fk_Logsheet_Duties_DutyID
FOREIGN KEY (DutyID)
REFERENCES Duties (DutyID)
)
Note: Logsheet should probably have a CHECK constraint to make sure
OffDuty is always greater than OnDuty, but it is omitted here (it
would require VBA code to implement). Basically, the assumption is
going to be that OffDuty is always greater than OnDuty.
Sample Data:
Employees
1, Jay, Smith
2, Dee, Smith
3, Jeff, Smith
Duties:
1, Car Patrol
2, Foot Patrol
Logsheet:
1, 1, 1, 12/01/2005 23:00:00, 12/02/2005 09:00:00
2, 1, 1, 12/02/2005 23:00:00, 12/03/2005 09:00:00
3, 1, 2, 12/03/2005 23:00:00, 12/04/2005 09:00:00
4, 2, 2, 12/01/2005 14:00:00, 12/02/2005 00:00:00
5, 2, 2, 12/02/2005 14:00:00, 12/03/2005 00:00:00
6, 2, 2, 12/03/2005 14:00:00, 12/04/2005 00:00:00
7, 3, 1, 12/01/2005 08:00:00, 12/01/2005 18:00:00
8, 3, 1, 12/02/2005 08:00:00, 12/02/2005 18:00:00
9, 3, 1, 12/03/2005 08:00:00, 12/03/2005 18:00:00
VBA Function:
You use the DateDiff() functions, ala:
DateDiff("s", #12/01/2005 13:01#, #12/01/2005 22:59#)
To determine the difference in seconds.
You can divide the number of seconds by 3600 to get the hours, and
use MOD operator to get the remaining seconds to determine the
number of minutes, and then again for the seconds remaining after
the minutes. (You won't be storing these results in a table, they
will exist only for the report.) I do realize you said only
"hours", but you can cut out the code for minutes and seconds if you
don't need it.
It will be a nested and long expression, so I would put it in a VBA
function, both to hide the code and to allow for simpler code.
Public Function DutyTime(SecondsOnDuty As Long) As String
' Created: 12/28/2005
'
' Function to determine the elapsed time, in hours, minutes,
' and seconds, between two given times.
'
' SecondsOnDuty must be greater than 0
'
' When this function returns "-1", SecondsOn Duty was less
' than 1, and an error has occured.
'
Dim HoursFromSeconds As Long
Dim MinutesFromSeconds As Long
Dim SecondsRemaining As Long
If SecondsOnDuty < 1 Then
DutyTime = "-1"
Else
HoursFromSeconds = SecondsOnDuty \ 3600
MinutesFromSeconds = (SecondsOnDuty Mod 3600) \ 60
SecondsRemaining = (SecondsOnDuty Mod 3600) Mod 60
DutyTime = Format(HoursFromSeconds, "00") & ":" & _
Format(MinutesFromSeconds, "00") & ":" & _
Format(SecondsRemaining, "00")
End If
End Function
Query:
SELECT E1.LName & ", " & E1.FName
AS OfficerName
,D1.Duty
,DutyTime(SUM(DateDiff("s", L1.OnDuty, L1.OffDuty)))
As TimeOnDuty
FROM (Logsheet AS L1
INNER JOIN
Duties AS D1
ON L1.DutyID = D1.DutyID)
INNER JOIN
Employees AS E1
ON L1.EmployeeID = E1.EmployeeID
GROUP BY E1.LName & ", " & E1.FName
,D1.Duty
Results:
OfficerName, Duty, TimeOnDuty
Smith, Dee, Foot Patrol, 30:00:00
Smith, Jay, Car Patrol, 20:00:00
Smith, Jay, Foot Patrol, 10:00:00
Smith, Jeff, Car Patrol, 30:00:00
I believe this meets your original requirement of showing the time
worked by officer and duty.
To meet your "between dates" requirement, it's a bit more
complicates.
You can add a WHERE clause with parameters:
WHERE L1.OnDuty >= CDate([Start Date and Time Range])
AND L1.OffDuty <= CDate([End Date and Time Range])
Essentially, this means (at least as far as I can tell, that either
whole shifts will, or won't, be included. If you want partial
shifts to be included, you'll need to re-write the WHERE conditions
(I'll leave that to you).
Note: Duane Hookum mentioned using a Form for data entry of the
times (instead of the parameters I have used above). This is the
correct way of going about it, but I have used parameters above
anyway because you can test the results easily before constructing
your Form.
If I enter, 12/02/2005 00:00:00 and 12/03/2005 00:00:00 (with the
WHERE clause above added into the above query), I get:
OfficerName, Duty, TimeOnDuty
Smith, Dee, Foot Patrol, 10:00:00
Smith, Jeff, Car Patrol, 10:00:00
A check of the sample data will show that the results are correct.
I hope that this will be of assitance to you in solving your
problem.
Sincerely,
Chris O.
