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,

 

[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)

 

[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.