使用交叉表查询让汇总数据更容易阅读

若要使 Access 中的汇总数据更易于阅读和理解，请考虑使用交叉表查询。 交叉表查询计算总和、平均值或其他聚合函数，然后按照两组值对结果进行分组 — 一组值位于数据表的一侧，另一组值横跨数据表的顶端。 在功能区中，单击“创建”，然后在“查询”组中，单击“查询向导”。 在“新建查询”对话框中，双击“交叉表查询向导”。

如果标题设置有日期值，该向导将帮助您通过标准时间间隔（如月份或季度）对记录进行分组。

概述

使用交叉表查询向导创建交叉表查询

在设计视图中创建交叉查询

在 SQL 视图中创建交叉表查询

对标题使用范围或间隔

提示输入用于限制行标题的参数

将空值替换为零

交叉表查询提示

注意: 交叉表查询在 Access Web 应用中不可用，且 Web 数据库仅对其提供部分支持：Web 对象无法使用交叉表查询。

交叉表查询是一种 选择查询。 运行交叉表查询时，查询结果显示在结构与其他数据表类型不同的数据表中。

相较于显示同一数据的简单选择查询，交叉表查询的结构让查询本身更易于阅读，如下图所示。

1. 此选择查询按员工和类别在垂直方向对汇总数据进行分组。

2.交叉表查询显示同一数据，但会将数据以水平和垂直方式分组，让数据表显得更紧凑且更易于阅读。

在创建交叉表查询时，您可以指定哪些字段包含行标题，哪些字段包含列标题，以及哪些字段包含要汇总的值。 每次指定列标题和要汇总的值时，您只能使用一个字段。 当您指定行标题时，最多可使用三个字段。

还可以使用表达式生成行标题、列标题或值以进行汇总。 请参阅 SQL 聚合函数，了解详细信息。

1. 该侧的一列、两列或三列包含行标题。 用作行标题的字段名称显示在这些列的首行。

2. 行标题在此处显示。 由于显示所有行标题组合，因此在使用多个行标题字段时，交叉表数据表中的行数会迅速增加。

3. 位于此侧的列包含列标题和汇总值。 请注意，列标题字段的名称不在数据表中显示。

4. 此处显示汇总值。

使用交叉表查询向导    交叉表查询向导通常是创建交叉表查询的最快速、最简单的方法。 它为你完成大部分工作，但向导不提供一些选项。

此向导具有以下优点：

易于使用。 使用时，启动向导，然后回答一系列引导性问题。

它可自动将日期归入不同的时间间隔。 如果对列标题使用包含日期/时间数据的字段，向导也会帮助将日期归入不同的时间间隔，如月份或季度。

提示: 如果想要对列标题使用“日期/时间”字段中的值，但又想将日期归入向导未提供的不同时间间隔（如财政年度或两年期），则不要使用向导来创建查询。 这时请在“设计”视图中创建交叉表查询，并使用表达式创建间隔。

可以此作为起点。 可以使用此向导创建所需的基本交叉表查询，然后通过使用设计视图微调查询的设计。

但使用向导时无法：

将多个表或查询用作 记录源

使用 表达式 创建字段。

添加参数提示。

指定用作列标题的固定值列表。

在向导的最后一步，可以选择修改“设计”视图中的查询。 此操作允许添加向导不支持的查询设计元素，如其他记录源。

在设计视图中工作     设计视图允许你更好地控制查询设计。 它支持向导中不可用的功能。

如果想实现以下目的，可考虑使用设计视图来创建交叉表查询：

加大对流程的控制力度。 通过向导制定某些决策。

将多个表或查询用作记录源。

将参数提示添加到查询。

将表达式用作查询中的字段。

指定用作列标题的固定值列表。

使用 设计网格 进行练习。

在 SQL 视图中编写查询    如果需要，可以在 SQL 视图中编写交叉表查询。 但是，不能使用 SQL 视图指定参数数据类型。 如果要在交叉表查询中使用参数，则必须通过在“设计”视图中修改查询来指定参数数据类型。

