[access查询]交叉表查询的应用

时　间:2009-12-24 15:11:38
作　者:王樵民   ID：5203  城市：郑州
摘　要:概述了建立交叉表查询的方法，并解决了Access2007中的一个错误。
正　文:

点击下载此附件 

交叉表查询的应用

交叉表查询的还是比较有用的，比如我们要统计出各部门年度销售的情况表，就可以利用交叉表查询来实现。

创建交叉表查询的步骤如下：

1.         选择工具：选择“查询向导”工具。

2.         选择向导：选择“交叉表查询向导”，单击“确定”按钮，如图1所示。

图1查询类型的选择

3.         选择表：选择“销售合同”，如图2所示。单击“下一步”按钮。

图2表的选择

4.         选择行标题字段：选择“签订部门”字段，如图3所示。单击“下一步”按钮。

图3选择“签订部门”字段

5.         选择列标题字段：选择“签订日期”，如图4所示，单击“下一步”按钮。

图4选择“签订日期”字段

6.         选择划分间隔：选择“年”，如图5所示，单击“下一步”按钮。

图5时间间隔的选择

7.         选择行与列交叉的计算字段：选择“合同额”，并在函数区选择“汇总”，如图6所示，单击“下一步”按钮。

图6汇总字段“合同额”的选择

8.         修改查询的名称：这里我们取系统所给的默认值“销售合同_交叉表”，选中“察看查询”单选按钮，如图7所示，单击“完成”按钮。

图7为查询起名称

9.         察看结果：其查询德结果如图8所示。

图8查询结果

从图17-92可以看出，利用交叉表查询，对于统计部门销售金额多么方便。

现在我们看一看其交叉表查询向导究竟设计了什么样的SQL查询语句。

在导航栏中选中“销售合同_交叉表”，单击鼠标右键，选设计视图，其设计的查询如图9所示。

图9查询设计视图

切换到SQL视图，其SQL查询语句为：

TRANSFORM Sum(销售合同.合同额) AS 合同额总计

Select 销售合同.签订部门, Sum(销售合同.合同额) AS [总计 合同额]

FROM 销售合同

GROUP BY 销售合同.签订部门

PIVOT Format([签订日期],"yyyy");

其中TRANSFORM就是创建交叉表的语句。PIVOT表示列标题是以什么格式来书写。Format([签订日期],"yyyy")表示按年来构建列标题。

如果我们想要在加入“签订人”的信息，请参照图10的来加入的“签订人”字段。

图10加入“签订人”字段

加入后，其查询结果如图11所示。

图11查询结果

交叉表中的问题：这个实例是笔者创建交叉表查询时遇到的问题，请读者注意其解决方法，这是目前发现的在Access的多个版本中都发现的问题，这就是在创建交叉表查询时（图5中）不能选择月份，如果选择了月份，则结果是不对的，请看下例：

假设在图5中选择了月，其查询结果如图12所示。

图12选择月份后的查询结果

结果是不对的，那个月中也没有数据，这显然是错误的，请看其SQL查询语句：

TRANSFORM Sum(销售合同.合同额) AS 合同额之总计

Select 销售合同.签订部门, Sum(销售合同.合同额) AS [总计 合同额]

FROM 销售合同

GROUP BY 销售合同.签订部门

PIVOT Format([签订日期],"mmm") In ("一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月");

错误的原因是查询向导用到了中文的“一月”、“二月”等等，而在签订日期中并不是这种格式，也就是说日期类型的数据不是中文的一月、二月等等，而是1、2、3......12等数字，所以交叉表查询就不可能得到结果。

改正这个错误只要用PIVOT Year([签订日期]) & "年" & Format([签订日期],"mm") & "月";替换掉

PIVOT Format([签订日期],"mmm") In ("一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月");

这一行即可，其结果如图13所示。

图13正确的查询结果

Access软件网QQ交流群 (群号:54525238) 　　　　　　Access源码网店