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

如果您希望重构汇总数据，使其更容易阅读和理解，请考虑使用交叉表查询。

交叉表查询计算总和、平均值或其他聚合函数 （聚合函数:用于计算总计的函数。例如:Sum、Count、Avg 或 Var。），然后按照两组值对结果进行分组：一组值垂直分布在数据表的一侧，而另一组值水平分布在数据表的顶端。

---

• 概述
• 创建交叉表查询
• 对标题使用范围或间隔
• 提示输入用于限制行标题的参数
• 将空值替换为零
• 交叉表查询提示

---

概述

交叉表查询是一种选择查询 （选择查询:就表中存储的数据提出问题，然后在不更改数据的情况下以数据表的形式返回一个结果集。）。在运行交叉表查询时，结果显示在一个数据表中，该数据表的结构不同于其他类型的数据表。

与显示相同数据的简单选择查询（如下图所示）相比，交叉表查询的结构让数据更易于阅读。

---

http://officeimg.vo.msecnd.net/zh-cn/files/361/374/ZA010235787.gif

---

创建交叉表查询

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

 提示   此外，也可以使用表达式生成行标题、列标题或要汇总的值。

---

http://officeimg.vo.msecnd.net/zh-cn/files/881/804/ZA010235877.gif

---

创建交叉表查询的方法

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

该向导具有下列优点：

• 简单易用。    若要使用该向导，请启动它，然后回答一系列引导问题。
• 它可以自动将日期组合为间隔。    如果对列标题使用包含日期/时间数据的字段，该向导还会帮助您将日期组合为间隔，如月份或季度等。

 提示   在对列标题使用“日期/时间”字段中的值时，如果要将日期组合为该向导不提供的间隔（如财政年度或两年期间），则不要使用该向导创建查询，而应该在设计视图中创建交叉表查询，然后使用表达式创建这些间隔。

• 它可以用作设计起点。    可以使用该向导创建所需的基本交叉表查询，然后使用设计视图精确调整该查询的设计。

但不能使用该向导执行下列任务：

• 将多个表或查询用作记录源 （记录源:窗体、报表或数据访问页的基础数据源。在 Access 数据库中，它可以是表、查询，也可以是 SQL 语句。在 Access 项目中，它可以是表、视图、SQL 语句，也可以是存储过程。）。
• 使用表达式 （表达式:算术或逻辑运算符、常数、函数和字段名称、控件和属性的任意组合，计算结果为单个值。表达式可执行计算、操作字符或测试数据。）创建字段。
• 添加参数 （参数查询:在这种查询中，用户以交互方式指定一个或多个条件值。参数查询不是一个单独种类的查询，而是扩展了查询的灵活性。）提示。
• 指定要用作列标题的固定值的列表。

 注释   在向导的最后一步，可以选择在设计视图中修改查询。这样可以添加向导不支持的查询设计元素，如附加记录源。

在设计视图中工作     通过设计视图，可以对查询设计进行更多的控制。该视图支持向导中未提供的一些功能。

如果您要执行下列任务，请考虑使用设计视图创建交叉表查询：

• 在创建过程中进行更多的控制，而向导会自动为您做出一些决定。
• 将多个表或查询用作记录源。
• 向查询中添加参数提示。
• 将表达式用作查询中的字段。
• 指定要用作列标题的固定值的列表。
• 练习使用设计网格 （设计网格:在查询设计视图或“高级筛选/排序”窗口中设计查询或筛选时所用的网格。对于查询，该网格以前称为“QBE 网格”。）。

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

 提示   请记住，不必局限于一种方法来创建交叉表查询。可以使用向导创建查询，然后再使用设计视图修改查询设计。

http://officeimg.vo.msecnd.net/zh-cn/files/206/661/ZA010077668.gif 返回页首

创建交叉表查询

---

• 使用交叉表查询向导创建交叉表查询
• 在设计视图中创建交叉表查询
• 在 SQL 视图中创建交叉表查询

---

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

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

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

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

将启动交叉表查询向导。

3. 在向导的第一页，选择要用于创建交叉表查询的表或查询。
4. 在下一页上，选择包含要用作行标题的值的字段。

最多可选择三个字段用作行标题源，但使用的行标题越少，交叉表查询数据表就越容易阅读。

 注释   如果选择多个字段来提供行标题，则选择这些字段的顺序将决定对结果排序的默认顺序。

5. 在下一页上，选择包含要用作列标题的值的字段。

通常应选择一个包含很少值的字段，这样有助于使结果易于阅读。例如，最好是使用只包含少量可能值（如性别）的字段，而不是使用包含许多不同值（如年龄）的字段。

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

