Ms Access 简明教程

MS Access - Unmatched Query Wizard

在 Access 中，还有另一个非常有用的向导，即 Find Unmatched Query Wizard 。查找不匹配查询向导创建一个查询，该查询查找表中没有另一个表中相关记录的记录或行。

In Access, there is another very useful wizard and that is Find Unmatched Query Wizard. The Find Unmatched Query Wizard creates a query that finds records or rows in one table that have no related records in another table.

正如我们先前讨论的那样，数据如何在查询中连接在一起，以及大多数查询如何查找两个或更多表之间的匹配项。

As we have already discussed how data joins together in queries, and how most queries are looking for the matches between two or more tables.

matched

  1. This is the default join in Access, for example, if we design a query with two tables, tblCustomers and tblOrders, and join those two tables by the CustomerIDs, this query will return only the results that match. In other words, the customers who have placed orders.

  2. There are times when we don’t want to see the matches, for instance, we may not want to see any customer in our database — the customers who have not placed orders as yet.

not placed orders

  1. This is exactly what the find unmatched query does.

此类查询还有许多其他可能的用途。

There are many other possible uses for this kind of query as well.

在我们的数据库中，我们可以使用它来查看哪些作者尚未编写项目，或者您可以使用它来查看哪些员工尚未选择任何健康福利。现在让我们打开包含 Customers 和 Orders 表的数据库；转到“创建”选项卡并单击查询向导按钮。

In our database, we can use it to see which authors have not yet written a project or you could use it to see which employees have not yet elected any health benefits. Let us now open your database which contains Customers and Orders table; go to the Create tab and click on the query wizard button.

选择 Find Unmatched Query Wizard 并单击 Ok 。

Select the Find Unmatched Query Wizard and click Ok.

unmatched

在此场景中，我们将找出尚未下订单的客户。在第一个屏幕中，它询问包含查询结果记录的表或查询。

In this scenario, we will look out for those customers who have not placed an order. In the first screen it’s asking which table or query contains the records you want in the query results.

我们现在想要 tblCustomers 中的客户列表。选择该选项并单击 Next 。

We now want a list of customers from tblCustomers. Select that option and click Next.

customers table

在以下屏幕中，您需要指定 which table or query contains the related records 。换句话说，您使用哪个表与第一个表进行比较。为此，我们需要找到尚未下订单的那些项目。我们需要选择包含所有订单信息的表 tblOrders 。现在，单击 Next 。

In the following screen, you need to specify which table or query contains the related records. In other words, what table are you using to compare with the first one. For this, we need to find the ones that have not placed orders. We need to select the table that contains information on all orders — tblOrders. Now, click Next.

tblorders

在以下屏幕中，您需要指定两张表中包含哪些信息。

In the following screen, you need to specify which piece of information is in both tables.

  1. This will typically be some kind of primary key, foreign key, field, or relationship.

  2. If you have an existing relationship in your database, Access will select and match those fields for you.

  3. But, if you have other fields that you can join together, contain similar information, you can choose that here as well.

此处，我们在“tblCustomers”字段和“tblOrders”字段中默认选择了 CustID。现在，单击 Next 。

Here, we have CustID selected by default in both Fields in ‘tblCustomers’ and Fields in ‘tblOrders’. Now, click Next.

custid

在以下屏幕中，您可以选择想要在查询结果中显示的字段。

In the following screen, you can pick and choose the fields you want to see displayed in the query results.

selected files

现在让我们选择所有可用字段并单击双箭头。这将把所有 available fields 移动到 selected fields 区域。现在，单击 Next 。

Let us now select all the available fields and click on the double-headed arrow. This moves all the available fields over to the selected fields area. Now, click Next.

first order

最后一个屏幕将允许您为查询选择一个名称并单击 Finish 。

The last screen will allow you to choose a name for your query and click Finish.

without matching

这里我们列出了尚未向我们下订单的某个客户。

Here we have one customer listed as that customer who has not placed an order with us yet.

customer listed

您还可以了解如何创建该查询。为此，您需要返回到 Design View 。

You can also see how that query was created. For this, you need to go back to the Design View.

is null

此向导在 tblCustomer 和 tblOrders 之间创建了 Outer Join ，并且 Is Null 条件已添加到 tblORders 中的 CustID。这样做是为了排除某些记录。在本例中，这些记录是已下达订单或在 tblOrders 中有相关信息的客户。

This wizard has created an Outer Join between tblCustomer and tblOrders and the Is Null criteria is added to the CustID from tblORders. This is to exclude certain records. In this case, it is the customers who have placed orders, or who have related information in tblOrders.