Report Error: Multi-Level GROUP BY clause is not allowed in a subquery

[Tim Marshall]

A2003. I am getting this error message when I try to set a report's
recordsource to an SQL statement or a saved querydef that uses sub-queries.

I've debug.printed the SQL, and run it as a stand alone query, as a
rowsource for a list box and as a recordsource for a form. There's no
problem. I'm only encountering this in reports.

As a stop gap, I'm going to use a form for a printe report, bvut I
wonder if anyone has encountered anything like this before? A2003's
help is useless on this - no error number is shown and pressing the help
button with a broadband connection produces absolutely nothing.

The SQL is rather lengthy, but here're a couple of field's expressions
copied straight from the querydesign grid that could, I suppose, be
considered "typical". Remember, this works beautifully in forms and
listboxes and run as a query. There are 12 similar sub queries in the
SQL - they are drawing information from mainly a large transaction table
that contains many types of transactions (lots of fields) which are
either 0 or some value:

Square: IIf([Carried by]="Not Mounted",nz((Select top 1 ORD_SQUARE from
TBL_TURN_ORDERS as S where S.ORD_UNT_FK = A.UNT_PK and ((ORD_CONFIRM = 1
and ORD_TURN < 1) or ORD_TURN = 1) order by ord_sequence desc),"Not
Placed"),"-")

Sp Av: [UTY_SUP_CAP]+nz((select sum(ORD_SUP_COST) from TBL_TURN_ORDERS
as S where S.ORD_UNT_FK = A.UNT_PK and ((ORD_CONFIRM = 1 AND ORD_TURN <
1) or ORD_TURN = 1)),0)

Carried by: IIf(nz((select top 1 ORD_TRANSPORT from TBL_TURN_ORDERS as S
where S.ORD_UNT_FK = A.UNT_PK and ORD_TRANSPORT <> 0 and
ORD_TRANS_UNT_FK <> 0 and ((S.ORD_TURN < 1 and S.ORD_CONFIRM = -1) or
S.ORD_TURN = 1) Order by ORD_SEQUENCE desc),-1)=-1,"Not Mounted",(SELECT
top 1 UNT_CODE FROM TBL_TURN_ORDERS as S INNER JOIN TBL_UNITS as U ON
S.ORD_TRANS_UNT_FK = U.UNT_PK where S.ORD_TRANSPORT = 1 AND
S.ORD_UNT_FK = A.UNT_PK AND ((S.ORD_TURN < 1 AND S.ORD_CONFIRM = 1) or
(S.ORD_TURN = 1)) order by ord_sequence desc))
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Allen Browne]

Yes, Tim, it's very easy to get this error.

It seems that the reporter is creating another query into your query, to
handle the things specified in the Sorting'n'Grouping dialog. If there is
nothing there, the query with subquery works fine, but if it has to group
further, it spits the dummy with this "multi-level GROUP BY" message.

Since you generally can't avoid the grouping at the report level, the
workaround is generally to change the source query. You might be able to use
stacked queries instead of subqueries (i.e. a query that uses another query
as a source "table".) And you might even be able to get away with including
the subquery in the lower level query.

You may have also found that it's fairly easy to crash Access using
subqueries ("shut down by Windows... Sorry for inconvenience"). While
subqueries are very useful, JET's implementation of them is somewhat
lacking.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:da**********@coranto.ucs.mun.ca...

A2003. I am getting this error message when I try to set a report's
recordsource to an SQL statement or a saved querydef that uses
sub-queries.

I've debug.printed the SQL, and run it as a stand alone query, as a
rowsource for a list box and as a recordsource for a form. There's no
problem. I'm only encountering this in reports.

As a stop gap, I'm going to use a form for a printe report, bvut I wonder
if anyone has encountered anything like this before? A2003's help is
useless on this - no error number is shown and pressing the help button
with a broadband connection produces absolutely nothing.

The SQL is rather lengthy, but here're a couple of field's expressions
copied straight from the querydesign grid that could, I suppose, be
considered "typical". Remember, this works beautifully in forms and
listboxes and run as a query. There are 12 similar sub queries in the
SQL - they are drawing information from mainly a large transaction table
that contains many types of transactions (lots of fields) which are either
0 or some value:

Square: IIf([Carried by]="Not Mounted",nz((Select top 1 ORD_SQUARE from
TBL_TURN_ORDERS as S where S.ORD_UNT_FK = A.UNT_PK and ((ORD_CONFIRM = 1
and ORD_TURN < 1) or ORD_TURN = 1) order by ord_sequence desc),"Not
Placed"),"-")

Sp Av: [UTY_SUP_CAP]+nz((select sum(ORD_SUP_COST) from TBL_TURN_ORDERS as
S where S.ORD_UNT_FK = A.UNT_PK and ((ORD_CONFIRM = 1 AND ORD_TURN < 1) or
ORD_TURN = 1)),0)

Carried by: IIf(nz((select top 1 ORD_TRANSPORT from TBL_TURN_ORDERS as S
where S.ORD_UNT_FK = A.UNT_PK and ORD_TRANSPORT <> 0 and ORD_TRANS_UNT_FK
<> 0 and ((S.ORD_TURN < 1 and S.ORD_CONFIRM = -1) or S.ORD_TURN = 1)
Order by ORD_SEQUENCE desc),-1)=-1,"Not Mounted",(SELECT top 1 UNT_CODE
FROM TBL_TURN_ORDERS as S INNER JOIN TBL_UNITS as U ON S.ORD_TRANS_UNT_FK
= U.UNT_PK where S.ORD_TRANSPORT = 1 AND S.ORD_UNT_FK = A.UNT_PK AND
((S.ORD_TURN < 1 AND S.ORD_CONFIRM = 1) or (S.ORD_TURN = 1)) order by
ord_sequence desc))