6. 如果为列标题选择了“日期/时间”字段，则向导的下一页会要求您指定要用于组合日期的间隔。可以指定“年”、“季度”、“月”、“日期”或“日期/时间”。如果没有为列标题选择“日期/时间”字段，则向导会跳过该页。
7. 在下一页上，选择一个字段和一个用于计算汇总值的函数。所选字段的数据类型 （字段数据类型:决定可以存储哪种数据的字段特征。例如，数据类型为“文本”的字段可以存储由文本或数值字符组成的数据，而“数字”字段只能存储数值数据。）将决定哪些函数可用。
8. 在同一页上，选择或清除“是，包括各行小计”复选框以包含或排除行小计。

如果包含行小计，则交叉表查询中有一个附加行标题，该标题与字段值使用相同的字段和函数。包含行小计还会插入一个对其余列进行汇总的附加列。例如，如果交叉表查询按位置和性别（使用性别列标题）计算平均年龄，该附加列会按位置计算平均年龄，而不分性别。

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

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

http://officeimg.vo.msecnd.net/zh-cn/files/206/661/ZA010077668.gif 返回页首

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

通过使用设计视图创建交叉表查询，可以根据需要使用任意多个记录源（表和查询）。不过，您可以让设计简单些，方法是：先创建一个返回所需的全部数据的选择查询，然后将该查询用作交叉表查询的唯一记录源。有关创建选择查询的详细信息，请参阅请参阅部分。

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

---

http://officeimg.vo.msecnd.net/zh-cn/files/840/557/ZA010235788.gif

---

创建查询

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

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

如果使用多个记录源，请确保这些表或查询的共有字段中是联接 （联接:表格或查询中的字段与另一表格或查询中具有同一数据类型的字段之间的关联。联接向程序说明了数据之间的关联方式。根据联接的类型，不匹配的记录可能被包括在内，也可能被排除在外。）的。有关联接表和查询的详细信息，请参阅请参阅部分。

3. 关闭“显示表”对话框。
4. 在“设计”选项卡上的“查询类型”组中，单击“交叉表”。

5. 在查询设计窗口中，双击要用作行标题的源的各个字段。最多可以为行标题选择三个字段。
6. 在查询设计网格中的各个行标题字段的“交叉表”行中，选择“行标题”。

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

7. 在查询设计窗口中，双击要用作列标题的源的字段。只能为列标题选择一个字段。
8. 在查询设计网格中的列标题字段的“交叉表”行中，选择“列标题”。

可以在“条件”行中输入一个表达式来限制列标题字段的结果。但是，对列标题字段使用条件表达式不会限制交叉表查询返回的列数，而是会限制哪些列中包含数据。例如，假设有一个列标题字段包含三个可能值：red、green 和 blue。如果将条件 ='blue' 应用于该列标题字段，则交叉表仍会显示 red 列和 green 列，但只有 blue 列包含数据。

 注释   如果要限制显示为列标题的值，可以通过使用查询的“列标题”属性指定固定值的列表。有关详细信息，请参阅为列标题指定固定值。

9. 在查询设计窗口中，双击要用于计算汇总值的字段。只能选择一个字段用于汇总值。
10. 在查询设计网格中的汇总值字段的“总计”行中，选择一个用于计算这些值的聚合函数。
11. 在汇总值字段的“交叉表”行中，选择“值”。

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

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

为列标题指定固定值

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

1. 在设计视图中打开交叉表查询。
2. 如果未显示属性表，请按 F4 显示它。
3. 在属性表的“常规”选项卡的上方，确保“所选内容的类型”为“查询属性”。如果不是，请在查询设计网格上方的空间中单击一处空白位置。
4. 在属性表的“常规”选项卡上，在“列标题”属性中输入要用作列标题的逗号分隔的值列表。

 注释   有些字符（如大多数标点符号）在列标题中是不允许使用的。如果在值列表中使用这些字符，则 Access 会将所有此类字符替换为下划线 (_)。

http://officeimg.vo.msecnd.net/zh-cn/files/206/661/ZA010077668.gif 返回页首

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

http://officeimg.vo.msecnd.net/zh-cn/files/785/945/ZA079005000.gif交叉表查询的 SQL 语法

SQL 视图不限制可用作交叉表查询的记录源的表或查询的数量。不过，您可以让设计简单些，方法是：创建一个返回要在交叉表查询中使用的全部数据的选择查询，然后将该选择查询用作记录源。有关创建选择查询的详细信息，请参阅请参阅部分。

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

