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

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

--
Message posted via http://www.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.


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

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

.

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

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

.

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