Solved "Multi-level GROUP BY clause is not allowed in a subquery" (1 Viewer)

jumnhy

Member
Local time
Today, 08:59
Joined
Feb 25, 2021
Messages
68
Hi all,

1615919334830.png

Any idea why I get this error when I go to create a report off of the query in question, but the query otherwise runs fine (ie, I can hit "run", and look at the datasheet view of the query, no error. Base a report off of that query, go to run the report, above error.

Copy the query, and just do a SELECT * into a new query, run the report off of that, no popup error, but an #Error message appears the field based off of the underlying subquery. Again, the query and subquery run fine on their own; the issue seems to be in using it for a report.

Any ideas?

Query is a mess, but see below:

SELECT Q.LastName AS [Last], Q.FirstName AS [First], Q1.LastName AS TrainerLastName, Q1.FirstName AS TrainerFirstName, qry1.Process, qry1.mostRecentDate, t1.trbpnotes, tblSOP.bpname AS ProcessName, tblSOP.bpnumber AS SOPNumber, (SELECT TOP 1 CD.crtlDocRev FROM tblMasterListControlledDocs AS CD LEFT JOIN tblSOP AS S on CD.[ctrlDocDoc#] = S.bpnumber WHERE CD.ctrlDocDate <= qry1.mostRecentDate AND S.bpid = qry1.Process) AS RevID
FROM (((qry1 INNER JOIN tblTrainingRecords AS t1 ON (qry1.Employee = t1.trbpEmp) AND (qry1.Process = t1.trbpnumber) AND (qry1.mostRecentDate = t1.trbpdate)) LEFT JOIN ForQData AS Q ON qry1.Employee = Q.ID) LEFT JOIN ForQData AS Q1 ON t1.trbpTrainer = Q1.ID) LEFT JOIN tblSOP ON qry1.Process = tblSOP.bpid
WHERE (((Q.ID)=[Forms]![frmEmpSelect]![Combo6]));

The issue is with:

(SELECT TOP 1 CD.crtlDocRev FROM tblMasterListControlledDocs AS CD LEFT JOIN tblSOP AS S on CD.[ctrlDocDoc#] = S.bpnumber WHERE CD.ctrlDocDate <= qry1.mostRecentDate AND S.bpid = qry1.Process) AS RevID

qry1 is:

SELECT t1.trbpEmp AS Employee, t1.trbpnumber AS Process, Max(t1.trbpdate) AS mostRecentDate
FROM tblTrainingRecords AS t1
GROUP BY t1.trbpEmp, t1.trbpnumber;

So it would appear that the issue is in this "nesting" of queries. Is there a way around this?



EDIT:

I think I've answered my own question. Allen Browne on point as ever:

The relevant passage for me:
"
  • In report design, remove everything form the Sorting and Grouping dialog, and do not try to sum anything in the Report Header or Report Footer. (In most cases this is not a practical solution.)
  • In query design, uncheck the Show box under the subquery. (This solution is practical only if you do not need to show the results of the subquery in the report.)
  • 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;
  • Use a domain aggregate function such as DSum() instead of a subquery. While this is fine for small tables, performance will be unusable for large ones.
  • If nothing else works, create a temporary table to hold the data for the report. You can convert your query into an Append query (Append on Query menu in query design) to populate the temporary table, and then base the report on the temporary table."

I don't actually have any grouping or sorting going on at the moment (would like to do that in the future...) and I'd already tried pushing everything under a rug (eg, throwing it into another query),

BUT (and I'm adding all this because it's wasn't mentioned elsewhere that I could find--)

If you do ANY calculated expression in the header or footer, it throws the error. I had a =[First] & " " & [Last] textbox in the header. Deleted that, and no more error.

Access is such a shitshow.
 

Attachments

  • 1615920294055.png
    1615920294055.png
    7.9 KB · Views: 239
  • 1615920336675.png
    1615920336675.png
    57.8 KB · Views: 263
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:59
Joined
Jan 14, 2017
Messages
18,186
That's an error I've never seen in 20+ years of using Access...presumably because I may never have done what the error says.
What happens if you:
a) try removing the subquery which causes the issue?
b) break this down into a number of separate queries?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:59
Joined
Oct 29, 2018
Messages
21,358
See if anything from here helps...

 

jumnhy

Member
Local time
Today, 08:59
Joined
Feb 25, 2021
Messages
68
That's an error I've never seen in 20+ years of using Access...presumably because I may never have done what the error says.
What happens if you:
a) try removing the subquery which causes the issue?
b) break this down into a number of separate queries?
Proud to be a trailblazer, even if the trail goes off a cliff...

Yeah, I pull the subquery out, and it works. But I can't figure out a better way to do it without a subquery. Some folks here were kind enough to help me with that initially a few days ago--


But basically, this query has to a.) select the most recently dated training, and then b.) match that training with the appropriate revision of the associated document based on the training date.

