Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    35

    Question Unanswered: Creating a Total_hours query..(may be a little tricky)

    Heres the setup:
    I have a table called Emp_Clock which stores:
    Employee ID (pk)
    Date (pk)
    Time in
    Time out
    Destination (fk)

    Note: an employee will work more than once in a day, but never in the same place (Destination).

    I want to make a parameter query (dateStart and dateEnd) that will total the hoursWorked in one day and then total the dailyHours to come up with weeklyHours. For readability I also want to then display Regular Hours (<=40) and Overtime horus (if overtime applies)

    So far Ive been having trouble because of the parameters. Ive tried making a query of a query where the first query summed the hoursWorked to get dailyHours, and the second query would sum the daily hours to get weeklyHours. The problem with this is that now I have two startDate Parameters and two endDate parameters.

    What I want for an end product is to print a report with :
    Employee ID, Total Hours worked, Regular Hours, Overtime Hours

    And maybe having the Paramters as a header on the report.

    Hopefully I haven't left anything out.
    "My Idea is that everyone should be required to use small fonts. That way we'll save disk space.
    ~Asok.

  2. #2
    Join Date
    Dec 2002
    Posts
    5

    Re: Creating a Total_hours query..(may be a little tricky)

    Hi okiiyama ,
    The following query should work based on the table structure you have given,


    SELECT EmployeeId, sum((hour(timeout)-hour(timein))) AS TotalHours
    FROM Emp_Clock AS e
    WHERE e.Date between #StartDate# and #EndDate#
    GROUP BY EmployeeId

    You don't require multiple queries for this.

    Once you get the TotalHours from the query,
    Calculating Regular Hours & Overtime Hours (totalhours-regularhours) will just be easy.

    The syntax sum((hour(timeout)-hour(timein))) will round the hour to 1 hour, check out for datefunctions to get exact no.of hours.

    otherwise the query should work fine.

    Hope this helps.


    Originally posted by okiiyama
    Heres the setup:
    I have a table called Emp_Clock which stores:
    Employee ID (pk)
    Date (pk)
    Time in
    Time out
    Destination (fk)

    Note: an employee will work more than once in a day, but never in the same place (Destination).

    I want to make a parameter query (dateStart and dateEnd) that will total the hoursWorked in one day and then total the dailyHours to come up with weeklyHours. For readability I also want to then display Regular Hours (<=40) and Overtime horus (if overtime applies)

    So far Ive been having trouble because of the parameters. Ive tried making a query of a query where the first query summed the hoursWorked to get dailyHours, and the second query would sum the daily hours to get weeklyHours. The problem with this is that now I have two startDate Parameters and two endDate parameters.

    What I want for an end product is to print a report with :
    Employee ID, Total Hours worked, Regular Hours, Overtime Hours

    And maybe having the Paramters as a header on the report.

    Hopefully I haven't left anything out.

Posting Permissions

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