第10章Access创建多表查询和交叉表查询Word文件下载.docx
《第10章Access创建多表查询和交叉表查询Word文件下载.docx》由会员分享，可在线阅读，更多相关《第10章Access创建多表查询和交叉表查询Word文件下载.docx（49页珍藏版）》请在冰豆网上搜索。
择、合计、参数和交叉表查询。
操作类的四种查询类型可以用来创建或者修改表中的数据：
创
建、追加、删除和更新。
第11章“用操作查询修改数据”中提供了每种操作查询类型的典型应
用和它们各自的一些例子。
如下的Access2000新特征适用于本章的主题内容：
•新的查询属性子数据表名称、链接子字段、链接主字段、子数据表高度和子数据表扩
展可以在查询结果集中容纳子数据表。
•现在已经可以打印“关系”窗口的内容当“关系”窗口拥有焦点时，选择“文件”，
“打印关系”，从“关系”窗口的内容创建一个报表，然后在“打印预览”模式显示该报
表，之后便可以打印出该报表。
•将关系图表以“报表快照”格式作为电子邮件的一个附件进行发送接收者必须安装有
第10章创建多表查询和交叉表查询215
Access2000或者“报表快照”阅读器。
10.2连接表以创建多表查询
在创建表之间的连接之前，必须知道哪些或哪个字段具有相同的值，可以建立相关关系。
正如在第4章中曾提到的，在包含相关数据的不同表中，为主键和外部键指定相同的名称是一
个常见的设计习惯。
Microsoft在创建Northwind示例数据库时也使用了这种方法，使得在确定
表之间的关系和创建表之间的连接时显得更为容易。
例如，Customers表的CustomerID字段和
Orders表的CustomerID字段用于将订单和顾客建立连接。
图10-1给出了Northwind数据库的结构，用图形方式显示了表之间的连接。
Access查询设计
表示连接的方法为在不同表的字段名之间连以线段。
粗体指示的是主键字段。
通常每个连接至
少涉及到一个主键字段。
图10-1Northwind示例数据库中表之间的连接
显示Access2000Northwind数据库表之间的连接结构时，首先使“数据库”窗口具有焦点
（按下F11），然后单击工具栏上的“关系”按钮或者选择“工具”、“关系”。
图10-1中显示两表
之间连接的线段上的1指示的是一对多关系中“一”的一方;
无穷大符号（∝）指示“多”的一方。
在Access2000中，可以只显示某个表的直接关系（使用工具栏上的“显示直接关系”按钮）
或者数据库所有表之间的关系（“显示所有关系”按钮）。
在默认情况下Northwind.mdb的所有
表，当打开Northwind示例数据库的“关系”窗口时将全部显示出来。
这时，单击“显示直接
关系”按钮将不起作用。
提示只显示某个表的关系时，可单击工具栏的“清除布局”按钮，单击“显示表”按钮，显示
“显示表”对话框，在“表”列表中选择要显示的表，然后单击“添加”并“关闭”该对话框。
单击“显示直接关系”按钮，显示所选表的关系。
清除“关系”窗口的布局不会对后台表之间
的关系产生任何影响。
“显示直接关系”的特征主要用于包含许多相关表的数据库。
Access在“查询设计”窗口中支持四种类型的连接：
•等值连接（也叫做内连接）是创建选择查询时最为常见的连接。
等值连接显示某表中的全部
记录与另一个表中相应记录。
记录之间的对应关系是由连接表的字段中相同的值决定的
（SQL语句：
WHEREfield1=field2）。
在大多数情况下，连接都是基于某个表中唯一的主
键字段和一对多关系中其他表的外部键字段来完成的。
如果担当关系中多方的表中没有
记录的字段值与一方表中的记录相对应，则一方中相应的记录将不会出现在查询结果中。
如果两个表之间共同的字段名称所对应的字段为其中某个表的主键，Access将自动地创建
表之间的连接。
如果在前面曾在关系窗口中指定过表之间的关系，这些连接也会自动地创建。
216第二部分最大限度地利用查询
•外连接可以用于数据库维护，通过创建一个包含具有唯一值记录的新表，从表中删除
孤立记录和复制数据。
外连接不管在连接的其他方是否存在与之对应的记录，将显示连
接中某成员表中的全部记录。
•自连接在单个表之内结合数据。
在Access中创建自连接时，需要向查询添加一个表的
复制件（Access将为复制件提供一个别名），然后与复制的记录之间创建连接。
•Theta连接在结合数据时使用的是比较操作符而不是=。
Theta连接包含用于返回不具备
某个特定关系的记录的查询所使用的不等号（<
>
）。
实现theta连接用的是WHERE准则而
不是SQLJOIN保留字。
“查询设计”窗口并不用字段名之间划线的形式指示theta连接，
theta连接不会显示在“关系”窗口。
本书所带光盘\Beckwith文件夹下的Beckwith.mdb数据库具有环状的关系集合。
从CD-ROM
或者从硬盘上的复制件打开Beckwith.mdb，然后单击“关系”按钮，打开“关系”窗口（见图
10-2）。
Sections和Courses之间为多对一的关系，Departments和Courses之间为一对多关系，
Employees和Departments之间为多对一关系，Employees和Sections之间为一对多关系。
你还可以
看到Courses、Enrollments、Students、Grades和Courses之间也存在一个环形的关系集。
当你想
测试具有大量记录的查询性能时，Beckwith.mdb十分有用。
这个虚构的BeckwithCollege位于
Navasota、Texas，拥有30000名学生，2320名雇员，在14个系的590门课程中提供了1770班级。
图10-2Beckwith示例数据库中表之间的关系
注意Beckwith数据库的设计上存在小小的缺陷。
一个教授同时属于多个系的情况是极为可能的，
但是在图10-2所示的结构图中却不允许这种情况发生。
允许教授隶属于多个系时，需要建立多对
多关系和Employee-Departments关系表。
10.2.1创建传统的单列等值连接
基于每个表中的一列所建立的连接称为单列连接。
下面详细给出了创建一个数据库、使所
有查询都使用简单的单列连接时需要满足的基本规则：
•关系中一方的每个表必须具有一个主键，并具有无重复索引来维护参照完整性。
Access
将自动地在一个表的主键字段上创建无重复索引。
•多对多关系，例如Orders到Products的关系，是靠一个中间表来实现的（在这里为Order
Details），该中间表和其中某个表之间存在一对多关系（Orders到OrderDetails），而和另一
个表之间存在多对一关系（OrderDetails到Products）。
第10章创建多表查询和交叉表查询217
•表中存在的重复数据（假设允许重复）被提取到一个具有主键、无重复、与提取重复数
据的表具有一对多关系的新表中。
使用多列主键来标识提取的数据常常是必须的，因为
单个关键字字段可能会包含重复的数据。
但是，关键字字段值的结合（也称为串联）必须
是唯一的。
Access2000的“表分析器向导”可以自行定位和提取大部分重复数据。
我们
在第11章将描述如何手工地从表中提取重复数据。
如果需要使用生成表查询的帮助，请参见11.2节“用生成表查询创建新表”。
如果你对如何创建关系还不熟悉，请参见4.10.1节“建立表之间的关系”。
Northwind数据库中的所有连接，在图10-1中用相邻表字段名之间的连线标识，都是具有
一对多关系的表之间的单列等值连接。
Access使用ANSISQL-92保留字INNERJOIN来标识传
统的等值连接，用LEFTJOIN或者RIGHTJOIN指定外连接。
如果想学习有关SQL连接的更多术语，请参见23.4.4节“用SQL创建连接”。
在基于等值连接的查询中最为常见的用法便是用顾客名字和地址接收到的订单匹配。
例如，
你或许希望创建一个简单的报表，列出顾客名字、订单数目、订单日期和数量。
遵循如下步骤，
创建一个传统的一对多，单列等值连接查询，将Northwind的顾客和他们的订单关联起来，并
按照公司和订单的日期进行排序：
1）如果Northwind.mdb是打开的，关闭除了“数据库”窗口之外的所有窗口；
否则打开
Northwind.mdb。
2）单击“数据库”窗口中的“查询”快捷方式，然后双击在“设计”视图中“创建查询”
快捷方式。
Access在空的“查询设计”窗口上显示“显示表”对话框。
3）从显示表列表中选择Customers表，单击“添加”按钮。
或者，也可以双击Customers表
名字，将该表添加到查询中。
Access将在“查询设计”窗口中添加Customers表的“字段名字”
列表。
4）双击“显示表”列表中的Orders表，然后单击“关闭”按钮。
Access将Orders表的“字
段名称”列表添加该窗口中，并在两个表的CustomerID字段之间添加了一条连线，指示两者之
间存在的连接。
Access是自动创建该连接的，因为CustomerID是Customers表的主键字段，而
Access在Orders表中发现了具有相同名字的字段（外部键）。
5）为了用顾客的名字标识每个订单，可选择Customers表CompanyName字段，并将字段符
号拖到“查询设计”网格的第一列的“字段”行。
6）选择Orders表的OrderID字段，将字段符号拖到第二列的“字段”行。
将OrderDate字段
拖到第三列。
查询设计现在的状态如图10-3所示。
图10-3该查询显示按公司名字和日期排序的顾客签订的订单
7）单击“运行”或者“查询视图”按钮，显示查询结果，Recordset如图10-4所示。
注意查
询结果集的字段标题显示了表字段的标题（其中包含有空格），而不是实际的字段名，实际的
字段名中是没有包含空格的。
图10-4连接Customers和Orders表的图10-3所示的查询设计所产生的结果
10.2.2为查询结果集指定排序次序
Access显示查询结果集时是按照主键
字段上的索引的次序进行显示的。
如果代
表主键字段的列不止一个，Access将按照
关键字列出现的次序从左到右排序简单的
查询结果集。
因为CompanyName是最左
边的主键字段，查询结果集将为单个公司
按订单编号顺序显示所有订单。
你可以改
写主键显示的次序，只要向查询添加一个
排序次序即可。
例如，如果你想首先看到
最近的订单，可以指定按订单日期的降序
排序。
遵循如下步骤，向查询添加这个排
序次序：
关于主键索引的更多信息，请参见
4.10.4节“往表中添加索引”。
1）单击“设计视图”按钮，返回“查
询设计”模式。
2）将插入符放到“查询设计”网格
OrderDate列的“排序”行，按下F4，打
开下拉列表。
3）从下拉列表中选择“降序”，指定
在日期上按降序排序—最近的订单在先
（见图10-5）。
4）单击“运行”按钮或者“数据表视
图”按钮，显示新排序次序下的查询结果集（见图10-6）。
218第二部分最大限度地利用查询
图10-5向查询添加一个特定的排序次序
图10-6向查询添加OrderDate降序排序之后的结果
第10章创建多表查询和交叉表查询219
10.2.3使用表之间的间接关系创建查询
可以创建查询返回不直接相关的记录，例如每个顾客所购买的产品的类别。
必须将作为连
接链中一个链接的每个表包含在查询中。
例如，如果创建查询是为了显示每个顾客所购买产品
的类别，则必须将Customers和Categories表之间存在的连接链上的每一个作为中间链接的表都
包含到查询中。
该链中包含Customers、Orders、OrderDetails、Products和Categories表。
但是，
不需要从中间表向查询设计网格添加任何字段；
CompanyName和CategoryName字段就足够了。
遵循如下步骤，修改顾客和订单查询，创建一个查询，显示间接相关记录中的的字段：
1）在“查询设计”视图中，删除查询的OrderID列，单击字段行上方的细条，将整个列选
定（突出显示），然后按下Delete键。
在OrderDate列上也执行相同的过程，只留下
CompanyName列显示在查询中。
2）单击工具栏的“显示表”按钮或者选择“查询”，“显示表”，按顺序将OrderDetails、
Products和Categories表添加到查询中；
然后单击“添加表”对话框的“关闭”按钮。
图10-7上
面的窗格显示出Access自动地在Customers和Categories表之间基于每个中间表的主键字段和相
邻表中同名的外部键字段创建出来的连接链。
提示在添加表到“查询设计”窗口中时，该表的字段列表可能不随之出现在上面的窗格中。
可
以使用上面窗格中的垂直的滚动条来显示“隐藏起来”的表。
可以将“表字段”列表拖放到上
面窗格的上方，然后重新排列字段列表，使之与图10-7所显示的式样相匹配。
3）从Categories字段列表将CategoryName拖到网格第二列的“字段”行。
或者，通过双击
字段名字也可以将之加到网格中的下一个空列上（见图10-7）。
图10-7从没有直接关系的表创建查询所必须的连接链
4）单击工具栏上的“运行”按钮。
查询结果集如图
10-8所示。
5）单击窗口的“关闭”框，关闭查询。
该查询只是一
个例子，所以不需要保存它。
提示图10-8中的查询结果集有2100多行，大多数都是重复的。
为了删除重复，可以右击“查询设计”视图上面窗格中的空
白区域，并选择“属性”，打开“查询属性”页。
双击“唯一
值”文本框，将唯一值设为“是”。
再次运行查询验证一下重
复是否不见了。
本章后面的“创建多列等值连接和选择唯一
值”一节将给出一些其他使用“唯一值”查询属性的例子。
图10-8图10-7中的查询所产生的
Customers-Categories记录集
在不直接相关的表上进行查询的情况极为常见，尤其当你想用SQL总计函数或Access的交
叉表查询进行数据分析的时候更是如此。
更多的信息，可以参见本章后面“使用SQL总计函数”
和“创建交叉表查询”两节中的内容。
10.2.4创建多列等值连接和选择唯一值
在两个表之间可以具有多个连接。
例如，你可能希望能创建一个查询，返回票据和运输地
址相同的顾客名字。
票据地址是Customers表的Address字段，而运输地址为Orders表的
ShipAddress字段。
因此，需要匹配两个表中的CustomerID字段以及Customers.Address和
Orders.ShipAddress。
这个任务需要多列等值连接才能完成。
遵循如下步骤，创建这个地址匹配多列等值连接的例子：
1）在“设计”视图中打开一个新的查询。
2）向查询添加Customers和Orders表，关闭“添加表”对话框。
3）单击并将Customers表“字段列表”框中的Address字段拖到Orders表的“字段列表”框
的ShipAddress字段。
这将创建另一个连接，由Address和ShipAddress之间新的连线指示（参见图
10-9中上面的窗格）。
Address和ShipAddress之间新的连线在两端都有园点，指示出这时在一对
没有特定关系的字段之间建立的连接，它们不具有相同的字段名字，或者主键索引。
4）将Customers表的CompanyName和Address字段分别拖到查询的第一和第二列的字段行，
然后丢放该字段。
将Orders表的ShipAddress字段拖到查询的第三列并将该字段丢放在该列的字
段行（参见图10-9中下面的窗格）。
图10-9将一个表中的某个字段名称拖到另一个表中某个字段上，创建一个多列等值连接
5）单击工具栏上的“运行”按钮。
图10-10所示为查询产生的结果。
6）要消除重复的行，必须使用“查询属性”窗口的“唯一值”选项。
为了显示“查询属性”
窗口，如图10-11，需要单击“设计视图”按钮，然后单击工具栏的“属性”按钮或者双击
“查询设计”窗口的上面窗格中的空白区域。
如果“属性”窗口的工具栏显示“字段属性”或
者“字段列表”，单击“查询设计”窗口上面窗格中的空白区域，让标题栏显示“查询属性”。
或者，右击上面窗格中的空白区域，从弹出菜单中选择“属性”。
7）在默认情况下，“唯一记录”的属性和“唯一值”属性的设置都为“否”。
将插入符放到
“唯一值”文本框并按下F4，打开下拉列表。
选择“是”并关闭该列表。
设置“唯一值”属性
220第二部分最大限度地利用查询
手工添加连接
为“是”将导致在查询上添加ANSISQL保留字DISTINCT。
再次单击“属性”按钮，关闭
“属性”窗口。
关于删除重复记录的更多的信息，请参见23.6.9节“Jet的DISTINCTROW和SQL的
DISTINCT关键字”。
提示或者，也可以双击“属性”窗口中其文本框来改变“唯一记录”和“唯一值”属性的设置。
所有具有“是/否”值的属性都可以靠双击来切换它的值。
8）单击工具栏上的“运行”按钮。
结果集中不再包含重复的行，如图10-12所示。
图10-12删除了重复行的查询结果集
9）单击“关闭窗口”按钮，关闭查询，不进行保存。
这样便可以避免让过时的查询例子搞
乱“数据库”窗口中的“查询”列表。
因为大部分订单具有相同的票据和运输地址，所以更为有用的查询是找出顾客的票据和运
输地址不同的订单。
但是，使用多列等值连接是无法创建这个查询的，因为AccessSQL的
INNERJOIN保留字不能接受<
>
操作符。
所以需要使用准则添加一个不相等连接，而不是使
用一个多列连接，具体见本章后面“用准则创建不相等theta连接”。
如果在你运行前面的查询时，遇到“输入参数”对话框，请参见本章后面“疑难解答”部
分中的“缺少查询的对象”。
第10章创建多表查询和交叉表查询221
图10-10订单查询结果集，返回票据地址和
运输地址相同的顾客订单
图10-11使用“查询属性”窗口来
只显示具有唯一值的行
222第二部分最大限度地利用查询
10.3使用表中的查找字段
Access2000的表字段查阅功能允许你替代传统的字段文本框所使用的下拉列表框或者列表
框。
查阅功能是一个由Access自动地创建的一对多查询。
查阅功能允许你为某个特定的字段提
供一个可接受值的列表。
当你从该列表中选择值的时候，该值将自动地输入到当前记录的字段
中。
可以指定的查阅字段有以下两种类型：
•在一个包含外部键值的字段，来自一个相关基本表的一个或者多个字段组成的值列表的
字段。
这种类型的查找字段目的是添加或者更改外部键值，确保外部键值和一个主键值
相匹配，从而保持了相关的完整性。
在定义一个包含外部键的字段时，两表之间必须在
关系窗口中存在一个关系。
例如，Northwind.mdb的Orders表具有两个外部键字段：
CustomerID和EmployeeID。
C
升级会员 