第16章　Access查询应用案例

通过案例来学习是最好的学习方式之一，下面结合一个具体案例来进一步学习Access中的各种功能。假设我们是一家儿童自行车工厂的生产计划人员，现在需要根据：①客户订单数量；②各种型号童车的零部件组成情况（即所谓的“物料清单”），计算儿童自行车零部件的总需求量，以便向供应商采购，确保满足生产所需。

16.1　产品物料清单分解

由于产品是由一个个的零件组成的，企业从客户那里接受客户订单后，计划人员会根据产成品的订单计算零部件的需求数量，然后根据交货期和提前期的要求安排采购部门向零部件供应商采购，以备生产所需。在这个业务过程中Access可以发挥巨大作用。

举一个具体的例子，假设某个工厂生产童车，有三个型号：A01，A02，A03，每种型号的童车的客户订单数量如图16-1所示。

图16-1

童车是由不同的组件组装而成的，每个型号童车的组件构成情况如图16-2所示（这个表格就是所谓的产品物料清单，或者称为BOM：Bill of Material）。

图16-2

对于童车A01来说，它由3个车轮，1个车架，2个脚踏板和1个车座组成；对于童车A02来说，组件构成情况同上，但是多了1个车筐。

值得注意的是，在客户订单中有型号为A03的童车订单，但是在如图16-2所示的物料清单中没有童车A03的物料清单，也许工厂的管理人员还没有来得及为童车A03建立相应的物料清单，我们设计的Access查询功能必须能够及时发现这个问题以便提前应对。

现在的任务是，根据以上两个表格提供的信息，即“订单”表上的产品需求数量信息和“物料清单”表上的产品组件构成信息，推算出完成全部客户订单所需的每种童车组件的总数量，以便向供应商采购这些童车组件。

16.2　链接外部数据

新建一个空白Access数据库文件。选择“外部数据→导入并链接→Excel”命令。在弹出的“获取外部数据—Excel电子表格”对话框中，单击“浏览”按钮，选择保存有“客户订单”和“物料清单”数据的Excel文件位置。

在“指定数据在当前数据库中的存储方式和存数位置。”下方，选择第二个单选框：“通过创建链接表来链接到数据源。”这表示我们将通过“链接”方式将Excel中的数据动态地与Access建立联系，这样，当我们修改Excel中的数据时，Access中的数据也会相应地发生改变，如图16-3所示。设置完毕后单击“确定”按钮，弹出“链接数据表向导”对话框。

图16-3

在弹出的“链接数据表向导”对话框中，接受该对话框上部的默认单选框：“显示工作表”。此时会在该单选框右边列出了Excel文件中的所有工作表。在这里，我们需要导入“订单”和“物料清单”两个表格。先选择“订单”工作表，然后单击“下一步”按钮，如图16-4所示。

图16-4

此时弹出“链接数据表向导”对话框，该对话框中显示我们上一步中选择的“订单”数据表中的数据预览。由于我们的数据含有列标题，因此，勾选“第一行包含列标题”复选框，然后单击“下一步”按钮，如图16-5所示。

图16-5

在弹出的“链接数据表向导”下一个对话框中的“链接表名称”文本框处，保持默认的名称“订单”，然后单击“完成”按钮，如图16-6所示。

图16-6

这时，弹出一个对话框，表示我们已经成功地将数据链接到Access，单击“确定”按钮，完成数据的链接。

此时我们会发现在Access窗口的左边“所有Access对象”下面出现了一个名为“订单”的Excel小图标，这就是我们刚才链接到Access中的Excel数据表，如图16-7所示。

图16-7

重复上述链接外部数据的操作，导入另外一个Excel工作表“物料清单”。外部数据链接完成后的Access界面如图16-8所示，在Access界面左侧的Access对象列表中，我们看到了链接到Access中的两个Excel数据表格对象：“订单”和“物料清单”。

图16-8

16.3　设计查询

将所需的外部数据链接到Access后，下面的任务就是在Access中设计查询进行数据的分析和处理。

选择“创建→查询→查询设计”命令，在弹出的“显示表”对话框中，分别选中两个表格名称，单击“添加”按钮，将两个表格添加到Access查询设计器界面中，添加完成后单击“关闭”按钮，关闭“显示表”对话框。两个表格添加完成后，Access查询设计器界面如图16-9所示。

图16-9

下面我们建立两个表格之间的联接，选中左表“订单”表格中的“订购产品型号”字段，将其拖曳到右表的“产品型号”字段上面，这时，两个表格对应字段之间出现了一条联接线，请注意，该联接线两端的节点的形状是一样的，如图16-10所示。

