Need help with query summing hours
in [Queries]
|
From: KARL DEWEY on 24 Feb 2010 10:45 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/Forums.aspx/access-queries/201002/1 > > . >
From: Russ via AccessMonster.com on 24 Feb 2010 14:15
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 |