If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help with query summing hours
Tring to get total worked hours and total maintenance hours summary.
I have a table with the clock hours WorkID WorkDate WorkEmployeeID WorkHours And have a maintenace table MaintID MaintDate MaintEmployeeID MaintHours Needing help combining the information to get summary something like this.. Date - Employee - Clock Hours - Maintenance Hours 1/1/10 Joe Billy Bob 8 6 1/1/10 Billy Joe Bob 8 7 Whay type of query do I need to build, any help would be great. Thanks -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Need help with query summing hours
Russ,
use a Crosstab query. The wizard should walk you through most/all of it. -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Need help with query summing hours
Not sure that will do it.
How would I sum all maintenance hours for the day and sum hours worked for the day and math only that day providing a summary list? example From the maintenance table...(recordID, date, employee, time) on 1/1/09 Jim had a total of 5 hrs from from maintenance records he worked on that day 2hrs from record 1 and 3hrs from record 2. From the Time table.....(recordID, date, employee, time) on 1/1/09 Jim had a total of 8 hrs from from his clock in/out times he worked from 8am to noon giving him 4 hrs then came back and worked from 6pm-10pm give him a total of 8 hours for that day. Trying to get summary of the days work hours and actual time 1/1/09 / Jim / 5hrs Maint / 8hrs actual time I am just not getting the link, is it somehow in the date? Thanks in advance for any help. PieterLinden wrote: Russ, use a Crosstab query. The wizard should walk you through most/all of it. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#4
|
|||
|
|||
Need help with query summing hours
Use a union query to make an employe/date query and then left join to the
other tables. qryWorkEmpMaint -- SELECT WorkDate, WorkEmployeeID FROM tblWorkHours UNION SELECT MaintDate, MaintEmployeeID FROM tblMaintenace; SELECT WorkDate, WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance Hours] FROM (qryWorkEmpMaint LEFT JOIN tblWorkHours ON qryWorkEmpMaint.WorkDate = tblWorkHours.WorkDate AND qryWorkEmpMaint.WorkEmployeeID = tblWorkHours.WorkEmployeeID) LEFT JOIN tblMaintenace ON tblMaintenace.MaintDate = qryWorkEmpMaint.WorkDate AND tblMaintenace.MaintEmployeeID = qryWorkEmpMaint.WorkEmployeeID; -- Build a little, test a little. "Russ via AccessMonster.com" wrote: Tring to get total worked hours and total maintenance hours summary. I have a table with the clock hours WorkID WorkDate WorkEmployeeID WorkHours And have a maintenace table MaintID MaintDate MaintEmployeeID MaintHours Needing help combining the information to get summary something like this.. Date - Employee - Clock Hours - Maintenance Hours 1/1/10 Joe Billy Bob 8 6 1/1/10 Billy Joe Bob 8 7 Whay type of query do I need to build, any help would be great. Thanks -- Message posted via http://www.accessmonster.com . |
#5
|
|||
|
|||
Need help with query summing hours
Karl,
Thanks for the help. I seem to be getting closer. But currently getting error when trying to run final query. You tried to execute a query that does not include the specified expression "worked date" as part of the aggregate function qryWorkEmpMaint... SELECT WorkedDate, WorkEmployeeID FROM QryEmployeeWorkedHours UNION SELECT EmpMaintDate, MaintEmployeeID FROM QryMaintenanceHrs; SELECT qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance Hours] FROM (qryWorkEmpMaint LEFT JOIN QryEmployeeWorkedHours ON (qryWorkEmpMaint. WorkedDate = QryEmployeeWorkedHours.[WorkedDate]) AND (qryWorkEmpMaint. WorkEmployeeID = QryEmployeeWorkedHours.[WorkEmployeeID])) LEFT JOIN QryMaintenanceHrs ON (qryWorkEmpMaint.WorkedDate = QryMaintenanceHrs. EmpMaintDate) AND (qryWorkEmpMaint.WorkEmployeeID = QryMaintenanceHrs. MaintEmployeeID); KARL DEWEY wrote: Use a union query to make an employe/date query and then left join to the other tables. qryWorkEmpMaint -- SELECT WorkDate, WorkEmployeeID FROM tblWorkHours UNION SELECT MaintDate, MaintEmployeeID FROM tblMaintenace; SELECT WorkDate, WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance Hours] FROM (qryWorkEmpMaint LEFT JOIN tblWorkHours ON qryWorkEmpMaint.WorkDate = tblWorkHours.WorkDate AND qryWorkEmpMaint.WorkEmployeeID = tblWorkHours.WorkEmployeeID) LEFT JOIN tblMaintenace ON tblMaintenace.MaintDate = qryWorkEmpMaint.WorkDate AND tblMaintenace.MaintEmployeeID = qryWorkEmpMaint.WorkEmployeeID; Tring to get total worked hours and total maintenance hours summary. I have a table with the clock hours [quoted text clipped - 16 lines] Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#6
|
|||
|
|||
Need help with query summing hours
Left off the group by --
SELECT qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance Hours] FROM (qryWorkEmpMaint LEFT JOIN QryEmployeeWorkedHours ON (qryWorkEmpMaint.WorkedDate = QryEmployeeWorkedHours.[WorkedDate]) AND (qryWorkEmpMaint.WorkEmployeeID = QryEmployeeWorkedHours.[WorkEmployeeID])) LEFT JOIN QryMaintenanceHrs ON(qryWorkEmpMaint.WorkedDate = QryMaintenanceHrs.EmpMaintDate) AND (qryWorkEmpMaint.WorkEmployeeID = QryMaintenanceHrs.MaintEmployeeID) GROUP BY qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID; -- Build a little, test a little. "Russ via AccessMonster.com" wrote: Karl, Thanks for the help. I seem to be getting closer. But currently getting error when trying to run final query. You tried to execute a query that does not include the specified expression "worked date" as part of the aggregate function qryWorkEmpMaint... SELECT WorkedDate, WorkEmployeeID FROM QryEmployeeWorkedHours UNION SELECT EmpMaintDate, MaintEmployeeID FROM QryMaintenanceHrs; SELECT qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance Hours] FROM (qryWorkEmpMaint LEFT JOIN QryEmployeeWorkedHours ON (qryWorkEmpMaint. WorkedDate = QryEmployeeWorkedHours.[WorkedDate]) AND (qryWorkEmpMaint. WorkEmployeeID = QryEmployeeWorkedHours.[WorkEmployeeID])) LEFT JOIN QryMaintenanceHrs ON (qryWorkEmpMaint.WorkedDate = QryMaintenanceHrs. EmpMaintDate) AND (qryWorkEmpMaint.WorkEmployeeID = QryMaintenanceHrs. MaintEmployeeID); KARL DEWEY wrote: Use a union query to make an employe/date query and then left join to the other tables. qryWorkEmpMaint -- SELECT WorkDate, WorkEmployeeID FROM tblWorkHours UNION SELECT MaintDate, MaintEmployeeID FROM tblMaintenace; SELECT WorkDate, WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance Hours] FROM (qryWorkEmpMaint LEFT JOIN tblWorkHours ON qryWorkEmpMaint.WorkDate = tblWorkHours.WorkDate AND qryWorkEmpMaint.WorkEmployeeID = tblWorkHours.WorkEmployeeID) LEFT JOIN tblMaintenace ON tblMaintenace.MaintDate = qryWorkEmpMaint.WorkDate AND tblMaintenace.MaintEmployeeID = qryWorkEmpMaint.WorkEmployeeID; Tring to get total worked hours and total maintenance hours summary. I have a table with the clock hours [quoted text clipped - 16 lines] Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
#7
|
|||
|
|||
Need help with query summing hours
Karl,
You pointed me in the direction I needed, I now have it working. Thank for taking the time to help! Russ KARL DEWEY wrote: Left off the group by -- SELECT qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID AS Employee, Sum(WorkHours) AS [Clock Hours], Sum(MaintHours) AS [Maintenance Hours] FROM (qryWorkEmpMaint LEFT JOIN QryEmployeeWorkedHours ON (qryWorkEmpMaint.WorkedDate = QryEmployeeWorkedHours.[WorkedDate]) AND (qryWorkEmpMaint.WorkEmployeeID = QryEmployeeWorkedHours.[WorkEmployeeID])) LEFT JOIN QryMaintenanceHrs ON(qryWorkEmpMaint.WorkedDate = QryMaintenanceHrs.EmpMaintDate) AND (qryWorkEmpMaint.WorkEmployeeID = QryMaintenanceHrs.MaintEmployeeID) GROUP BY qryWorkEmpMaint.WorkedDate, qryWorkEmpMaint.WorkEmployeeID; Karl, Thanks for the help. I seem to be getting closer. [quoted text clipped - 39 lines] Thanks -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|