2. 关闭“显示表”对话框。
3. 在“设计”选项卡的“视图”组中，单击“视图”，然后单击“SQL 视图”。
4. 在“SQL 对象”选项卡上，键入或粘贴以下 SQL 语句：

TRANSFORM 
SELECT 
FROM 
GROUP BY 
PIVOT 
;

5. 在第一行上，在 TRANSFORM 后面键入一个用于计算汇总值的表达式；例如，Sum([Amount])。

 注释   如果要将多个表或查询用作记录源，请将表或查询的名称包含在各个字段名中；例如，Sum([Expense].[Amount])。

6. 在第二行上，在 SELECT 后面键入要用于行标题的字段或字段表达式的列表。用逗号分隔各列表项；例如，[Budget].[Dept_ID], [Expense].[Type]。
7. 在第三行上，在 FROM 后面键入要用作记录源的表或查询的列表；例如，Budget, Expense。
8. 在第四行上，在 GROUP BY 后面键入步骤 6 中用于 SELECT 子句的相同字段列表。
9. 在第五行上，在 PIVOT 后面键入一个要用于列标题的字段名或表达式；例如，PIVOT [Budget].[Year]。

向行标题字段添加排序顺序

若要在 SQL 视图中向交叉表查询添加排序顺序，请使用 ORDER BY 子句。

1. 在 GROUP BY 子句和 PIVOT 子句之间插入一行。
2. 在新行上，键入 ORDER BY 并在后面跟一个空格。
3. 键入要对其进行排序的字段名或表达式；例如，ORDER BY [Expense].[Expense_Class]

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

4. 如果要对附加字段或表达式进行排序，请键入一个逗号，然后键入附加字段名或表达式。字段或表达式将按照在 ORDER BY 子句中出现的顺序进行排序。

限制用于行或列标题的值

可以使用下列过程指定要用作列标题的值列表，并向行标题字段中添加条件。这些过程假定交叉表查询已在 SQL 视图中打开。

指定要用作列标题的固定值

• 在 PIVOT 子句末尾，键入 IN 并在后面跟随一个要用作列标题的逗号分隔的值列表（放在括号内）。例如，IN (2007, 2008, 2009, 2010) 会生成四个列标题：2007、2008、2009 和 2010。

 注释   如果指定的固定值与数据透视字段中的字段值不对应，则该固定值将成为空列的列标题。

添加用于限制行标题的查询条件

1. 在 FROM 子句后面插入一个新行。
2. 键入 WHERE 并在后面跟随一个字段条件。

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

http://officeimg.vo.msecnd.net/zh-cn/files/206/661/ZA010077668.gif 返回页首

对标题使用范围或间隔

有时，需要将字段值组合为范围，然后将这些范围用于行或列标题，而不是将各个字段值用于行或列标题。例如，假设您对列标题使用“年龄”字段。您可能更愿意使用表示年龄范围的列，而不是对每个年龄分别使用一列。

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

 提示   如果要使用“日期/时间”字段创建间隔，请考虑使用交叉表查询向导。使用该向导可以将日期组合为以下间隔：“年”、“季度”、“月”、“日期”或“日期/时间”。如果这些间隔都不能满足您的要求，则应在设计视图中创建交叉表查询，然后使用此部分中介绍的方法创建所需的间隔。

IIf 的工作方式

IIf 的工作方式是：对一个表达式进行计算；如果该表达式为 true，则返回一个值；如果该表达式为 false，则返回一个替代值。通过嵌套 IIf 语句，可以创建一个逻辑比较序列。因此，可以使用 IIf 将数值字段值分隔为范围。

http://officeimg.vo.msecnd.net/zh-cn/files/785/945/ZA079005000.gifIIf 语法

使用表达式创建范围

1. 在设计视图中打开交叉表查询。
2. 在查询设计网格的“字段”行中，右击一个空列，然后在快捷菜单上单击“显示比例”。
3. 在“缩放”框中，键入字段别名 （别名 (SQL):表达式中表或字段的可选名称。通常用来缩短表或字段名以便以后在代码中引用，这样可防止可能出现的模糊引用或者在查询输出中提供更具描述性的名称。）并在后面跟随一个冒号 (:)。
4. 键入 IIf()。
5. 在 IIf 后面的括号中，键入一个定义字段值的第一个范围的比较表达式。

例如，假设您要为“年龄”字段创建范围，并希望将每个范围设为二十年。则第一个范围的比较表达式为 [Age]<21。

6. 在比较表达式后面键入一个逗号，然后键入该范围的名称，并将其用双引号括起。提供的名称将成为位于该范围内的值的交叉表标题。

例如，在 [Age]<21 后面键入一个逗号，然后键入 "0-20 years"。

