subquery in group by query

  • Thread starter Thread starter inungh
  • Start date Start date
I

inungh

I have a sub query in my group by query and get a message as
follwoing:
you tried to execute a query that does not include the specified
expression 'DeptID' as part of aggregate function.

The field 'DeptID' is a linking ID to in my table joint.

Are there any way to have a sub query in the group by query without
aggregate function in the table joint?

If not, is the best solution to have one prepare query fir the sub
query?


Your information is great appreciated,
 
Post the SQL of your query, please.
--
Dave Hargis, Microsoft Access MVP









- Show quoted text -
Click to expand...

SELECT tblBusinessPlan.ElementID, CDate(Int([Activity_date]))-Weekday
(CDate(Int([Activity_date])))+6 AS WeekEnding, Round(Sum
([avg_aftr_call]*[acd_calls])/Sum([acd_calls]),2) AS ACW, (select
minValue from tblbusinessplan where deptID = tblPIPemployee.deptID and
splitsID = tblPIPEmployee.GroupID and elementID = 8 and ratenumber =4)
AS [Business Plan], Max(tblBusinessPlan.MinValue) AS Goal
FROM tblBusinessPlan INNER JOIN (tblSPAvayaDaily INNER JOIN
tblPIPEmployee ON tblSPAvayaDaily.Login_ID = tblPIPEmployee.ICSCLOGIN)
ON (tblBusinessPlan.SplitsID = tblPIPEmployee.GroupID) AND
(tblBusinessPlan.DeptID = tblPIPEmployee.DeptID)
WHERE (((tblSPAvayaDaily.Activity_date)>=[tblPIPEmployee].[Start_date]
And (tblSPAvayaDaily.Activity_date) Between [MyStart] And [MyEnd] And
(tblSPAvayaDaily.Activity_date)<=IIf(IsNull([tblPIPEmployee].
[Term_Date]),Date(),[tblPIPEmployee].[Term_date])) AND
((tblPIPEmployee.Employee_ID)=[MyEmployee]))
GROUP BY tblBusinessPlan.ElementID, CDate(Int([Activity_date]))-Weekday
(CDate(Int([Activity_date])))+6
HAVING (((tblBusinessPlan.ElementID)=8))
ORDER BY CDate(Int([Activity_date]))-Weekday(CDate(Int
([Activity_date])))+6

here is my query.
I got message regarding DeptID in the sub query.

Thanks again,
 
It is because the subquery becomes a field in the query results. It becomes
[Business Plan]

I don't know your data, but probably just using the Group By function for it
will resolve the problem.
--
Dave Hargis, Microsoft Access MVP


inungh said:
Post the SQL of your query, please.
--
Dave Hargis, Microsoft Access MVP









- Show quoted text -
Click to expand...

SELECT tblBusinessPlan.ElementID, CDate(Int([Activity_date]))-Weekday
(CDate(Int([Activity_date])))+6 AS WeekEnding, Round(Sum
([avg_aftr_call]*[acd_calls])/Sum([acd_calls]),2) AS ACW, (select
minValue from tblbusinessplan where deptID = tblPIPemployee.deptID and
splitsID = tblPIPEmployee.GroupID and elementID = 8 and ratenumber =4)
AS [Business Plan], Max(tblBusinessPlan.MinValue) AS Goal
FROM tblBusinessPlan INNER JOIN (tblSPAvayaDaily INNER JOIN
tblPIPEmployee ON tblSPAvayaDaily.Login_ID = tblPIPEmployee.ICSCLOGIN)
ON (tblBusinessPlan.SplitsID = tblPIPEmployee.GroupID) AND
(tblBusinessPlan.DeptID = tblPIPEmployee.DeptID)
WHERE (((tblSPAvayaDaily.Activity_date)>=[tblPIPEmployee].[Start_date]
And (tblSPAvayaDaily.Activity_date) Between [MyStart] And [MyEnd] And
(tblSPAvayaDaily.Activity_date)<=IIf(IsNull([tblPIPEmployee].
[Term_Date]),Date(),[tblPIPEmployee].[Term_date])) AND
((tblPIPEmployee.Employee_ID)=[MyEmployee]))
GROUP BY tblBusinessPlan.ElementID, CDate(Int([Activity_date]))-Weekday
(CDate(Int([Activity_date])))+6
HAVING (((tblBusinessPlan.ElementID)=8))
ORDER BY CDate(Int([Activity_date]))-Weekday(CDate(Int
([Activity_date])))+6

here is my query.
I got message regarding DeptID in the sub query.

Thanks again,
Click to expand...
 
Back
Top