访问交叉表小计列 [英] Access Crosstab subtotal columns

问题描述

关于Access中的交叉表，我有以下问题：
如何创建小计列？

I have a following question regarding crosstabs in Access: How do I create a subtotal columns?

我想从中看到什么查询是这样的：

What I want to see as a result of the query is this:

          Nov 2010     Dec 2010     2010 Total     Jan 2011    Feb 2011 
Row1             2            4             17            3           2
Row2             8            6             35            7           5

如何创建年份的这些小计？ （没关系，如果年份数据将在所有月份之后都结束了）
问题是我需要在每年不进行硬编码的情况下执行此操作，该查询应该适用于任何数据集

How do I create these subtotals for the year? (It's ok, if the year data will be in the end, after all months) The problem is that I need to do this without hardcoding each year, the query should work with any dataset

谢谢！

推荐答案

说我们有原始的[SalesData]

Say we have raw [SalesData]

SalesYear   SalesMonth  Region  SalesTotal
---------   ----------  ------  ----------
2010        11          East    45
2010        11          West    58
2010        12          East    55
2010        12          West    63
2011        1           East    51
2011        1           West    54
2011        2           East    55
2011        2           West    61

我们可以创建一个[SalesTotals]查询，将月销售总额与年销售总额结合起来。

We can create a [SalesTotals] query to combine the monthly sales totals with the yearly totals...

SELECT SalesYear & "-" & Format(SalesMonth, "00") AS SalesPeriod, Region, SalesTotal FROM SalesData
UNION ALL
SELECT SalesYear & "-Total", Region, SUM(SalesTotal) FROM SalesData GROUP BY SalesYear, Region;

...产生

SalesPeriod Region  SalesTotal
----------- ------  ----------
2010-11     East    45
2010-11     West    58
2010-12     East    55
2010-12     West    63
2011-01     East    51
2011-01     West    54
2011-02     East    55
2011-02     West    61
2010-Total  East    100
2010-Total  West    121
2011-Total  East    106
2011-Total  West    115

然后，我们可以在[SalesTotals]查询中执行交叉表查询...

Then we can do our crosstab query on the [SalesTotals] query...

TRANSFORM Sum(SalesTotals.[SalesTotal]) AS SumOfSalesTotal
SELECT SalesTotals.[Region]
FROM SalesTotals
GROUP BY SalesTotals.[Region]
PIVOT SalesTotals.[SalesPeriod];

...将产生

Region  2010-11 2010-12 2010-Total  2011-01  2011-02  2011-Total
------  ------- ------- ----------  -------  -------  ----------
East    45      55      100         51       55       106
West    58      63      121         54       61       115

这篇关于访问交叉表小计列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！