SUM function for DISTINCT rows

[aaearhart]

Hello-

I have a table like this

tbl_WorkDetails
WorkDate
WorkProject
WorkHours

....with values like this:
WorkDate WorkProject WorkHours
06/01/2005 ProjectA 4
06/01/2005 ProjectC 2
06/02/2005 ProjectA 2
06/02/2005 ProjectB 2
06/02/2005 ProjectC 4
06/03/2005 ProjectA 4
06/03/2005 ProjectC 2
06/04/2005 ProjectA 2
06/04/2005 ProjectB 2
06/04/2005 ProjectC 4

What i'd like my report(query) to show is this:
WorkProject SumOfWorkHours
ProjectA 12
ProjectB 4
ProjectC 12

I imagine a query like this:

SELECT DISTINCT WorkProject
FROM tbl_WorkDetails
ORDER BY WorkProject;

....and then one like this (coding help provided by Marshall Barton):

SELECT T.WorkProject,
Y.DistinctWorkProjectQuery,
Sum(T.WorkHours) AS SumOfWorkHours
FROM tbl_WorkDetails As T INNER JOIN
[SELECT X.WorkHours
FROM DistinctWorkProjectQuery As X
GROUP BY X.WorkHours]. Y
ON Y.WorkHours= T.WorkHours
GROUP BY T.WorkHours, Y.DistinctWorkProjectQuery

so

the code runs just fine, but the math is bad bad bad. Any thoughts on why
this is?

Thanks!
/amelia

 

[Steve Schapel]

Amelia,

How about like this?...
SELECT WorkProject, Sum([WorkHours])
FROM tbl_WorkDetails
GROUP BY WorkProject

 

[aaearhart]

marvy!

thanks loads!

/amelia

Amelia,

How about like this?...
SELECT WorkProject, Sum([WorkHours])
FROM tbl_WorkDetails
GROUP BY WorkProject

--
Steve Schapel, Microsoft Access MVP

Hello-

I have a table like this

tbl_WorkDetails
WorkDate
WorkProject
WorkHours

...with values like this:
WorkDate WorkProject WorkHours
06/01/2005 ProjectA 4
06/01/2005 ProjectC 2
06/02/2005 ProjectA 2
06/02/2005 ProjectB 2
06/02/2005 ProjectC 4
06/03/2005 ProjectA 4
06/03/2005 ProjectC 2
06/04/2005 ProjectA 2
06/04/2005 ProjectB 2
06/04/2005 ProjectC 4

What i'd like my report(query) to show is this:
WorkProject SumOfWorkHours
ProjectA 12
ProjectB 4
ProjectC 12

I imagine a query like this:

SELECT DISTINCT WorkProject
FROM tbl_WorkDetails
ORDER BY WorkProject;

...and then one like this (coding help provided by Marshall Barton):

SELECT T.WorkProject,
Y.DistinctWorkProjectQuery,
Sum(T.WorkHours) AS SumOfWorkHours
FROM tbl_WorkDetails As T INNER JOIN
[SELECT X.WorkHours
FROM DistinctWorkProjectQuery As X
GROUP BY X.WorkHours]. Y
ON Y.WorkHours= T.WorkHours
GROUP BY T.WorkHours, Y.DistinctWorkProjectQuery

so

the code runs just fine, but the math is bad bad bad. Any thoughts on why
this is?

Thanks!
/amelia