Results 1 to 5 of 5
  1. #1
    zx3 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    47

    Question A complex sql query to summaries hours worked on jobs grouped by employees and dates

    Hi all, I m working on this table


    emp date job hours
    a 01/01/2014 1 1
    b 02/01/2014 1 2
    c 02/01/2014 2 1
    a 04/01/2014 1 4
    b 02/01/2014 2 1
    c 04/01/2014 2 3
    - Each line records the hours worked on a job by specific emp on a date,
    - On odd number dates only job1 can be chose,
    - On even number dates emp can choose either job or both job1 and job2, each line record will only show time for 1 job even both jobs are worked.
    - No work no record
    - there is no pattern of emp or date in above listing

    Expecting the following table as result
    emp date job1 job2
    a 01/01/2014 1 x
    a 04/01/2014 4
    b 02/01/2014 2 1
    c 02/01/2014 1
    c 04/01/2014 3

    since 01/01/2014 is an odd number date so job2 didn't exist, the table has to show an "x" in job2 on that date, even number date with no hours recorded will be left blank.

    emp b has 2 lines of hours works and will be summed as 1 line.

    records of all emps will be listed by their names sorted and then dates that worked in Ascending orders

    Please help.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    don't use reserved words (Date) as object names in your database, it will cause you problems. I changed your 'date' field to WORKDATE.

    Code:
    SELECT Emp, WorkDate, Sum(iif([job] = 1, hours, 0)) as Job1Hours, sum(iif([job] = 2, hours, 0)) as Job2Hours FROM TABLENAME GROUP BY Emp, WorkDate
    You can't mix alpha and number values in the same column of data and expect it to be treated like a number so I didn't insert the 'x' value.

  3. #3
    zx3 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    47
    Thanks rpeare for the reply, it works, and about the 'x' value, may be i can use a null(blank) value instead or even a 9999 value representing the absence of job2 (if the query is too complicated), can we have another query including that feature? the reason is i want to identify those lines that are job1 only at a glance and job1 only date(on real life project) is on certain days in a week.

    Please help.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if your goal is to end up putting this on a report you can handle a visual reminder that it's not a valid time slot with conditional formatting. otherwise you can change the formula for job2hours to something like

    iif(([job] = 2 and (datepart("d", [WorkDate]) mod 2) = 1, null, [hours])

  5. #5
    zx3 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    47
    Thanks rpeare, i'll try that.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 03-01-2013, 11:55 AM
  2. calculate rate * hours worked
    By hamish mather in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 06:14 AM
  3. Tracking education hours for employees
    By sephiroth2906 in forum Access
    Replies: 3
    Last Post: 11-15-2011, 03:22 PM
  4. Sum hours worked, grouped by dep't
    By goodfood in forum Queries
    Replies: 3
    Last Post: 05-05-2011, 06:11 AM
  5. Replies: 2
    Last Post: 02-12-2011, 12:46 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums