The access report either does not show data or does not work with "Multilevel GROUP BY not allowed in subquery".
I have two queries that I am using to generate a report, the problem is when I run the report, the three fields show no data at all for some reason.
Request 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;
Request 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 are not displayed. N_This Week, N_To Date and% Spent. Everyone has no data. These are not IIF functions as it doesn't matter if I have them in there or delete them.
Any thoughts? If I connect directly to the first recordset it works fine, but then SQL gives an error: Multi-level GROUP BY not allowed in subquery.
Is there a way to get around this message to link directly to it, or does anyone know why these fields are being returned? Here I am here!
a source to share
Today, when I was tormented by what I think is the same problem, I will take steps here that resolved this in our case. The key is to prevent Access from using its default route when structuring the inner GROUP BY used for sorting and grouping.
Basic Problem
You have a report rptFoo
whose RecordSource is a query qryFoo
.
You've applied some sorting and grouping in rptFoo
, and that's great. But a qryFoo
little more complicated; it contains a subquery.
You finetune qryFoo
to improve, tweak and reconfigure your subquery item and it all looks good, at least when you test the query separately. Problems start when you run your report and you get this error:
Multilevel GROUP BY clause not allowed in subquery
This is one of the problems you mentioned.
Resolution Attempt 1:
Your first result, if you google error, will be a great Allen Browne site . He has a whole section on the site called Surviving Subqueries . The best look at his suggestions on this particular issue is as follows:
- Create a separate query to handle the subquery. Use this query as the source "table" for the query on which the report is based. Moving a subquery to a 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
, the content is simple SELECT * FROM qryFoo
. You make this the record source for rptFoo
and, guess what, the report starts loading without errors. Unfortunately this also just shows a blank field instead of the results of your original subquery.
This is similar to the initial problem you mention and we seem to be at a dead end.
Solution Attempt 2:
So leaving Allen Brown's suggestions one way, what else is there to try? Ok, this is a discussion in Google Groups . The initial proposal looks like a giant kludge - add smelly UNION ALL to your initial request. This cannot be the answer.
But wait, some lighting comes in halfway downstream. All UNION ALL does is force restructuring access to the internal GROUP BY group that is created as part of your report. And inserting a simple DISTINCT in the original qryFoo
will do the same job with much less ugliness.
And voila, the solution. includes a simple DISTINCT in the original query. ... No kludgy UNION ALL
, no awful qryFooWrapper
and no smelly temporary table.
a source to share