Error Msg in report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone

Could anybody help me with the following problem

I have a report which is based on a subquery that runs an ‘As Rank’ functio
To number the records. Both the query and the report work fine until I add a sum or count function,
in the report footer. I then get the error message ‘Multi level (group by clause) is not allowed in a subqueryâ€

Is there any way I can work around this

Thank

Kevin
 
Kevin said:
I have a report which is based on a subquery that runs an ‘As Rank’ function
To number the records. Both the query and the report work fine until I add a sum or count function,
in the report footer. I then get the error message ‘Multi level (group by clause) is not allowed in a subquery’
Click to expand...


I think this is a bug because some even more complex queries
are handled correctly. There seems to be a small(?) gap in
what report record source queries Access thinks can be
passed through without extra handling and ones that are so
complex that some kind of "special" handling is used. This
leads me to the following really dumb work around.

If you don't have one already create a table with one row,
it doesn't matter how many or what type of fields it has.

Now, change the report's record source to a new query that
looks something like:

SELECT f1, f2, f3, ...
FROM originalrankingquery
UNION ALL
SELECT 1, "A", 3, ...
FROM onerowtable
WHERE False

In my experience, adding a Union All of an empty dataset
makes this query complex enough to get Access to kick into
its "special" handling mode.

Note 1: The number of constant values in the second select
must be the same as the number of fields in your original
query.

Note 2: Be sure to use UNION ALL and not just UNION.
Using just UNION might be slow because it requires that all
records be compared so duplicate records can be dropped.
 
Back
Top