You can join the tables by week and group by the week beginning dates, then
sum the time values per week. Just how you sum them will depend on how the
times are stored. If they are simply the number of hours worked per day then
a simple summation will do, e.g. for the total hours per week:
SELECT [Week Beginning],
SUM([Hours Worked] AS [Total Hours]
FROM [Time Sheet], [Weeks]
WHERE [Work Date] BETWEEN
[Week Beginning] AND [Week Ending]
GROUP BY [Week Beginning];
You can of course further group the query on other column(s) to produce
subtotals per week.
If the times are recorded as start and end times per day as values of
date/time data type then you'll need to sum the result of one subtracted from
the other, and then use a function such as the following, which returns the
values in hours:minutes:seconds (and optionally days) format:
Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String
Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
dblDuration = dtmTo - dtmFrom
'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 Function
So if the times are stored as start and end times:
SELECT [Week Beginning],
TimeDuration(SUM([StartTime]), SUM([End Time]))
AS [Total Hours]
FROM [Time Sheet], [Weeks]
WHERE [Work Date] BETWEEN
[Week Beginning] AND [Week Ending]
GROUP BY [Week Beginning];
Ken Sheridan
Stafford, England
