Need help with query summing hours
in [Queries]
|
From: Russ via AccessMonster.com on 23 Feb 2010 15:40 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
From: PieterLinden via AccessMonster.com on 23 Feb 2010 16:22 Russ, use a Crosstab query. The wizard should walk you through most/all of it. -- Message posted via http://www.accessmonster.com
From: Russ via AccessMonster.com on 23 Feb 2010 19:44 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/Forums.aspx/access-queries/201002/1
From: KARL DEWEY on 23 Feb 2010 23:49 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 > > . >
From: Russ via AccessMonster.com on 24 Feb 2010 08:04
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/Forums.aspx/access-queries/201002/1 |