Group by Clause Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I receive a "Multi-level GROUP BY clause is not allowed in a subquery" error when I try to run a report with the following query:

SELECT Vessel!VES_NM+" "+[Vessel Schedule]!VOY_CD AS [Vessel Voyage], [Vessel Schedule].VES_CD, [Vessel Schedule].VOY_CD, [Vessel Schedule].ETD_VER_TM, [Vessel Schedule Update Qry].MaxOfADD_DT, (SELECT Max([ETD_VER_TM]) FROM [Vessel Schedule] AS X WHERE X.[ETD_VER_TM] < [Vessel Schedule]![ETD_VER_TM] ) AS PrevETD, DateDiff("d",[PrevETD],[ETD_VER_TM]) AS Spacing
FROM [Vessel Schedule Update Qry] INNER JOIN ([Vessel Schedule] INNER JOIN Vessel ON [Vessel Schedule].VES_CD = Vessel.VES_CD) ON ([Vessel Schedule Update Qry].MaxOfADD_DT = [Vessel Schedule].ADD_DT) AND ([Vessel Schedule Update Qry].VOY_CD = [Vessel Schedule].VOY_CD) AND ([Vessel Schedule Update Qry].VES_CD = [Vessel Schedule].VES_CD)
ORDER BY [Vessel Schedule].ETD_VER_TM;

Any help is appreciated.
 
DaOcean said:
I receive a "Multi-level GROUP BY clause is not allowed in a subquery" error when I try to run a report with the following query:

SELECT Vessel!VES_NM+" "+[Vessel Schedule]!VOY_CD AS [Vessel Voyage], [Vessel Schedule].VES_CD, [Vessel Schedule].VOY_CD, [Vessel Schedule].ETD_VER_TM, [Vessel Schedule Update Qry].MaxOfADD_DT, (SELECT Max([ETD_VER_TM]) FROM [Vessel Schedule] AS X WHERE X.[ETD_VER_TM] < [Vessel Schedule]![ETD_VER_TM] ) AS PrevETD, DateDiff("d",[PrevETD],[ETD_VER_TM]) AS Spacing
FROM [Vessel Schedule Update Qry] INNER JOIN ([Vessel Schedule] INNER JOIN Vessel ON [Vessel Schedule].VES_CD = Vessel.VES_CD) ON ([Vessel Schedule Update Qry].MaxOfADD_DT = [Vessel Schedule].ADD_DT) AND ([Vessel Schedule Update Qry].VOY_CD = [Vessel Schedule].VOY_CD) AND ([Vessel Schedule Update Qry].VES_CD = [Vessel Schedule].VES_CD)
ORDER BY [Vessel Schedule].ETD_VER_TM;
Click to expand...


It's not just your query that's involved here. For reports,
Access creates its own internal query to organize the data
and functionality specified in the report. That error can
be caused by the combination of your query and the reports
Sorting and Grouping and/or header/footer Aggregate
functions (Count, Sum, etc).

There are a couple of workarounds, a simple minded one is to
use DMax instead of the subquery.

I'm not sure, but it looks like the query by itself might
have some problems, does it run when you open it directly
from the db or query design windows?
 
Back
Top