Multi-level GROUP BY clause not allowed in subquery

Question

I have a query as follows in MS Access

SELECT tblUsers.Forename, tblUsers.Surname, 
  (SELECT COUNT(ID) 
     FROM tblGrades 
     WHERE UserID = tblUsers.UserID 
     AND (Grade = 'A' OR Grade = 'B' OR Grade = 'C')) AS TotalGrades
FROM tblUsers

I've put this into a report and now when trying to view the report it displays an alert "Multi-level GROUP BY clause is not allowed in subquery"

What I dont get is I dont even have any GROUP BY clauses in the query so why is it returning this error?

Answer 1

From Allen Browne's excellent website of Access tips: Surviving Subqueries

Error: "Multi-level group by not allowed"

You spent half an hour building a query with subquery, and verifying it all works. You create a report based on the query, and immediately it fails. Why?

The problem arises from what Access does behind the scenes in response to the report's Sorting and Grouping or aggregation. If it must aggregate the data for the report, and that's the "multi-level" grouping that is not permitted.

Solutions

• 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.

_{IMPORTANT NOTE: I'm reposting the info here because I believe Allen Browne explicitly allows it. From his website:}

_{Permission You may freely use anything (code, forms, algorithms, ...) from these articles and sample databases for any purpose (personal, educational, commercial, resale, ...). All we ask is that you acknowledge this website in your code, with comments such as: 'Source: http://allenbrowne.com 'Adapted from: http://allenbrowne.com}