New user question - timesheet query

Discussion in 'Microsoft Access Queries' started by mikey, Sep 4, 2008.

  1. mikey

    mikey Guest

    I have been inputting employee timesheet data into a table for the last 3
    months.
    I have been able to write a query to give the total hours worked in this
    time, but I am struggling to develop a query to give me the average hours
    worked for each employee over the last rolling 12 weeks.
    Please help but keep it simple - thanks.
     
    mikey, Sep 4, 2008
    #1
    1. Advertisements

  2. mikey

    KARL DEWEY Guest

    If your data is stored like this --
    EMP WorkDate WorkHours
    1 2/2/2008 4.5
    2 2/2/2008 9
    1 2/3/2008 7
    2 2/3/2008 6.5

    Then use this query and name EMP_Week_Hrs --
    SELECT EMP Format([WorkDate], "yyyyww") AS Worked, Sum([WorkHours]) AS Hours
    FROM YourTable
    WHERE WorkDate Between Date() And DateAdd("ww", -12, Date())
    GROUP BY EMP, Format([WorkDate], "yyyyww");

    SELECT EMP, [Hours]/12 AS Avg_per_week
    FROM EMP_Week_Hrs;

    --
    KARL DEWEY
    Build a little - Test a little


    "mikey" wrote:

    > I have been inputting employee timesheet data into a table for the last 3
    > months.
    > I have been able to write a query to give the total hours worked in this
    > time, but I am struggling to develop a query to give me the average hours
    > worked for each employee over the last rolling 12 weeks.
    > Please help but keep it simple - thanks.
     
    KARL DEWEY, Sep 4, 2008
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Duane Hookom

    Re: Summary Query - Grouping Timesheet Entries by Week

    Duane Hookom, Sep 19, 2003, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    195
    Duane Hookom
    Sep 22, 2003
  2. Leanne

    Help with timesheet query

    Leanne, Feb 1, 2007, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    150
    Leanne
    Feb 1, 2007
  3. Guest

    New User Query Question - really need some help!!

    Guest, Mar 28, 2007, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    121
    Guest
    Mar 28, 2007
  4. SS

    template for timesheet - calculation wrong

    SS, Jul 9, 2009, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    195
  5. Billiam

    Timesheet

    Billiam, Sep 17, 2009, in forum: Microsoft Access Queries
    Replies:
    20
    Views:
    456
    Billiam
    Sep 20, 2009
Loading...

Share This Page