基于Access数据库的计算查询教学实例

霍莉

摘要

查询作为Access数据库六大对象之一，是Access数据库中最重要的应用查询可以对数据进行检索、处理，数据处理中经常会涉及到数据的计算。创建计算查询是整个查询对象的重难点之一，几种方式容易混淆，计算查询在Access全国计算机等级二级考试中也是必考知识点。本文对计算查询中涉及到的查询种类以及适用场合用具体的实例做了详细分析。

【关键词】Access数据库 查询设计 计算查询

1 引言

Access数据库对象有表、查询、窗体、报表、宏、模块。从数据库最典型的应用来说，查询是Access数据库中最重要的对象之一。它不仅可以能够快速从不同的数据表中检索中符合条件的记录，还能对检索结果进行分析和处理。数据处理包括对数据进行计算、统计，这类查询统称计算查询。

2 计算查询作用分析

在数据库操作中，数据的统计、计算与检索在日常工作中占很大的比例。近年来，计算查询在每年的全国计算机等级二级Access考试中成为了必考知识点。

Access查询分为选择查询、参数查询、交叉表查询、操作查询、SQL查询五种类型，有关计算的查询没有单独归类。其中参数查询是一种特殊的选择查询，只是在选择查询的条件行上用方括号处理。操作查询可以对数据表中的原始数据进行修改，包括生成一张新表、向现有表中追加数据、更新现有表中的数据以及删除现有表中的数据，这几种操作查询都没有涉及到计算查询。SQL查询是通过Select语句来创建各种查询包括计算查询在内，Select语句在本文中不做介绍。本文只介绍在查询设计视图中涉及的计算查询：选择查询和交叉表查询。

3 计算查询分类与实例分析

3.1 计算查询分类

计算查询分为两大类：一是利用表达式计算出新字段的查询;二是利用总计行进行分组计算的查询。下面就通过具体的教学实例来详细分析这两种计算查询。

本文所有实例均使用“学生管理系统”数据库来完成，该数据库中有六张数据表：“学生信息表”、“学生成绩表”、“课程表”、“班级表”、“教师表”、“授课表”，各表都已经按照相关字段建立了数据库表间关系。各表结构如下：

學生信息表（学号，姓名，性别，出生年月，政治面貌，生源地，班级编号，联系电话）;学生成绩表（学号，课程号，成绩）;课程表（课程编号，课程名称）;班级表（班级编号，班级名称，所在系）;教师表（教师号，姓名，性别，出生年月，政治面貌，参加工作时间，职称，工资，所在系，联系电话）;授课表（教师号，课程号，班级编号，学期）

3.2 表达式计算出新字段的查询

利用表达式构造新字段的计算查询，这类查询适用于要查询的内容在原始数据表中并不存在该字段，但是我们可以通过已经存在的相关字段加以计算而得出。具体实例如下：

实例一：创建计算学生年龄的查询，要求结果显示学号，姓名，年龄内容。

分析：所需数据表为“学生信息表”;所需字段为学号、姓名，年龄字段并不存在，但是通过对出生日期计算可以得到年龄;年龄的计算方法分析当前年份减去出生年份，相应表达式为：year（date（））-year（[出生年月]）

操作步骤：

（1）打开查询设计器;

（2）添加“学生信息表”;

（3）选择“学号”、“姓名”字段;

（4）在“姓名”字段的后一个字段列输入年龄：year（date（））-year（[出生年月]）;

（5）运行;

（6）保存。

注意：本实例的关键是年龄字段的构造，利用正确的表达式计算出年龄的值。如果表达式中出现了数据表中的字段名，则字段名一定要放在英文半角的方括号中。

在“学生管理系统”数据库中还有“教师表”，可以利用相似的表达式来计算教师的工龄。在“图书管理系统”的图书表中还可以利用图书入库量字段减去出库量字段得到图书库存量。

3.3 总计行进行分组计算的查询

总计行的分组计算查询分为两种：

（1）利用选择查询中的汇总按钮Σ计算;

（2）利用交叉表查询中的总计行计算。

两种总计行中的选项一样，常用主要有Group By、合计、平均值、最大值、最小值、计数六个选项。Group By是本计算查询中的分组项，可以是字段也可以是表达式。

下面通过具体实例来分析两种总计行分组计算查询的适用场合及用法。操作对象使用本文的“学生管理系统”数据库。

3.3.1 选择查询中的汇总

实例二：统计各班人数（要求结果中出现班级名称、总人数）。

分析：所需表“学生信息表”、“班级表”;所需字段“班级表”中的“班级名称”，“学生信息表”中的“学号”;分组项为“班级名称”字段，“学号”字段进行计数计算。

操作步骤：

（1）打开查询设计器;

（2）添加“学生信息表”、班级表;

（3）选择“班级名称”、“学号”字段;

（4）单击汇总按钮，在总计行设置“班级名称”为Group By，“学号”为计数，并且在“学号”的字段行更改“学号之计数”为“总人数”。

（5）运行;

（6）保存。

运行结果如图1。

实例三：统计不同职称的教师工资（要求结果中出现职称、平均工资）。

分析：所需表“教师表”;所需字段“职称”、“工资”;分组项为“职称”字段，“工资”字段进行平均值计算。

操作步骤：

（1）打开查询设计器;

（2）添加“教师表”;

（3）选择“工资”、“职称”字段;

