Need help with query summing hours

[Russ via AccessMonster.com]

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

 

[PieterLinden via AccessMonster.com]

Russ,
use a Crosstab query. The wizard should walk you through most/all of it.

 

[Russ via AccessMonster.com]

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.

 

[KARL DEWEY]

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;

 

[Russ via AccessMonster.com]

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);

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

 

[KARL DEWEY]

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.

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);

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

--



.

 

[Russ via AccessMonster.com]

Karl,
You pointed me in the direction I needed, I now have it working. Thank for
taking the time to help!
Russ

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]