图16-10

我们在第15.1节中已经了解到：联接线两端的节点形状代表了两个表格之间的不同联接操作。当两表之间联接线两端的节点形状相同时，表示在查询结果中“只包含两个表中联接字段相等的行”，这并不是我们所需要的；针对这个问题，我们需要的是，当左表中的某行订单所对应的产品型号即使在右表中没有相应的物料清单对应，我们也要把该行订单包括在查询结果中，以便及时发现问题。因此，我们需要修改两个表格之间的联接线，具体修改方法如下。

双击两表之间的联接线，弹出“联接属性”对话框，该对话框上半部分表示两个表格之间的联接字段，下部的三个选项表示两表之间可以实现的三种联接关系。当我们以拖曳的方式设置左右两个表格之间的联接线时，“联接属性”对话框的自动生成设置是第1个复选框，前面我们已经解释过，第1个复选框不符合我们的要求。根据需求，选择第2个“包括‘订单’中的所有记录和‘物料清单’中联接字段相等的那些记录。”然后单击“确定”按钮关闭对话框，如图16-11所示。

图16-11

这时，我们发现，联接线两端的节点形状发生了变化，联接线右侧的节点变成了箭头，如图16-12所示。这反映了我们刚才在“联接属性”对话框中的设置情况（对应着“联接属性”对话框中的第2个复选框。

图16-12

下面把两个表格中的所有字段全部拖曳到查询网格“字段”行处，如图16-13所示。单击“运行”按钮（带有红色惊叹号的按钮），或者选择“设计→视图→数据表视图”命令，查看查询运行结果。

图16-13

查询执行结果如图16-14所示，我们发现，借助Access查询，“物料清单”表和“订单”表已经被正确地分解为零部件表格，其中我们还发现了一个问题，就是300辆“童车A03”的订单还没有相应的物料清单，我们需要联系生产技术人员协助解决，以避免造成无法投产，从而耽误客户订单准时交货。最后，单击快捷工具栏上的“保存”按钮，接受默认名称，保存查询。

图16-14

16.4　增加自定义字段

到现在为止，借助Access查询和“物料清单”表，我们已经成功地将“订单”表分解为产品组件表，如图16-15所示。但是，任务还没有完成，我们需要根据订单数量和据此展开的物料清单数量计算出零部件的需求量。

图16-15

在Access界面左侧的Access对象列表中，选中上面设计的“查询1”，选择“开始→视图→设计视图”命令，重新回到“查询1”的设计视图。

我们在“查询1”的查询设计网格最后一列增加一个自定义字段“组件需求数量：[订单数量][组件数量]”。自定义字段的计算方法“[订单数量][组件数量]”和自定义字段名称“组件需求数量”之间用冒号分开。我们在引用字段名称时需要用方括号（[　]）括起来，如图16-16所示。

图16-16

选择“设计→运行”命令或者选择“开始→视图→数据表视图”命令，查看查询的运行结果。我们发现查询运行结果中多了一个自定义字段“组件需求量”，其数值等于订单数量乘以对应的组件数量，如图16-17所示。

图16-17

16.5　分组汇总查询

由于各种型号的童车的组件中很多是相同的，因此，我们需要按组件汇总一下才更有意义。选择“开始→视图→设计视图”命令，再次回到“查询1”的设计视图，如图16-18所示。

图16-18

把光标至于查询网格中，删除除“产品组件”字段和“组件需求数量”自定义字段之外的所有字段，单击鼠标右键，在弹出的快捷菜单中选择“汇总”命令。

这时，我们发现查询设计网格中多了一行标签为“汇总”的行，该行与对应的字段交叉处为“Group By”，“Group By”表示以对应的字段分组操作，如图16-19所示。

图16-19

我们以产品组件字段分组，对自定义的“组件需求量”字段求和。单击“组件需求量”自定义字段下方的“Group By”，在展开的下拉选项中选择“合计”选项，如图16-20所示。

图16-20

选择“设计→运行”命令或者选择“开始→视图→数据表视图”命令，查看查询的运行结果。查询运行结果如图16-21所示。

图16-21

至此，我们已经把订单中的各种型号童车数量转化成了所需组件的数量。现在，我们可以拿着这份童车组件汇总报告，向组件供货商定货了。而且更为方便的是，由于我们已经用Access设计好了数据的分析和处理的逻辑，并且把该逻辑以查询的方式固化了下来，这样，等接到新的订单的时候，我们只需在Excel中替换掉以前的订单数据，然后双击查询，即可得到更新了的结果！