Access > 查询

 

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

全部显示

全部隐藏

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

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

 注释    不可在 Web 浏览器中使用交叉表查询。如果要在 Web 数据库中运行交叉表查询，必须先使用 Access 打开数据库。

本文内容

---

概述

创建交叉表查询

对标题使用范围或间隔

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

将空值替换为零

交叉表查询提示

---

概述

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

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

---

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

交叉表查询可以显示相同的数据，但它同时在水平方向和垂直方向对数据进行分组，这样数据表可以更紧凑并且更容易阅读。

---

创建交叉表查询

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

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

---

位于此侧的一列、两列或三列包含行标题。用作行标题的字段名称显示在这些列的最上面的一行中。

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

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

此处显示汇总值。

---

创建交叉表查询的方法

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

该向导具有下列优点：

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

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

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

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

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

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

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

返回页首

创建交叉表查询

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

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

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

---

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

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

将启动交叉表查询向导。

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

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

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

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

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

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

返回页首

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

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

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

---

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

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

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

这些设置生成汇总值。

---

创建查询

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

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

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

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

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

为列标题指定固定值

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

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

返回页首

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

交叉表查询的 SQL 语法

交叉表查询在 SQL 中表示为 TRANSFORM 语句。TRANSFORM 语句的语法如下：

TRANSFORM aggfunction

selectstatement

PIVOT pivotfield [IN (value1[, value2[, ...]])]

TRANSFORM 语句包含以下三部分：

---

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

TRANSFORM

SELECT

FROM

GROUP BY

PIVOT

;

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

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

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

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

限制用于行或列标题的值

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

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

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

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

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

返回页首

对标题使用范围或间隔

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

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

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

IIf 的工作方式

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

IIF 语法

IIf(expr, truepart, falsepart)

IIf 函数语法包含以下这些参数 （参数：为操作、事件、方法、属性、函数或过程提供信息的值。）：

---

使用表达式创建范围

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

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

例如，以下所示是一个完整的嵌套 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。

返回页首

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

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

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

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

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

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

返回页首

将空值替换为零

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

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

NZ 语法

Nz(variant [, valueifnull ] )

Nz 函数语法接受以下参数：

---

例如，如果将 Nz 用于名为“Hours Lost”的字段，以将 null 值转换为零，那么最终的表达式将如下所示：

Nz([Hours Lost], 0)

返回页首

交叉表查询提示

返回页首