提示: 请记住 — 交叉表查询的创建方法不止一种。 可以使用向导来创建查询，然后使用设计视图来修改查询设计。

页面顶端

使用交叉表查询向导要求使用单个表或查询作为交叉表查询的记录源。 如果单个表未包含在交叉表查询中要包含的全部数据，请首先创建将返回所需数据的选择查询。 有关创建选择查询的详细信息，请参阅另请参阅部分。

对于此示例，我们将使用 Northwind 示例数据库中的“产品”表来创建交叉表查询。 我们想显示各类别中来自所有供应商的的产品数量的计数。

在“创建”选项卡上的“查询”组中，单击“查询向导”。

在“新建查询”对话框中，单击“交叉表查询向导”，然后单击“确定”。

将启动交叉表查询向导。

在向导的第一页，选择要用于创建交叉表查询的表或查询。 对于此示例，选择“产品”表，然后单击“下一步”。

在下一页上，选择包含要用作行标题的值的字段。 最多可选择三个字段用作行标题源，但使用的行标题越少，交叉表查询数据表就越容易阅读。 如果选择多个字段来提供行标题，则选择字段的顺序确定了对结果进行排序的默认顺序。

对于此示例，选择“供应商 IDs.Value”，然后单击标记有“>”符号的按钮。 注意，Access 在对话框底部的示例查询预览左侧显示字段名称。 单击“下一步”继续。

在下一页上，选择包含要用作列标题的值的字段。 通常，您应选择包含少量值的字段，以帮助保持您的结果易于阅读。 例如，使用只包含少量可能值（如性别）的字段可能优于使用包含许多不同值（如年龄）的字段。

如果选择用于列标题的字段具有“日期/时间”数据类型，则向导会增加一个步骤，使您能够指定将日期组合为间隔（如月份或季度）的方式。

对于此示例，选择“类别”，注意，Access 在对话框底部的示例查询预览的顶部显示类别示例名称。 单击“下一步”继续。

如果您为列标题选择“日期/时间”字段，向导的下一页将要求您指定用于对日期进行分组的时间间隔。 您可以指定年、季度、月、日期或日期/时间。 如果您没有为列标题选择“日期/时间”字段，则该向导将跳过此页。

在下一页，选择用于计算汇总值的字段和函数。 您选择的字段的数据类型将决定可用的函数。

在同一页上，选择或清除“是，包括各行小计”复选框以包括或排除行小计。

如果包括各行小计，交叉表查询拥有一个与字段值一样，使用相同字段和函数的附加行标题。 包括一个插入到附加列的行总和，该附加列汇总了剩余的列。 例如，如果交叉表查询通过位置和性别（性别列标题）计算平均年龄，附加列将按位置计算所有性别的平均年龄。

对于此示例，选择“字段”框中的“ID”和“函数”框中的“计数”，以使 Access 对供应商和类别的每个相交处的产品数量进行计数。 勾选“是，包括各行小计”复选框。 Access 会创建一列，用于计算和显示每个供应商的产品总数。 单击“下一步”继续。

在向导的最后一页上，键入查询的名称，然后指定是查看结果还是修改查询设计。

可以通过在设计视图中编辑交叉表查询来更改用于生成行总和的函数。

如果已使用来自 Northwind 数据库的“产品”表浏览完此示例，则交叉表查询会将供应商名称列表显示为行、产品类别名称列表显示为列，并在每个交叉处显示产品数量的计数。

页面顶端

通过使用设计视图来创建交叉表查询，可以使用所需数量的记录源（表和查询）。 但可以先创建返回全部所需数据的选择查询，然后将该查询用作交叉表查询的唯一记录源，以此让设计保持简洁。 有关创建选择查询的详细信息，请参阅另请参阅部分。

当在设计视图中生成交叉表查询时，使用设计网格中的“总计”和“交叉表”行指定成为列标题的字段值、成为行标题的字段值，以及用于计算和、平均值、计数或其他计算的字段值。

