SUM

  • Thread starter Thread starter Ryan
  • Start date Start date
R

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
 
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
 
Back
Top