（4）单击汇总按钮，在总计行设置“职称”为Group By，“工资”为平均值，并且在“工资”的字段行更改“工资之平均值”为“平均工资”。

（5）运行;

（6）保存;

注意：平均值在计算时经常会出现小数位数过多的情况，可以把“工资”字段列更改成表达式：ROUND（AVG（[工资]））来完成只保留整数部分，小数部分四舍五入。也可以把四舍五入ROUND（）函数换成INT（）直接取整。

实例四：统计所有课程的总分、平均分、最高分、最低分（要求结果中出现课程名称及四个统计结果）

分析：所需表“课程表”、“学生成绩表”;所需字段“课程名称”、“成绩”;分组项为“课程名称”字段，“成绩”字段进行四次计算分别为总计、平均值、最大值、最小值，在这里要特别注意“成绩”字段要选四次分别计算。

操作步骤：

（1）打开查询设计器;

（2）添加“课程表”、“学生成绩表”;

（3）选择“课程名称”、“成绩”字段;

（4）单击汇总按钮，在总计行设置“课程名称”为Group By，四个“成绩”字段分别为总计、平均值、最大值、最小值，并更改四個“成绩”的字段行标题“总分”、“平均分”、“最高分”、“最低分”。

（5）运行;

（6）保存。

运行结果如图2。

3.3.2 交叉表查询

交叉表查询是表中的记录按照不同的字段进行几次分组，分组字段分行标题和列标题两种，行标题位于数据表左侧，列标题位于数据表顶端，行列标题交叉点是分组计算的某个字段的计算结果。交叉表查询在很多教材中都是通过向导创建的，只用了很少的篇幅一带而过。交叉表查询向导只能创建数据源来自一个表或查询的字段，对涉及到多表的查询通常需要使用查询设计视图来创建。初学者理解起来比较困难，笔者通过几个实例详细分析交叉表查询的创建过程及注意事项。

交叉表查询在创建之前除了选定数据源表或查询之外，还要确定三项数据：一是行标题;二是列标题;三是行列交叉处的字段，以及该字段需要什么总计方式。在这里要说明的是：无论是行标题还是列标题都是分组项，一个交叉表查询最少要有一个行标题、一个列标题、一个值。最多行标题的数目可以是三个，列标题还是一个，值还是一个。交叉表查询最少要有两个分组字段，最多可以四个分组字段，值始终都只能是一个，列标题字段的字段名不出现，只出现字段值。例如选取“性别”字段作列标题，则结果顶端只显示“男”、“女”两个值作为列标题。

实例五：统计各班的男女生人数。（结果显示“班级名称”，“男”，“女”）

分析：本题中的数据源为“学生信息表”、“班级表”;所需分组字段为“学生信息表”的“性别”为列标题，“班级表”中的“班级名称”为行标题;所需统计字段为“学生信息表”的“学号”作计数计算。两张表是通过“班级编号”创建的一对多的关系。

操作步骤：

（1）打开查询设计器;

（2）添加“学生信息表”、“班级表”;

（3）选择“班级名称”、“性别”、“学号”三个字段;

（4）单击查询类型的“交叉表”按钮，在总计行设置“班级名称”为Group By，“性别”为Group By，“学号”为计数，并且在交叉表行设置“班级名称”为行标题，“性别”为列标题，“学号”为值。

（5）运行;

（6）保存。

运行结果如图3。

本实例也可以使用选择查询中汇总来完成。

运行结果如图4。

实例六：统计学生每门课程成绩，要求使用交叉表查询。

分析：所需数据源表“课程表”、“学生成绩表”、“学生信息表”;所需分组字段为“学生成绩表”中的“学号”作行标题，“学生信息表”中的“姓名”作行标题，“课程表”中的“课程名称”为列标题，“学生成绩表”中的“成绩”作值。这里“成绩”的总计行选“总计”、“平均值”、“最大值”、“最小值”都可以，因为每个学生每门课程只有一个值，它无论以上四种计算结果都一样。

操作步骤：

（1）打开查询设计器;

（2）添加“学生信息表”、“学生成绩表”、“课程表”;

（3）选择“学号”、“姓名”、“课程名称”、“成绩”四个字段;

（4）单击查询类型的“交叉表”按钮，在总计行设置“学号”为Group By，“姓名”为Group By，“课程名称”为Group By，“成绩”为“总计”、“平均值”、“最大值”、“最小值”四个中的任意一个，并且在交叉表行设置“学号”为行标题，“姓名”为行标题，“课程名称”为列标题，“成绩”为值。

（5）运行;

（6）保存。

4 总结

基于Access的计算查询第一种构造新字段查询，关键在于如何利用已经存在的字段，书写正确的能计算出符合要求表达式;第二种分组统计查询是使用交叉表查询还是选择查询中的汇总，要根据实际情况来确定。

综上所述，如表1结论。

在实际创建统计查询时，有时以上两种方式都可以完成例如本文的实例五，但统计结果在清晰度上有一定的差别，需要查询设计者自己体会，选择最合适的查询方式。希望本文能够对于设计者创建计算查询有所帮助。

参考文献

[1]李雁翎.Access 2003数据库技术及应用[M].高等教育出版社，2010.

[2]张成叔.Access 2010数据库技术及应用（第四版）[M].中国铁道出版社，2013.

[3]屈武江.耿青松.Access数据库技术与应用项目人教程[M].大连理工出版社，2015.