Re: Multi-Level GROUP By Clause is not allowed in subquery

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,



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

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: Reporting the last two updates
    ... a subquery. ... correlated subquery because it refers to the outer query, ... last two updates per property. ... My report brings in all the information ok, and I was able to limit the ...
    (microsoft.public.access.gettingstarted)
  • Re: Sum of numbers
    ... Administrator to come in to insure we have this critical report by April. ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: Calculated field question
    ... In a query, you could use a subquery to get the time from the previous row. ... PriorTime: ... If your report performs any aggregation that depends on the subquery field, ...
    (microsoft.public.access.queries)
  • Re: Report based on query w/subquery wont give a Report Footer to
    ... I don't have the underlying query updating the tables. ... the user's entry into the entry form. ... and report on the difference between them. ... multiple Group Bys in a subquery. ...
    (microsoft.public.access.reports)
  • Re: SQL Select Query Help
    ... I should be approaching this as a union and not trying to ... If I type all the rows (pay or deduction) ... > in the case of your report, there IS no logical relationship - so I had to ... > query in chronological order. ...
    (microsoft.public.sqlserver.programming)