Ms Access 简明教程

MS Access - Duplicates Query Wizard

在本教程中，我们创建了各种选择查询，主要来自设计视图。但是在 Access 中，我们可以使用一些特殊查询向导来创建一些特定的选择查询。第一个是查找重复查询向导。查找重复查询向导将创建一个查询，该查询可在单个表或查询中找到具有重复字段值的记录。

In this tutorial, we have created a variety of select queries, mainly from the Design View. But in Access, we can make use of some special query wizards to create a couple of specific select queries. The first one is Find Duplicates Query wizard. The Find Duplicates Query Wizard will create a query that locates records with duplicate field values in a single table or query.

正如我们所讨论的，关系数据库旨在避免存储重复的信息。但尽管有这样的设计，用户有时会无意输入重复的信息。

As we have discussed that relational databases are designed to avoid storing duplicate information. But despite that design, sometimes users accidentally enter duplicate information.

Example

在客户表中，你可以让同一客户无意被添加两次。在这种情况下，客户将具有相同的地址，但客户 ID 不同，这会给报告造成问题。在此情况下，你可以使用 duplicates query wizard to quickly locate possible duplicate entries 。

In a customer’s table, you can have the same customer accidentally added twice. In such cases, the customer will have the same address, but different customer IDs, which can create problems with reporting. In this situation, you can make use of the duplicates query wizard to quickly locate possible duplicate entries.

现在让我们打开包含 tblAuthers 表的 Access 数据库并在 Create 选项卡中，在查询组中，选择 query wizard 。

Let us now open our Access database which contains tblAuthers table and go the Create tab and, in the queries group, select query wizard.

如果你看到以下屏幕截图，则有四种不同的向导可供选择。选择 find duplicates query wizard 并单击 Ok 。

If you see the following screenshot, you have four different wizards to choose from. Select the find duplicates query wizard and click Ok.

查找重复查询向导的第一个屏幕会询问要搜索可能的重复项的哪个表或哪个查询。假设我们想要检查作者表以确保同一作者未无意输入两次。因此，选择 tblAuthors 并单击 Next 。

The very first screen of the find duplicates query wizard will ask what table or what query you want to search for possible duplicates. Let us say we want to check our author’s table to make sure that the same author hasn’t accidentally been entered twice. So, select tblAuthors and click Next.

The second screen in the wizard will ask what fields might contain duplicate information.
Typically, you will not be using your primary key field, because, again, when you designate a field in Access as a primary key, Access will not allow duplicates to be entered.
We will look at any other field or a combination of fields.

我们可以按姓或名和姓搜索，也可以按他们的街道地址搜索，也可以更具体地按他们的电话号码或生日搜索。

We can search by the last name or the first name and the last name, or you can search by their street address, or to be more specific with their telephone number or birthday.

我们现在按名、姓和生日搜索，然后点击 Next 。

Let us now search by the first name, the last name, and birthday and click Next.

该向导的以下屏幕将要求输入我们希望在查询中显示的字段。为此，点击双箭头，所有字段都将移至其他查询字段区域，并将会添加到我们的查询结果中。

The following screen in this wizard will ask for the fields we want to be displayed in our query. For this, hit the double arrow, all of the fields will move over to the additional query fields area, and will be added to our query results.

我们现在点击 Next 。它将带我们进入该查询向导的最后一个屏幕。

Let us now click Next. It will take us to the last screen in this query wizard.

在以下屏幕中，输入你希望如何命名查询。

In the following screen, enter how do you want to name your query.

默认情况下，它将为其命名 find duplicates 对 whatever the name of the object that you’re querying 。在这种情况下， tblAuthors ，但你也可以为其提供任意其他名称，然后点击 finish 。

By default, it’s going to name it find duplicates for plus whatever the name of the object that you’re querying. In this case, tblAuthors, but you can give it any other name too and click finish.

此处，Access 找到了一个可能的重复项，作者 Jose Caline，他有相同的生日、地址、电话号码但有不同的 AuthorID。

Here, Access has found a possible duplicate, and that’s going to be author Jose Caline which has same birthday, same address, same telephone number but different AuthorIDs.

这一定是无意间重复输入了两次。我们现在已经将所有字段添加到查询中，我们能直接删除记录。我们还必须确保在其他表中没有与此相关的记录。

This one has definitely been entered twice by accident. We have now added all of the fields to our query, we could just go and delete the record. We also have to make sure that we don’t have any related records in another table.

选择任何记录，然后在以下对话框中选择“删除”。

Select any record and choose Delete as in the following dialog box.

Access 会提示你“您即将删除一条记录。”如果你要继续，请点击“是”。

Access gives you a prompt, “You are about to delete one record.” Click Yes if you want to continue.

如果你想查看该向导创建该查询的方式，请进入设计视图，查看该向导向该查询添加了哪些内容。

If you’d like to see how that wizard has created this query, go into the Design View and see what all has been added to this query.

正如你在以上屏幕截图中所看到的，我们的字段及一些特定的条件在名字段的下方。

As you can see in the above screenshot, we have our fields and some specific criteria underneath the first name field.

这是该向导搜索重复信息的方式。这是迄今为止查找重复项最简单的方法。

This is how this wizard is looking for that duplicate information. It is by far the easiest method to find duplicates.