It depends on how you are recording the hours worked. If each row in a table
has columns for the employee, the date and the number of hours worked its
simply a question of grouping by employee and summing the hours worked where
the date falls between the start and end dates of the pay period, which will
be in another table, e.g.
PARAMETERS
[Enter start date for pay period:] DATETIME;
SELECT PayPeriod, EmployeeID, SUM(HoursWorked) AS TotalHours
FROM WorkSchedule INNER JOIN PayPeriods
ON (WorkSchedule.WorkDate BETWEEN
PayPeriods.StartDate AND PayPeriods.EndDate)
GROUP BY PayPeriod, EmployeeID
WHERE PayPeriods.StartDate = [Enter start date for pay period:];
which would prompt for the start date of the pay period at runtime.
If on the other hand the employee's work schedule has columns of date/time
data type for the start and end of each period worked then you need to
compute the hours from the times. Just how this would be done depends on
whether the start and end times have been entered as full date/time values
including the data, or just as times, in which case there would be a separate
WorkDate column for the date. It would also have to take account of whether
the start and end times for each period are for the whole day, in which case
an allowance for break time(s) would have to be made, or as separate periods
for the individual periods worked per day between breaks.
All of these factors are easily handled but we'd need more information to say
just how.
Ken Sheridan
Stafford, England