子查询中不允许多级GROUP BY子句 [英] Multi-level GROUP BY clause not allowed in subquery

问题描述

我在MS Access中有如下查询

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

我已将其放入报告中，现在，当尝试查看报告时，它会显示警报"子查询中不允许使用多级GROUP BY子句"

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"

我没有得到的是查询中甚至没有任何GROUP BY子句，为什么它返回此错误?

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

推荐答案

来自Allen Browne出色的Access技巧网站:幸存子查询

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

您花了半个小时用子查询来构建查询，并验证它们是否全部正常.您基于该查询创建一个报表，然后立即失败.为什么?

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?

问题是由于Access在后台对报表的排序和分组"或聚合做出的响应而引起的.如果它必须汇总报告的数据，那是不允许的多级"分组.

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.

解决方案

• 在报表设计中，从排序和分组"对话框中删除所有内容，并且不要尝试对报表页眉"或报表页脚"中的任何内容进行求和. (在大多数情况下，这不是实际的解决方案.)

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

SELECT * FROM Query1;

使用域汇总函数(例如DSum())代替子查询.虽然对于小型表来说这很好，但是对于大型表来说性能将无法使用.

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.

_{重要说明:我在此重新发布信息，因为我相信Allen Browne明确允许.在他的网站上:}

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

_{权限 您可以出于任何目的(个人，教育，商业，转售...)自由使用这些文章和示例数据库中的任何内容(代码，格式，算法等).我们只要求您在代码中确认该网站，并提供以下注释: '来源: http://allenbrowne.com '改编自: http://allenbrowne.com}

_{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}

这篇关于子查询中不允许多级GROUP BY子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！