Results 1 to 2 of 2

Thread: Help !!

  1. #1
    Join Date
    Dec 2010
    Posts
    6

    Unanswered: Help !!

    I would like to create a query that shows me all the employees with their hours per day for varios day (in the same table if possible)eg.

    ...................01/01/2011 02/02/2011 etc
    Employee01 .....4 hrs 5 hrs
    Employee02 .....5 hrs 3 hrs
    etc

    My main tables are:
    ================================================== =====

    EmployeeTb
    EmployeeID (PK)
    Name
    LastName
    __________
    ShiftCardDetailTb
    ShiftDetailCardID (PK)
    EmployeeID (FK)
    ShiftID (FK)
    HoursperShift
    ___________
    ShiftCardTb
    ShiftCardID (PK)
    Shiftdate

    I have been trying to get in MS access a query that shows me total of hours per worker with their respective dates all I could get was.

    SELECT EmployeeTb.EmployeeID, EmployeeTb.FirstName, EmployeeTb.LastName, ShiftCardDetailTb.HourPerJob, ShiftCardTb.ShiftDate
    FROM ShiftCardTb INNER JOIN (EmployeeTb INNER JOIN ShiftCardDetailTb ON EmployeeTb.EmployeeID = ShiftCardDetailTb.EmployeeID) ON ShiftCardTb.ShiftCardID = ShiftCardDetailTb.ShiftCardID
    WHERE (((ShiftCardTb.ShiftDate) Between #1/3/2011# And #1/16/2011#))
    GROUP BY EmployeeTb.EmployeeID, EmployeeTb.FirstName, EmployeeTb.LastName, ShiftCardDetailTb.HourPerJob, ShiftCardTb.ShiftDate
    ORDER BY EmployeeTb.EmployeeID, ShiftCardTb.ShiftDate;


    Anyone? any idea if there is anyway I can do that?? or alternative ideas....?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It looks to me like you want to create a CrossTab query. If you are going to list the dates across the top then that is what I think you will need.

    When you are Grouping By the employee information, you will probably want to be Summing the time card information. What I would suggest is to break the query into two queries. The first query would collect the timecard information. Sum up the hours and have the following fields:

    EmpID
    Date
    HoursWorked (which would be the sum of the hours)

    Then in another query, join the employee table with the results from the above query. Then finally create a CrossTab query that takes the second query and reorients the data into the format you want.

Posting Permissions

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