Multi-level group-by clause not allowed in a sub-query

[Guest]

Hi,

When I try to run a report based on some queries, with a grand total in the
report footer, I get an error message saying something like the title of this
post (sorry my office is in french). However, there are no group by clause
in my queries, just the one in the report. Furthermore, if I change the
source of the report from e.g. [my query] to SELECT * FROM [my query]; then
Access simply crashes when I run the report.

Here is an example of a query that does that:
SELECT Table1.Champ1, (SELECT Champ2 FROM Table1 WHERE Champ1 = "b") AS Moo
FROM Table1;

This is a simple example that could be done otherwise, but in my other
query, I didn't find another way of doing it (without a sub-query in the
SELECT list).

Any idea?

Thanks

 

[Allen Browne]

Hi zorgkang

You have hit two fairly common problems in Access.

The "multi-level group by" issue occurs because the Access report actually
uses the information in the Sorting And Grouping dialog to write another
query behind the scenes, using the RecordSource as input. At this point, it
often fails if the source contains a subquery.

As a workaround for that issue, could you create a different query that
contains the:
SELECT Champ2 FROM Table1 WHERE Champ1 = "b")
save it, and then use it as an input "table" for the main query? Not always
convenient, but it does avoid the issue.

The second problem is the crashes associated with subqueries. This problem
occurs in all recent versions of Access, and AFAIK the only solution is to
find another approach (i.e. don't use a subquery) when you strike this. I've
not been able to pin down the exact circumstances, but it's super-easy to
crash Access with this. You can create a query containing a subquery that
works fine. Then even if you use that one as the input for another query,
the 2nd query (which contains no subquery itself) can crash Access. The only
workaround is to change the lower level query so it does not contain a
subquery.

Sorry: that's not very good news, but at least you know that the problem is
with Access and not with your database, design, or software installation.

 

[Guest]

Thanks!
I kinda expected that was the case