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....?
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:
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.