1. 这些行中的设置决定字段是行标题、列标题还是汇总值。

2. 此设置将字段值显示为行标题。

3. 此设置将字段值显示为列标题。

4. 这些设置生成汇总值。

创建查询

在“创建”选项卡上的“查询”组中，单击“查询设计”。

在“显示表”对话框中，双击要用作记录源的各个表或查询。

如果使用多个记录源，请确保这些表或查询连接到他们共有的字段上。 有关联接表和查询的详细信息，请参阅“另请参阅”部分。

关闭“显示表”对话框。

在“设计”选项卡的“查询类型”组中，单击“交叉表”。

在查询设计窗口中，双击要用作行标题源的每个字段。 您可以为行标题选择三个字段。

在查询设计网格中的各个行标题字段的“交叉表”行中，选择“行标题”。

您可以在“条件”行中输入一个表达式来限制该字段的结果。 您还可以使用“排序”行指定字段的排序顺序。

在查询设计窗口中，双击要用作行标题源的字段。 您只可以为列标题选择一个字段。

在查询设计网格中的列标题字段的“交叉表”行中，选择“列标题”。

您可以在“条件”行中输入一个表达式以限制该列标题字段的结果。 但是，使用带有列标题字段的条件表达式不限制交叉表查询返回的列数。 相反，它限制哪些列包含数据。 例如，假设列标题字段有三个可能值：红色、绿色和蓝色。 如果将条件 =‘蓝色’应用于列标题字段，交叉表仍会显示为红色列和绿色列，但是只有蓝色列包含数据。

如果希望限制显示为列标题的值，可以通过使用查询的列标题属性指定固定值列表。 有关详细信息，请参阅下一部分。

在查询设计窗口中，双击要用于计算汇总值的字段。 您只可以选择一个字段用于汇总值。

在查询设计网格中的汇总值字段的“总计”行中，选择一个用于计算这些值的聚合函数。

在汇总值字段的“交叉表”行中，选择“值”。

不能为汇总值字段指定条件，也不能在该字段上进行排序。

在“设计”选项卡上的“结果”组中，单击“运行”。

为列标题指定固定值

如果要指定用于列标题的固定值，您可以设置查询的“列标题”属性。

在设计视图中打开交叉表查询。

如果属性表不可见，请按 F4 显示该表。

在“常规”选项卡上方的属性表中，确保“所选内容的类型”是“查询属性”。 如果不是，请单击查询设计网格上方的空间中的空白处。

在属性表的“常规”选项卡上，在“列标题”属性中输入要用作列标题的值列表，以逗号分隔。

列标题中不允许使用某些字符（如大多数标点符号）。 如果在值列表中使用这些字符，Access 用下划线（_）代替所有此类字符。

交叉表查询的 SQL 语法

交叉表查询在 SQL 中以 TRANSFORM 语句来表示。 TRANSFORM 语句包含以下语法：

TRANSFORM aggfunctionselectstatement PIVOT 透视字段 [IN (value1[， value2[， ...]]) ]

TRANSFORM 语句包含以下部分：

部分

说明

aggfunction

对所选数据进行操作的 SQL 聚合函数。

selectstatement

SELECT 语句。

pivotfield

要用于在查询结果集中创建列标题的字段或表达式。

value1, value2

用于创建列标题的固定值。

SQL 视图不限制可用作交叉表查询记录源的表或查询的数量。 但可以先创建一个会返回要在交叉表查询中使用的全部数据的选择查询，然后将该选择查询用作记录源，以此让设计保持简洁。 有关创建选择查询的详细信息，请参阅另请参阅部分。

在“创建”选项卡上的“其他”组中，单击“查询设计”。

关闭“显示表对话框。

在“设计”选项卡上的“视图”组中，单击“视图”，然后单击“SQL 视图”。

在“SQL 对象”选项卡中，键入或粘贴以下 SQL：

