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

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

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

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

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

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

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

创建查询

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

   

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

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

3. 关闭“显示表”对话框。

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

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

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

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

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

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

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

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

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

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

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

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

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

为列标题指定固定值

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

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

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

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

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

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

交叉表查询的 SQL 语法

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

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

TRANSFORM 语句包含以下部分：

部分	说明
aggfunction	对所选数据进行操作的 SQL 聚合函数。
selectstatement	SELECT 语句。
pivotfield	要用于在查询结果集中创建列标题的字段或表达式。
value1, value2	用于创建列标题的固定值。

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”之后，键入当前用作记录源的表或查询的列表；例如“预算”、“费用”。

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

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

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

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

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

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

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

4. 键入 IIf()。

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

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

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

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

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

   • 若要创建其他范围，键入 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. 在查询设计网格的“总计”行中，选择“分组依据”。

9. 在“交叉表”行中，指定是将范围用作行标题还是列标题。 请记住，您可以指定一个到三个行标题，和一个列标题。

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

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

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

2. 在您要为其提示用户输入的行标题字段的“条件”行中，在方括号中键入问题文本。 在运行查询时，该问题文本将显示为提示。

   例如，如果在“条件”行中键入 [哪个国家或地区？]，在运行查询时，将会出现一个对话框，其中包含问题“哪个国家或地区？”、一个输入框和一个“确定”按钮。

   提示: 如果希望参数灵活，请使用 Like 运算符将表达式与通配符连接。 例如，可以使用“喜欢”[哪个国家或地区？]作为你的条件，而不是使用[哪个国家或地区？]&“*”以使参数匹配更大的输入范围。 使用 Like 不会更改参数提示的外观。

3. 在“设计”选项卡的“显示/隐藏”组中，单击“参数”。

4. 在“查询参数”对话框中的“参数”列中，输入您在“条件”行中使用的相同参数提示。 包括方括号，但不包含任何连接的通配符或 Like 运算符。

5. 在“数据类型”列中，选择参数的数据类型。 数据类型应与行标题字段的数据类型相匹配。

如果您用于在交叉表查询中计算汇总值的字段包含空值，您所使用的任何聚合函数将忽略这些值。 对于某些聚合函数，结果可能受到影响。 例如，若要计算平均值，请您添加所有值并用结果除以值的个数。 但如果字段包含有任何空值，这些空值不计为值个数的一部分。

在某些情况下，您可能希望将所有空值替换为零，以便这些值将在聚合计算期间被计算在内。 您可以使用 Nz 函数将空值替换为零。

Nz 语法

Nz ( variant [, valueifnull ] )

Nz 函数的语法有以下参数：

参数	说明
Variant	必需。 可变的数据类型变量。
valueifnull	可选（除非用于查询中）。 如果变量参数为空，提供值的变量将被返回。 此参数允许您返回非零值或零长度的字符串。 注意: 如果在查询表达式中使用 Nz 函数而不使用 valueifnull 参数，那么在包含空值的字段中结果将是零长度字符串。

1. 在设计视图中打开查询后，在查询设计网格中右键单击“值”字段。

2. 在快捷菜单中，单击“缩放”。

3. 在“缩放”框中，将字段名或表达式放在括号中，然后在括号前面键入 Nz。

4. 在右侧括号内直接键入， 0。

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

Nz([Hours Lost], 0)

• 使其保持简单    随着行组合数量的增加，交叉表查询可能变得难以阅读。 请勿使用超出所需数量的行标题。

• 考虑将交叉表建造在步骤中    请不要局限于仅使用表。 您通常可以先建造总计查询，然后将该查询用作交叉表查询的记录源。

• 仔细选择自己的列标题字段    当列标题数保持相对较小时，交叉表数据表将更易于阅读。 一旦确定用作标题的字段，请考虑使用具有最少不同值的字段以生成列标题。 例如，如果查询计算按年龄和性别计算值，请考虑将性别用作列标题，而不是年龄，因为性别通常比年龄具有更少的可能值。

• 在 WHERE 子句中使用子查询    可以在交叉表查询中使用子查询作为 WHERE 子句的一部分。

返回页首