Sum of a Sum Query

  • Thread starter Thread starter Duncan Edment
  • Start date Start date
D

Duncan Edment

I'm trying to write a query that will retrieve records for a specified date
or date period, and show all the time worked by all employees on all
projects, in that time frame. So far, I have a query as follows:

SELECT tblProjects.fldProjectDescription, tblEmployee.fldEmployeeName,
tblTimeSheet.fldDateWorked, tblTimeSheet.fldStartTime,
tblTimeSheet.fldEndTime, Sum([fldEndTime]-[fldStartTime]) AS
ProjectDuration, tblTimeSheet.fldEmployeeID, tblTimeSheet.fldProjectID
FROM tblProjects INNER JOIN (tblEmployee INNER JOIN tblTimeSheet ON
tblEmployee.fldEmployeeID = tblTimeSheet.fldEmployeeID) ON
tblProjects.fldProjectID = tblTimeSheet.fldProjectID
WHERE (((tblTimeSheet.fldDateWorked) Between [StartDate] And [EndDate]))
GROUP BY tblProjects.fldProjectDescription, tblEmployee.fldEmployeeName,
tblTimeSheet.fldDateWorked, tblTimeSheet.fldStartTime,
tblTimeSheet.fldEndTime, tblTimeSheet.fldEmployeeID,
tblTimeSheet.fldProjectID
ORDER BY tblProjects.fldProjectDescription, tblEmployee.fldEmployeeName;

However, this retrieves multiple 'ProjectDuration' values for each employee.
What I want it to do, is provide me with a sum of the 'ProjectDuration'
values for each project and employee. Make sense?

Also, what is the best way to display this information? I tried looking at
a Crosstab & a PivotTable however, possibly since my query is not right,
both looked cluttered and were more of a hindrance than anything else. I'd
appreciate your input.

Thanks & regards

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


My guess is that there are multiple entries per project in the
tblTimeSheet table. When this table is JOINed to the other tables you
will get the number of records per project as there are entries in the
tblTimeSheet table for each project. You'd have to run 2 queries to
get the answer: one to get the sums; the other to join the sums to
each employee name & project name.

Specifically: You can't include daily start & end times in a summary
query because you will get a summary of each day, when what you want
is the summary of all days times.

Perhaps this may work (untested):

qryProjectTimeSum_2:

PARAMETERS [StartDate] Date, [EndDate] Date;
SELECT fldEmployeeID, fldProjectID,
Min(DateWorked) As FirstDay,
Max(DateWorked) As LastDay,
Sum(fldEndTime - fldStartTime) As Duration
FROM tblTimeSheet
WHERE fldDateWorked Between [StartDate] And [EndDate]
GROUP BY fldEmployeeID, fldProjectID


qryProjectTimeSum_1:

SELECT
P.fldProjectID, P.fldProjectDescription,
E.fldEmployeeID, E.fldEmployeeName,
TS.FirstDay,
TS.LastDay,
TS.Duration As ProjectDuration
FROM tblProjects As P INNER JOIN (tblEmployee As E INNER JOIN
qryProjectTimeSum_2 As TS ON E.fldEmployeeID = TS.fldEmployeeID) ON
P.fldProjectID = TS.fldProjectID
ORDER BY P.fldProjectDescription, E.fldEmployeeName;

Run qryProjectTimeSum_1 to get the final result.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCQi1IechKqOuFEgEQLsJgCfUptDgIDwlnC9Q8aNbHB0SWJCa1kAniT6
Qo77bJGjSFEOHtaxrqBR8AHM
=PO5T
-----END PGP SIGNATURE-----


Duncan said:
I'm trying to write a query that will retrieve records for a specified date
or date period, and show all the time worked by all employees on all
projects, in that time frame. So far, I have a query as follows:

SELECT tblProjects.fldProjectDescription, tblEmployee.fldEmployeeName,
tblTimeSheet.fldDateWorked, tblTimeSheet.fldStartTime,
tblTimeSheet.fldEndTime, Sum([fldEndTime]-[fldStartTime]) AS
ProjectDuration, tblTimeSheet.fldEmployeeID, tblTimeSheet.fldProjectID
FROM tblProjects INNER JOIN (tblEmployee INNER JOIN tblTimeSheet ON
tblEmployee.fldEmployeeID = tblTimeSheet.fldEmployeeID) ON
tblProjects.fldProjectID = tblTimeSheet.fldProjectID
WHERE (((tblTimeSheet.fldDateWorked) Between [StartDate] And [EndDate]))
GROUP BY tblProjects.fldProjectDescription, tblEmployee.fldEmployeeName,
tblTimeSheet.fldDateWorked, tblTimeSheet.fldStartTime,
tblTimeSheet.fldEndTime, tblTimeSheet.fldEmployeeID,
tblTimeSheet.fldProjectID
ORDER BY tblProjects.fldProjectDescription, tblEmployee.fldEmployeeName;

However, this retrieves multiple 'ProjectDuration' values for each employee.
What I want it to do, is provide me with a sum of the 'ProjectDuration'
values for each project and employee. Make sense?

Also, what is the best way to display this information? I tried looking at
a Crosstab & a PivotTable however, possibly since my query is not right,
both looked cluttered and were more of a hindrance than anything else. I'd
appreciate your input.

Thanks & regards

Duncan
Click to expand...
 
It's OK, I managed to work it out. I created a second query, based on the
query below, and selected to show a Sum of the Sum field from the previous
query.

Works a treat.

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.
 
Back
Top