### Title

Get sum of hours since previous Monday for an employee

### Post

I am trying to create a calculation field on a time log database but I've run into a problem.

Picture a table with the following fields. EmployeeID, Hours (time data type) DateWorked, HourlyRate, HourlyOvertimeRate.

OK, that's just the basics but here's what I have done. I created a calculation field that returns the Monday before the DateWorked value to determine the week's start date. Now I want a calculation that will return the sum of hours for that employee since the value of the Previous Monday field. Then I can determine if the new record puts the employee into overtime (over 40 hours since Monday) and calculate the cost of this hour entry based on his straight time hourly rate and overtime hourly rate.

Keep in mind there may be several records for an employee in a single day broken down by different jobs.

So say an employee has 39 hours already since monday and a new record is created for 4 hours. I need to create a calculation field that will do something like 1 hour times straight time rate + 3 hours times overtime rate. (with of course consideration for minutes as the hour field is a time datatype.

Any suggestions would be greatly appreciated!

Is this calculation needed from the context of a table of employees or the context of your timeLog table?

Either way, a relationship can be set up that matches by employeeID and the calculated Monday date to match to all records for the same employee and in the same week.

The following example assumes TimeLog as the context:

TimeLog::EmployeeID = TimeLog|SameEmpWeek::EmployeeID AND

TimeLog::cMondayDate = TimeLog|SameEmpWeek::cMondayDate

From TimeLog, Sum ( TimeLog|SameEmpWeek::Hours ) will return the total hours worked by that employee over the same week.

TimeLog|SameEmpWeek::sTotalHours, with sTotalHours defined as a summary field computing total hours, if also referenced from TimeLog would show the same total.

Caulkins Consulting, Home of Adventures In FileMaking