如何创建具有列和行总数的Access交叉表查询? [英] How to create an Access crosstab query with totals for the columns AND the rows?

问题描述

我希望查询结果如下所示:

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

我发现了一些有关使用UNION固定到最后一行的信息，但是我似乎不太正确.似乎这应该是常见的活动.

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将两者结合.

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 UNION as you mentioned.

尽管我不得不说我真的不推荐这样做.听起来您好像在试图强制SQL生成真正是表示性信息的东西(即它不属于同一数据集).

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

这篇关于如何创建具有列和行总数的Access交叉表查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！