# Thread: Overtime Pay project (2003)

1. ## Overtime Pay project (2003)

I have had to take over an Access project for an associate who departed (literally). I have limited familiarity with Access although I have done many analyses using Excel. The project is to compute the amount of overtime worked by approximately 300 workers over a four year period and determine how much overtime compensation they are due. I reviewed the thread at 227755, et seq and am still unsure how to proceed.

I have an Access file with three tables: 1) daily hours worked listed by employee number and date (approximately 73,000 records); 2) weekly gross pay by employee number over the four year period; and 3) employee data containing employee number, employee name, hire date and termination date.

I have done a query calculation to compute each worker's daily overtime (i.e., hours greater than 8 (OT2) and hours greater than 12 (OT2). To compute each worker's weekly overtime, if any, I conceptually believe these two calculations will determine the weekly overtime: 1) Total Hours for a Week minus OT1 minus OT2 equals Weekly Standard Hours; 2) IIF(Weekly Standard Hours > 40, Weekly Standard Hours minus 40, 0).

My question is how do I calculate each worker's weekly totals for hours worked, OT1 and OT2?

2. ## Re: Overtime Pay project (2003)

WeekDate: [DateField]-WeekDay([DateField],2)

where DateField is the name of the date field in your table of daily hours worked.
Save the query.
Create a new query based on the one you just saved.
Add WeekDate, HoursWorked, OT1 and OT2 to the query grid.
Select View | Totals to make the query into a Totals query.
Leave the Total option for WeekDate as Group By, and change it for the others to Sum.
You now have a query that totals HoursWorked, OT1 and OT2 by week. You should be able to use this as basis for further calculations.

3. ## Re: Overtime Pay project (2003)

Hans,

Thank you for your prompt response. I have added the Weekdate calculation. However, I am unaware how I "create a new query based on the one you just saved." Do I just re-open the initial query or select "Create a new query in Design view" and somehow refer to the initial query?

4. ## Re: Overtime Pay project (2003)

If you select "Create a new query in design view", Access will display the "Add table" dialog. This dialog has three tabbed pages: Tables, Queries and Both. Activate the Queries tab, select the query you just saved, and click Add, then Close.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•