RE: What's the best way.

[Ken Sheridan]

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

"Rodney" wrote:

> I have imported some time sheet data from SAP. It has the usual information
> but the Hours are recorded by Work Datewith the chrge information. I'd like
> to execute a query or script to sum data by Week and display the results. I
> do have a table of Week Beginning dates and Week Ending Dates.
> I'd appreciate a point in the right direction.