7. 在范围名称后面键入一个逗号（在双引号外面），然后执行下列操作之一：
8. • 若要创建其他范围，请键入 IIf()，然后重复步骤 5、6 和 7。
   • 对于最后一个范围，只需键入该范围的名称即可。

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

IIf([Age]<21,"0-20 years",
 IIf([Age]<41,"21-40 years",
 IIf([Age]<61,"41-60 years",
 IIf([Age]<81,"61-80 years", "80+ years"))))

 注释   当 Access 对该表达式进行计算时，只要某一 IIf 语句的计算结果为 true，它就会停止计算。您不必指定每个范围的下限，因为低于给定范围下限的所有值的计算结果都为 true。

8. 在查询设计网格的“总计”行中，选择“Group By”。
9. 在“交叉表”行中，指定是否将范围用作行标题或列标题。请记住，可以指定一到三个行标题，但只能指定一个列标题。

http://officeimg.vo.msecnd.net/zh-cn/files/206/661/ZA010077668.gif 返回页首

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

您可能希望交叉表查询在运行时提示输入数据。例如，假设要使用多个行标题，其中一个行标题为“国家/地区”。您可能希望查询提示输入一个名称，然后根据用户的输入显示数据，而不是为每个国家或地区显示数据。

可以向任意行标题字段添加参数提示。

 注释   也可以向列标题字段添加参数提示，但这不会限制所显示的列。有关对所显示的列进行限制的详细信息，请参阅为列标题指定固定值部分。

1. 在设计视图中打开交叉表查询。
2. 在需要提示用户进行输入的行标题字段的“条件”行中，键入问题文本（用方括号括起）。在运行查询时，该问题文本将显示为提示。

例如，如果在“条件”行中键入 [Which country or region?]，则在运行查询时，会出现一个对话框，其中包含问题“Which country or region?”、一个输入框以及一个“确定”按钮。

 提示   如果希望能够灵活地输入参数，请使用 Like 运算符将表达式与通配符连接起来。例如，可以使用 Like [Which country or region?]&"*" 使参数与更大的输入范围相匹配，而不是将 [Which country or region?] 用作条件。使用 Like 不会更改参数提示的外观。

3. 在“设计”选项卡的“显示/隐藏”组中，单击“参数”。
4. 在“查询参数”对话框的“参数”列中，输入“条件”行中使用的相同参数提示。请包含方括号，但不要包含任何连接的通配符或 Like 运算符。
5. 在“数据类型”列中，为参数选择数据类型。该数据类型应与行标题字段的数据类型相匹配。

http://officeimg.vo.msecnd.net/zh-cn/files/206/661/ZA010077668.gif 返回页首

将空值替换为零

如果在交叉表查询中用于计算汇总值的字段包含空值，则所使用的任意聚合函数都会忽略这些值。对于某些聚合函数，这可能会影响到结果。例如，若要计算平均值，应将所有值相加并将相加结果除以这些值的数量。但如果字段包含任意空值，则这些空值将不会被计入值数量。

在有些情况下，您可能希望将所有空值都替换为零，以便在执行聚合计算时会计入这些值。可以使用 Nz 函数将空值替换为零。

http://officeimg.vo.msecnd.net/zh-cn/files/785/945/ZA079005000.gifNz 语法

1. 在设计视图中打开查询后，在查询设计网格中右击“值”字段。
2. 在快捷菜单中，单击“显示比例”。
3. 在“缩放”框中，将字段名或表达式放在括号，然后在括号前面键入 Nz。

http://officeimg.vo.msecnd.net/zh-cn/files/206/661/ZA010077668.gif 返回页首

交叉表查询提示

• 让交叉表查询简单些    随着行组合数量的增大，交叉表查询可能变得难以阅读。因此请尽量少用行标题。
• 考虑分步生成交叉表查询    请不要仅局限于使用表这一种方法。通常还可以先生成总计查询 （总计查询:这种查询可针对表（一个或多个）中各不同字段显示汇总计算，如平均值或总计值。总计查询不是一个单独种类的查询，而是扩展了选择查询的灵活性。），然后将该查询用作交叉表查询的记录源。
• 慎重选择列标题字段    当列标题的数量保持相对较少时，交叉表数据表往往更容易阅读。在确定要用作标题的字段后，请考虑使用具有最少明确值的字段来生成列标题。例如，如果查询按年龄和性别计算值，请考虑对列标题使用性别而不是年龄，因为性别的可能值通常要比年龄少。
• 在 WHERE 子句中使用子查询    在交叉表查询中，可以将子查询中用作 WHERE 子句的一部分。