Indentifying a payperiod

I want to Sum the number of hrs A has worked in a pay period. I have a chart
that gives me the start and end date of the pay periods, and i have A's work
schedule. what I am trying to figure out is how to get access to identify the
correct pay period based on the dates.

Try this --
SELECT [Pay_Period].[WeekNUM], [Employee], Sum([Work Schedule].[WorkHours])
AS Pay_Period_Hours
FROM Pay_Period, [Work Schedule]
GROUP BY [Pay_Period].[WeekNUM], [Employee]
HAVING [Work Schedule].[Work_Day] Between [Pay_Period].[Start date] AND
[Pay_Period].[End Date];

--
Build a little, test a little.


"KWhamill" wrote:

I want to Sum the number of hrs A has worked in a pay period. I have a chart
that gives me the start and end date of the pay periods, and i have A's work
schedule. what I am trying to figure out is how to get access to identify the
correct pay period based on the dates.

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

KWhamill wrote:
I want to Sum the number of hrs A has worked in a pay period. I have a chart
that gives me the start and end date of the pay periods, and i have A's work
schedule. what I am trying to figure out is how to get access to identify the
correct pay period based on the dates.

--
Message posted via http://www.accessmonster.com