将摘要行添加到MS Access查询 [英] Add a summary row to MS Access query

问题描述

我在MS Access中存储了一个查询，该查询正在从Access表中进行标准选择.我想在末尾添加一个摘要行，以显示上述某些数据的总和.

I have a query stored in MS Access which is doing a standard select from an Access table. I would like to add a summary row at the end showing sums for some of the data above.

我已经看过DSum()，但是它不合适，因为我必须在每行而不是最后一行中都包含运行总计.

I have looked at DSum() but it isn't suitable as I would have to include the running total on each row as opposed to just the end.

另外，请注意，我不想汇总a列中的数据-我想为a列的摘要获取一个空字段.

Also, note that I don't want to sum data in column a - I would like to get an empty field for the summary of column a.

示例:

a | b | c
-------------
0 | 1 | 2
1 | 1 | 9

  | 2 | 11 <-- Sums data above

有人知道如何在Access中解决此问题吗?一种替代方法是定义第二个查询，该查询将进行汇总，然后将其与第一个查询的记录集合并，但这对我而言似乎并不特别.

Does anyone know how this problem can be solved in Access? An alternative might be to define a second query which does the aggregation and then merge it with the recordset of the first one, but this doesn't seem particularly elegant to me.

在SQL Server中，显然可以使用"COMPUTE"或"ROLLUP"，但MS Access不支持它们.

In SQL server it is apparently possible to use "COMPUTE" or "ROLLUP" but these are not supported under MS Access.

推荐答案

您可以使用联合查询:

SELECT "" As Sort, a,b,c FROM Table
UNION ALL
SELECT "Total" As Sort, Sum(a) As A, Sum(b) As b, Sum(c) As C FROM Table
ORDER BY Sort

SELECT "" As Sort, a,b,c FROM Table
UNION ALL
SELECT "Total" As Sort, "" As A, Sum(b) As b, Sum(c) As C FROM Table
ORDER BY Sort

这篇关于将摘要行添加到MS Access查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！