Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Overtime Pay project (2003)

    Add the following calculated column to your query:

    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. #3
    Lounger
    Join Date
    Apr 2001
    Location
    USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •