Multi-Level GROUP By Clause is not allowed in subquery

[Souris]

I use a query which includes subquery to generate my report, because they are
not in the same table.

I got "Multi-Level GROUP By Clause is not allowed in subquery" message when
I run the report.

MS Access lets me create the report using the query.

The query works fine when I run it.

Does MS Access report support subquery?
If not, any work around?

Your information is great appreciated,

 

[Marshall Barton]

I use a query which includes subquery to generate my report, because they are
not in the same table.

I got "Multi-Level GROUP By Clause is not allowed in subquery" message when
I run the report.

MS Access lets me create the report using the query.

The query works fine when I run it.

Does MS Access report support subquery?
If not, any work around?

The problem with a subquery in a report's record source
happens when the report uses an aggregate function (Count,
Sum, etc) or has groups (via Sorting and Grouping). In this
case, the internally generated query that the report
actually uses adds additional GROUP BY clauses that breaks
in the presence of some(?) subqueries.

The standard workaround is to use a domain aggregate
function (DLookup, DCount, DSum, etc) instead of a subquery.

If that's not feasible, then the only other workaround I
know of is kind of ridiculous, but it has always worked for
me. Add this kind of glop to the end of your query:

UNION ALL
SELECT Null, Null, Null, Null, ...
FROM [a small table]
WHERE (some condition that is always false)

 

[Souris]

Thanks millions for the information,

Do you mean that I change select query to union query and at the end to
union all?

Does it work using union or must union all?

Thanks again,

I use a query which includes subquery to generate my report, because they are
not in the same table.

I got "Multi-Level GROUP By Clause is not allowed in subquery" message when
I run the report.

MS Access lets me create the report using the query.

The query works fine when I run it.

Does MS Access report support subquery?
If not, any work around?

The problem with a subquery in a report's record source
happens when the report uses an aggregate function (Count,
Sum, etc) or has groups (via Sorting and Grouping). In this
case, the internally generated query that the report
actually uses adds additional GROUP BY clauses that breaks
in the presence of some(?) subqueries.

The standard workaround is to use a domain aggregate
function (DLookup, DCount, DSum, etc) instead of a subquery.

If that's not feasible, then the only other workaround I
know of is kind of ridiculous, but it has always worked for
me. Add this kind of glop to the end of your query:

UNION ALL
SELECT Null, Null, Null, Null, ...
FROM [a small table]
WHERE (some condition that is always false)

 

[Souris]

Thanks millions,

It works fine,

I use a query which includes subquery to generate my report, because they are
not in the same table.

I got "Multi-Level GROUP By Clause is not allowed in subquery" message when
I run the report.

MS Access lets me create the report using the query.

The query works fine when I run it.

Does MS Access report support subquery?
If not, any work around?

The problem with a subquery in a report's record source
happens when the report uses an aggregate function (Count,
Sum, etc) or has groups (via Sorting and Grouping). In this
case, the internally generated query that the report
actually uses adds additional GROUP BY clauses that breaks
in the presence of some(?) subqueries.

The standard workaround is to use a domain aggregate
function (DLookup, DCount, DSum, etc) instead of a subquery.

If that's not feasible, then the only other workaround I
know of is kind of ridiculous, but it has always worked for
me. Add this kind of glop to the end of your query:

UNION ALL
SELECT Null, Null, Null, Null, ...
FROM [a small table]
WHERE (some condition that is always false)

 

[Marshall Barton]

Glad to hear that you got it working.

It should work with UNION, but UNION eliminates duplicate
records (same as DISTINCT), which is a (big) waste of time.
If you want any duplicate records, then UNION would just be
wrong. If you did not want duplicates, your original
query's DISTINCT predicate would have already taken care of
it. If there can not be any duplicates in your original
query, then UNION just wastes a lot of time looking for
nonexistant duplicates.

