Weird problem with subqueries & reports

[ken]

This is very weird, wondering if any access gurus out there can answer this.

I have a fairly simple report which outputs the data from a query. The
query has a 5 item group by clause and a bunch of sum()'s in the select
clause. The select clause also contains a correlated subquery with a sum()
inside it. The query runs fine when I run it directly, but when I run the
report (which runs the query) I get an error:
"Multi-level GROUP BY clause is not allowed in a subquery". If i alter the
report to not output the column returned by the subquery, the message does
not appear.

Just so you don't ask:
1. The group by is not IN the subquery.. rather in the main query.
2. I have checked and re-checked, there is no missing ()

Thanks a bunch!
Ken

Query for reference:

SELECT tblCostSummary.ferc, tblCostSummary.point,
tblCostSummary.work_order_id, tblCostSummary.task_id,
tblCostSummary.vendor_cd, Sum(tblCostSummary.PTDActuals) AS PTDActuals,
Sum(tblCostSummary.YTDActuals) AS YTDActuals,
Sum(tblCostSummary.WM_Commitment) AS WM_Commitment,
Sum(tblCostSummary.CW_Commitment) AS CW_Commitment,
Sum(tblCostSummary.Total_Commitment) AS Total_Commitment,
Sum(tblCostSummary.Adjusted_Commitment) AS Adjusted_Commitment,
Sum(tblCostSummary.Commitment_Balance) AS Commitment_Balance, (SELECT
sum(tblCostSummary1.Estimate) FROM tblCostSummary1 WHERE
tblCostSummary1.ferc=tblCostSummary.ferc AND
tblCostSummary1.point=tblCostSummary.point AND
tblCostSummary1.work_order_id=tblCostSummary.work_order_id AND
tblCostSummary1.task_id=tblCostSummary.task_id) AS Estimate
FROM tblCostSummary INNER JOIN [E&C Projects] ON (tblCostSummary.point =
[E&C Projects].Pnt) AND (tblCostSummary.ferc = [E&C Projects].Ferc)
WHERE (((tblCostSummary.resource) In ('03','07')))
GROUP BY tblCostSummary.ferc, tblCostSummary.point,
tblCostSummary.work_order_id, tblCostSummary.task_id, tblCostSummary.vendor_cd
ORDER BY tblCostSummary.ferc, tblCostSummary.point,
tblCostSummary.work_order_id, tblCostSummary.task_id,
tblCostSummary.vendor_cd;








Expand AllCollapse All


Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletter
© 2005 Microsoft Corporation. All rights reserved. Terms of Use |Trademarks
|Privacy Statement

 

[ken]

I finally figured this out.
The report has a grouping on it, which somehow caused the query to evaluate
with the grouping added to the subquery. So, I changed the subquery to a
self-join and now it works.

fyi
k