Multi-level GROUP BY clause is not allowed in a subquery?

[John]

I get the following error when I tried to run a report:

"Multi-level GROUP BY clause is not allowed in a subquery."

The report groups the records on a column called HaulerID. The
recordsource for the report contains the following:

Select *, (Select HaulerID From tblHaulers Where HaulerKEY =
TransHaulerKEY) As HaulerID From tblTransactions

If I get rid of the subquery (for HaulerID) I don't get the error. I
need the subquery since TransHaulerKEY is a foreign key and not a column
value. I don't know how to get around this problem. Can someone help me?
Thank You.

John

 

[Allen Browne]

Yes, this is a common pain-point with reports that rely on a query that
contains a subquery.

You may be able to work around the problem by stacking queries. That is, you
may be able to replace the subquery with a lower-level query, and use it as
an input "table" in the main query. Alternatively, you may be able to keep
the subquery, and then create another query into the original, and use this
higher-level query as the source for the report.

If the query does not have to return the value from the subquery (i.e. it
just uses it for selection purposes), you can uncheck the "Show" box in
query design under your subquery. The subquery then exists only in the WHERE
clause, and that should solve the problem for the report.

Another workaround might be to use a subreport to handle the info that
subquery was doing.

Another is to change the sorting'n'grouping of the report: it is when Access
performs the sorting'n'grouping that it becomes a "multi-level" group by.

Worst scenario is to have to make a temp table to hold the p.k. value of the
records identified by the subquery (or possibly the values returned from the
subquery), and use it as the source of the report, in another query.

Hopefully one of those ideas gets you out of trouble. Stacked queries are
the first thing to try.

 

[John]

Thanks Allen,

You gave me an idea. I used INNER JOINS and that did the trick. Thanks.

John