在第一行的“TRANSFORM”之后，键入用于计算汇总值的表达式；例如 Sum([Amount])。

如果使用多个表或查询作为记录源，则将表或查询名称包含为每个字段名称的一部分；例如 Sum([Expense].[Amount])。

在第二行的“SELECT”之后，键入想要对行标题使用的字段或字段表达式列表。 使用逗号分隔列表项；例如 [Budget].[Dept_ID]，[Expense].[Type]。

在第三行的“FROM”之后，键入当前用作记录源的表或查询的列表；例如“预算”、“费用”。

在第四行的“GROUP BY”之后，键入步骤 6 中在 SELECT 子句中使用的字段的列表。

在第五行的“PIVOT”之后，键入想要为列标题使用的字段名称或表达式；例如 PIVOT [Budget].[Year]。

要将排序顺序添加到 SQL 视图中的交叉查询，请使用 ORDER BY 子句。

在 GROUP BY 子句和 PIVOT 子句之间插入行。

在新的一行中，键入 ORDER BY，后接一个空格。

键入想要排序的字段的名称或表达式；例如 ORDER BY [Expense].[Expense_Class]

默认情况下，ORDER BY 子句按升序对值进行排序。 如果想要按降序排序，在字段名称或表达式之后键入 DESC。

如果同时想对另一个字段或表达式进行排序，则键入逗号，然后键入另一个字段名称和表达式。 排序的先后顺序依循字段或表达式在 ORDER BY 子句中的顺序。

在交叉表查询的 SQL 视图中的 PIVOT 子句末尾，键入 IN，后接用逗号分隔的、用作列标题的值的列表（放在括号中）。 例如 IN (2007, 2008, 2009, 2010) 会生成四个列标题：2007、2008、2009、2010。

如果指定一个固定值，该值不对应核心字段中的字段值，则该固定值成为空列的列标题。

在交叉表查询的 SQL 视图中的 FROM 子句之后插入新行。

键入 WHERE，后接字段条件。

如果想要使用附加条件，可以使用 AND 和 OR 运算符来扩展 WHERE 子句。 还可以使用括号来将条件归组为逻辑集。

有时，想要将字段的值分组为范围，然后对行或列标题使用这些范围，而不是对行或列标题使用字段的每个值。 例如，假设对列标题使用“年龄”字段。 可能更希望使用代表年龄范围的列，而不是对每个年龄分别使用一列。

可以在表达式中使用 IIf 函数来创建用于行或列标题的范围。

提示: 如果要创建带有“日期/时间”字段的时间间隔，请考虑使用交叉表查询向导。 该向导可让您将日期分组为“年”、“季度”、“月”、“日期”或“日期/时间”间隔。 如果所有这些间隔是都不是您想要的，您应该在设计视图中创建交叉表查询，然后使用本节所述的方法来创建所需的间隔。

在设计视图中打开交叉表查询。

在查询设计网格的“字段”行中，右键单击一个空列，然后在快捷菜单上单击“缩放”。

在“缩放”框中，键入字段别名，并且后跟一个冒号 (:)。

键入 IIf()。

在 IIf 后面的括号中，键入一个定义字段值第一个范围的比较表达式。

例如，假定正在创建“年龄”字段的范围，且您希望每个范围是 20 年。 第一个范围的比较表达式是 [年龄]< 21。

在比较表达式后键入一个逗号，然后键入范围的名称，并用引号括起来。 您提供的名称为该范围之内值的交叉表标题。

例如，在 [年龄]< 21后面键入一个逗号，然后键入“0 — 20 岁”。

在范围名称后面键入一个逗号（在双引号外面），然后执行下列操作之一：

若要创建其他范围，键入 IIf()，然后重复步骤 5、6 和 7。

对于最后一个范围，只需键入该范围的名称即可。

例如，一个将“年龄”字段分隔为二十年的范围的完整嵌套 IIf 表达式如下所示（为易于阅读，添加了换行符）：