Note that the WHERE clause in the silly Select query should
be more than a simple WHERE False. You should choose a
small table that has a primary key and compare that to an
impossible value (e.g. WHERE Key = 0). The point is to
allow the query engine to use the index to eliminate all the
records instead of retrieving all the records just to
evaluate False on every record.
--
Marsh
MVP [MS Access]

Thanks millions for the information,

Do you mean that I change select query to union query and at the end to
union all?

Does it work using union or must union all?

I use a query which includes subquery to generate my report, because they are
not in the same table.

I got "Multi-Level GROUP By Clause is not allowed in subquery" message when
I run the report.

MS Access lets me create the report using the query.

The query works fine when I run it.

Does MS Access report support subquery?
If not, any work around?

The problem with a subquery in a report's record source
happens when the report uses an aggregate function (Count,
Sum, etc) or has groups (via Sorting and Grouping). In this
case, the internally generated query that the report
actually uses adds additional GROUP BY clauses that breaks
in the presence of some(?) subqueries.

The standard workaround is to use a domain aggregate
function (DLookup, DCount, DSum, etc) instead of a subquery.

If that's not feasible, then the only other workaround I
know of is kind of ridiculous, but it has always worked for
me. Add this kind of glop to the end of your query:

UNION ALL
SELECT Null, Null, Null, Null, ...
FROM [a small table]
WHERE (some condition that is always false)

 

[Phil M]

Thanks millions,

It works fine,

I use a query which includes subquery to generate my report, because they are
not in the same table.

I got "Multi-Level GROUP By Clause is not allowed in subquery" message when
I run the report.

MS Access lets me create the report using the query.

The query works fine when I run it.

Does MS Access report support subquery?
If not, any work around?

The problem with a subquery in a report's record source
happens when the report uses an aggregate function (Count,
Sum, etc) or has groups (via Sorting and Grouping). In this
case, the internally generated query that the report
actually uses adds additional GROUP BY clauses that breaks
in the presence of some(?) subqueries.

The standard workaround is to use a domain aggregate
function (DLookup, DCount, DSum, etc) instead of a subquery.

If that's not feasible, then the only other workaround I
know of is kind of ridiculous, but it has always worked for
me. Add this kind of glop to the end of your query:

UNION ALL
SELECT Null, Null, Null, Null, ...
FROM [a small table]
WHERE (some condition that is always false)

So basically, Access is perfectly capable of executing such queries. All you
have to do is fool it into thinking that it is dealing with a different kind
of subquery (one that doesn't contain a grouping level) and it's fine. So the
problem is with the error message?

 

[Marshall Barton]

Thanks millions,

It works fine,

Souris wrote:

I use a query which includes subquery to generate my report, because they are
not in the same table.

I got "Multi-Level GROUP By Clause is not allowed in subquery" message when
I run the report.

MS Access lets me create the report using the query.

The query works fine when I run it.

Does MS Access report support subquery?
If not, any work around?


The problem with a subquery in a report's record source
happens when the report uses an aggregate function (Count,
Sum, etc) or has groups (via Sorting and Grouping). In this
case, the internally generated query that the report
actually uses adds additional GROUP BY clauses that breaks
in the presence of some(?) subqueries.

The standard workaround is to use a domain aggregate
function (DLookup, DCount, DSum, etc) instead of a subquery.

If that's not feasible, then the only other workaround I
know of is kind of ridiculous, but it has always worked for
me. Add this kind of glop to the end of your query:

UNION ALL
SELECT Null, Null, Null, Null, ...
FROM [a small table]
WHERE (some condition that is always false)

So basically, Access is perfectly capable of executing such queries. All you
have to do is fool it into thinking that it is dealing with a different kind
of subquery (one that doesn't contain a grouping level) and it's fine. So the
problem is with the error message?

Personally, I think that's correct. Instead of generating
the error, Access could reconstruct its internal query to
avoid the issue and try again.

I think its probably too inefficient to determine apriori if
the problem will occur because the issue could easily be too
deep in Jet's query optimization process to mess with
without opening the doors to countless new problems. And
besides, who's to say that Jet is the database engine that's
running the query.