Access report either not showing data or failing with "Multi-level GROUP BY cause not allowed in subquery."

Question

I have two queries that I am using to generate a report from, the problem is when I run the report, three fields do not show any data at all for some reason.

Query 1:

SELECT ClientSummary.Field3 AS PM, 
       ClientSummary.[Client Nickname 2] AS [Project #], 
       ClientSummary.[Client Nickname 1] AS Customer, 
       ClientSummary.[In Reference To] AS [Job Name], 
       ClientSummary.Field10 AS Contract,

      (select sum([Billable Slip Value]) 
       from Util_bydate as U1 
       where U1.[Client Nickname 2] = ClientSummary.[Client Nickname 2]) 
          AS [This Week],

      (select sum([Billable Slip Value]) 
       from Util as U2 
       where U2.[Client Nickname 2] = ClientSummary.[Client Nickname 2] ) 
          AS [To Date],

      [To Date]/[Contract] AS [% Spent],

      0 AS Backlog, 

      ClientSummary.[Total Slip Fees & Costs] AS Billed, 
      ClientSummary.Payments AS Paid, ClientSummary.[Total A/R] AS Receivable, 

      [Forms]![ReportMenu]![StartDate] AS [Start Date], 
      [Forms]![ReportMenu]![EndDate] AS [End Date]

FROM ClientSummary;

Query 2:

SELECT JobManagement_Summary.pm, 
       JobManagement_Summary.[project #], 
       JobManagement_Summary.Customer, 
       JobManagement_Summary.[Job Name], 
       JobManagement_Summary.Contract, 
       IIf(IsNull([This Week]),0,[This Week]) AS [N_This Week], 
       IIf(IsNull([To Date]),0,[To Date]) AS [N_To Date], [% Spent], 
       JobManagement_Summary.Backlog, 
       JobManagement_Summary.Billed, 
       JobManagement_Summary.Paid, 
       JobManagement_Summary.Receivable, 
       JobManagement_Summary.[Start Date], 
       JobManagement_Summary.[End Date]
FROM JobManagement_Summary;

When I run the report from query 2 these 3 fields don't appear. N_This Week, N_To Date and % Spent. All have no data. It isn't the IIF functions, as it doesn't matter if I have those in there or remove them.

Any thoughts? If I connect directly to the first recordset it works fine, but then SQL throws the error message: Multi-level GROUP BY cause not allowed in subquery.

Is there any way to get around that message to link to it directly or does anyone have ANY clue why these fields are coming back blank? I am at wits end here!

Answer 1

Having been tormented today by what I think is the same problem, I'll record here the steps that resolved it in our case. The key is not to allow Access to take its default route when structuring the internal GROUP BY used in Sorting And Grouping.

Basic problem
You have a report rptFoo whose RecordSource is query qryFoo.

You've applied some Sorting and Grouping to rptFoo, and that's fine. But qryFoo is a little bit complex; it contains a subquery.

You finetune qryFoo to perfection, adjust and readjust its subquery element, and it's all looking good, at least when you test the query standalone. The problems begin when you fire up your report and get this error:

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

This is one of the problems you mention.

Resolution Attempt 1:
Your first result if you google the error will be the excellent Allen Browne site. He has a whole section on the site titled Surviving Subqueries. The best looking of his suggestions for this particular problem is this:

• Create a separate query that handles the subquery. Use this query as a source "table" for the query the report is based on. Moving the subquery to the lower level query sometimes (not always) avoids the problem, even if the second query is as simple as SELECT * FROM Query1;

So you create qryFooWrapper whose content is simply SELECT * FROM qryFoo. You make this the Record Source for rptFoo and, guess what, the report starts loading without errors. Sadly it's also merely showing a blank field instead of the results of your original subquery.

This looks like the initial problem you mention, and we're seemingly at a dead end.

Resolution Attempt 2:
So leaving Allen Browne's suggestions to one side, what else is there to try? Well there's this discussion in Google Groups. The initial suggestion looks like a giant kludge - append a smelly UNION ALL to your initial query. This cannot be the answer.

But wait, half way down the thread comes some illumination. All the UNION ALL is doing is forcing Access to restructure the internal GROUP BY it generates as part of your report. And inserting a simple DISTINCT in the original qryFoo will do the same job, with much less ugliness.

And, voilà, a solution. include a simple DISTINCT in the original query.. No kludgy UNION ALL, no horrid qryFooWrapper and no smelly temporary table.