I haven't come up with a more elegant solution.
 

jumnhy

Member
Local time
Today, 08:59
Joined
Feb 25, 2021
Messages
68
Just popping back in to add--

Looks like any standard fields in the header or footer will break, throwing the "Multiple ..." error. Not just calculated fields.

Calculated/expression based controls in the report details also break the report, though instead, you get a #Value or #Type or "Error 0" error where the value should be. Or sometimes it just shows as blank.

So now my already bloated query is even more bloated. It was already bloated as I moved everything to bound controls, but it looks like i'm not just bound any more, i'm fully hogtied.

Thanks to all for your help!
 

isladogs

MVP / VIP
Local time
Today, 12:59
Joined
Jan 14, 2017
Messages
18,186
Interestingly, I have Allen's article bookmarked but I've never fallen foul of that particular part of the article on surviving subqueries.
Anyway, glad to see you have a workround
 

LALovelady

New member
Local time
Today, 07:59
Joined
Apr 6, 2021
Messages
17
I'm having the same issue. When I try to create a report from my qryMeterDiff, I get the "Multi-level GROUP BY clause is not allowed in a subquery message." I'm pretty sure it has to do with the calculated fields in my qryMeterDiff (ScreenInDiff, ScreenOutDiff, MeterInDiff, MeterOutDiff). See rptMeters.
 

Attachments

  • SEVDatabase-NEW.accdb
    2.5 MB · Views: 247

jumnhy

Member
Local time
Today, 08:59
Joined
Feb 25, 2021
Messages
68
Hi @LALovelady , I think the mods will likely ask you to create your own separate thread so that your problem can be addressed on its own.

I took a peek at your query and reports regardless.

One thing that I noticed in playing around when I had similar issues--

The Report's in-built group and sort functionality seem to also cause the problem. It looks like the point of the ScreenIn/Out/Diff and MeterIn/Out/Diff is to track the running usage totals compared to the last reading, correct?
 

LALovelady

New member
Local time
Today, 07:59
Joined
Apr 6, 2021
Messages
17
Thanks for the info. I'm new to the Forum and was wondering if I needed to start a new thread or not. Will do that. You are correct in regards to the point of the ScreenIn/Out/Diff and MeterIn/Out/Diff. Basically the differences of the current compared to that last meter readings should be equal. (ScreenIn Diff should equal MeterIn Diff / ScrrenOut Diff should equal MeterOut Diff).
 

jumnhy

Member
Local time
Today, 08:59
Joined
Feb 25, 2021
Messages
68
Gotcha. Well, something I tried based on Allen Browne's site referenced earlier in the thread:

Hide your query with the nested subqueries in a new query--eg, make qryMeterDiffsHider, (SELECT * FROM qryMeterDiffs), and run the report on that.

When trying this on the limited dataset provided, it worked for me. See attached.

Best of luck, and welcome to the forum!
 

Attachments

  • SEVDatabase-FIX.accdb
    1.6 MB · Views: 280

LALovelady

New member
Local time
Today, 07:59
Joined
Apr 6, 2021
Messages
17
Thanks for the info. I'm new to the Forum and was wondering if I needed to start a new thread or not. Will do that. You are correct in regards to the point of the ScreenIn/Out/Diff and MeterIn/Out/Diff. Basically the differences of the current compared to that last meter readings should be equal. (ScreenIn Diff should equal MeterIn Diff / ScrrenOut Diff should equal MeterOut Diff).
I just reposted in new thread.
 

Users who are viewing this thread

Top Bottom