Re: Multi-Level GROUP By Clause is not allowed in subquery
- From: Marshall Barton <marshbarton@xxxxxxxxxx>
- Date: Wed, 26 Mar 2008 10:17:47 -0600
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]
Souris wrote:
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?
"Marshall Barton" wrote:
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)
- References:
- Re: Multi-Level GROUP By Clause is not allowed in subquery
- From: Marshall Barton
- Re: Multi-Level GROUP By Clause is not allowed in subquery
- From: Souris
- Re: Multi-Level GROUP By Clause is not allowed in subquery
- Prev by Date: Custom Footer...
- Next by Date: Re: report template
- Previous by thread: Re: Multi-Level GROUP By Clause is not allowed in subquery
- Next by thread: Re: Multi-Level GROUP By Clause is not allowed in subquery
- Index(es):
Relevant Pages
|
