SQL 如何使用列和行的总计创建 Access 交叉表查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5781788/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create an Access crosstab query with totals for the columns AND the rows?
提问by HuckIt
I want my query result to look like this:
我希望我的查询结果如下所示：
Person1 Person2 Person3 Person4 Total
Status1 2 4 7 3 16
Status2 0 1 0 3 4
Status3 0 0 0 0 0
Status4 0 1 3 0 4
Total 2 6 10 6 24
I'm able to get everything except that bottom row with:
除了底行之外，我可以获得以下所有内容：
TRANSFORM Count(personName)
SELECT status, Count(status) AS Total
FROM table1
GROUP BY status
PIVOT personName
I found something about using a UNION to tack on the last row, but I can't seem to quite get that right. Seems like this should be a common activity.
我发现了一些关于使用 UNION 来固定最后一行的内容，但我似乎不太正确。看起来这应该是一个常见的活动。
回答by Yuck
You'd basically have to run your query twice - once to get the data and then a second time to provide the aggregates. If you're set on doing this, make the first query to return data its own object. Then make another query to aggregate the first one another object. Create a final third query object to combine the two using a UNIONas you mentioned.
您基本上必须运行两次查询 - 一次获取数据，然后第二次提供聚合。如果您打算这样做，请使第一个查询返回数据作为其自己的对象。然后进行另一个查询以聚合第一个另一个对象。创建最后的第三个查询对象以使用UNION您提到的a 组合两者。
Although I have to say I don't really recommend this. It sounds like you're trying to force the SQL to generate something that's really presentational information (i.e. it doesn't belong in the same dataset).
虽然我不得不说我真的不推荐这个。听起来您正在尝试强制 SQL 生成一些真正具有表现力的信息（即它不属于同一数据集）。
回答by Lydia
There is actually a simple solution to this issue. Once you have designed your crosstab query, go into design mode within the query and select "Totals" in the Records section on the Home tab. Then you can select the Sum or Count etc....
这个问题实际上有一个简单的解决方案。设计交叉表查询后，进入查询中的设计模式并在主页选项卡的记录部分中选择“总计”。然后您可以选择 Sum 或 Count 等....
Here is a link that gives steps: http://office.microsoft.com/en-us/access-help/display-column-totals-in-a-datasheet-HA001233062.aspx
这是一个提供步骤的链接：http: //office.microsoft.com/en-us/access-help/display-column-totals-in-a-datasheet-HA001233062.aspx
回答by Jonathan
I've been looking for a solution too. Could not find one either except writing a query based on the crosstab and then summing that one and adding in to the bottom in a union query. Since I try to do all SQL statements from inside a form (more manageable to deploy) I do not like this approach: writing or refilling a Querydef/view from code etc.
我也一直在寻找解决方案。除了根据交叉表编写查询然后将其求和并添加到联合查询的底部之外，也找不到任何一个。由于我尝试从表单内部执行所有 SQL 语句（更易于部署），因此我不喜欢这种方法：从代码等编写或重新填充 Querydef/视图。
If you display the results in a subform on your form, you might do the following:
如果您在表单的子表单中显示结果，您可以执行以下操作：
below the subform, and another subform short enough to hold only 1 record.
在子窗体下方，另一个子窗体足够短，只能容纳 1 条记录。
Bind the controls in the form to a function as follows:
将表单中的控件绑定到一个函数，如下所示：
control1 = fnADOSum(yourCrosstabfield1, yourCrosstabSQL)
Public Function fnADOSum(fldName As String, strInputSQL As String) As Double
On Error GoTo ERRHANDLER
Dim RS1 As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim StrSQL As String
Dim dblRunTot As Double
Set RS1 = New ADODB.Recordset
RS1.CursorLocation = adUseServer
Set cnn = CurrentProject.Connection
dblRunTot = 0
With RS1
.Open strInputSQL, cnn, adOpenForwardOnly, adLockReadOnly
If Not .EOF And Not .BOF Then
.MoveFirst
Do Until .EOF
dblRunTot = dblRunTot + Nz(.Fields(fldName).Value, 0)
.MoveNext
Loop
End If
.Close
End With
fnADOSum = dblRunTot
'CLEAN UP:
cnn.Close
Set RS1 = Nothing
Set cnn = Nothing
EXITHANDLER:
Exit Function
ERRHANDLER:
'' your own error handling proc
'' LogError err.Number, err.Description
End Function
回答by deGuza
Lydia wrote: "There is actually a simple solution to this issue. Once you have designed your crosstab query, go into design mode within the query and select "Totals" in the Records section on the Home tab. Then you can select the Sum or Count etc...."
Lydia 写道：“这个问题实际上有一个简单的解决方案。一旦你设计了你的交叉表查询，进入查询中的设计模式，并在主页选项卡的记录部分选择“总计”。然后你可以选择总和或数数等等……”
Going into the design mode did not work for me:
进入设计模式对我不起作用：
- I ran the query.
- Then went to the Home tab
- Selected Totals in the Records section
- The label "Totals" appeared at the bottom of the Crosstab query results, but no actual totals yet.
- Clicked on the empty cell to the right of the Totals label.
- An arrow appeared, and I chose "Sum".
- 我运行了查询。
- 然后转到“主页”选项卡
- 记录部分中的选定总计
- 标签“总计”出现在交叉表查询结果的底部，但还没有实际的总计。
- 单击“总计”标签右侧的空单元格。
- 出现一个箭头，我选择了“总和”。
[I am using Access 2013]
[我使用的是 Access 2013]
