SUM

[Ryan]

Is there anyway I can summ an expression in a query?

For example... Here is my query:

-----------------------------------------------------------
SELECT [tblActuals].[master], [tblActuals].[wrkpkg], Sum
([total_hours])/(SELECT Wk_Hours FROM tblWeek_Hours WHERE
tblActuals.period_date = tblWeek_Hours.WE_Date) AS Equiv

FROM tblRptCAMVariance, tblActuals INNER JOIN
tblWeek_Hours ON [tblActuals].[period_date]=
[tblWeek_Hours].[WE_Date]

WHERE ((([tblActuals].[master])=[tblRptCAMVariance]!
[Master]) And (([tblActuals].[wrkpkg])=[tblRptCAMVariance]!
[Wrkpkg]))

GROUP BY [tblActuals].[master], [tblActuals].[wrkpkg],
[tblWeek_Hours].[Mo_Num], [tblWeek_Hours].[Wk_Num],
[tblActuals].[period_date], [tblActuals].[dept_chgd]

HAVING (((tblWeek_Hours.Mo_Num)=[Forms]![frmMain]!
[txtMoNum]) AND ((tblWeek_Hours.Wk_Num)=1));

-----------------------------------------------------------


I would like to SUM the Equiv field that is calculated for
each wrkpkg. Is there an easy way to do this or do I need
to create another query and reference this one?

Thanks,
Ryan

 

[Michel Walsh]

Hi,

You cannot aggregate on an aggregate, in the same "query", since, in a
"query", you can't have a mixed group by clause. There are alternatives,
like a CUBE or a ROLLUP, in MS SQL Server, but in general, you have to use
the actual query as a virtual table (or "embedded" in another query) which
would define the new GROUP BY, and eventually, UNION ALL the two queries
together.


Hoping it may help,
Vanderghast, Access MVP