Ms Access 简明教程

MS Access - Query Criteria

查询条件可帮助您从 Access 数据库中检索特定项目。如果某个项目与您输入的所有条件匹配，它将显示在查询结果中。当您想基于字段中的值来限制查询结果时，可以使用查询条件。

Query criteria helps you to retrieve specific items from an Access database. If an item matches with all the criteria you enter, it appears in the query results. When you want to limit the results of a query based on the values in a field, you use query criteria.

  1. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value.

  2. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.

  3. To add some criteria to a query, you must open the query in the Design View.

  4. You then identify the fields for which you want to specify criteria.

Example

让我们来看一个简单的示例，其中我们在查询中使用条件。首先打开您的 Access 数据库，然后转到“创建”选项卡，然后单击“查询设计”。

Let’s look at a simple example in which we will use criteria in a query. First open your Access database and then go to the Create tab and click on Query Design.

query design

在“显示表”对话框的“表”选项卡中，双击 tblEmployees 表，然后关闭对话框。

In the Tables tab on Show Table dialog, double-click on the tblEmployees table and then close the dialog box.

tblemployees

现在让我们向查询网格中添加一些字段，例如 EmployeeID、FirstName、LastName、JobTitle 和 Email，如下面的屏幕截图所示。

Let us now add some field to the query grid such as EmployeeID, FirstName, LastName, JobTitle and Email as shown in the following screenshot.

query grid

现在让我们运行您的查询，您将仅看到这些字段作为查询结果。

Let us now run your query and you will see only these fields as query result.

query result

如果您只想看到 JobTitle 为 Marketing Coordinator 的那些字段，那么您需要为此添加条件。让我们再次转到“查询设计”，然后在 JobTitle 的“条件”行中输入 Marketing Coordinator。

If you want to see only those whose JobTitle are Marketing Coordinator then you will need to add the criteria for that. Let’s go to the Query Design again and in Criteria row of JobTitle enter Marketing Coordinator.

marketing coordinator

现在让我们再次运行您的查询，您将看到仅检索了 JobTitle 为 Marketing Coordinator 的字段。

Let us now run your query again and you will see that only Job title of Marketing Coordinators are retrieved.

retrieved

如果您想为多个字段添加条件，只需在多个字段中添加条件。假设我们只想检索“Marketing Coordinator”和“Accounting Assistant”的数据；我们可以指定 OR 行运算符，如下面的屏幕截图所示 −

If you want to add criteria for multiple fields, just add the criteria in multiple fields. Let us say we want to retrieve data only for “Marketing Coordinator” and “Accounting Assistant”; we can specify the OR row operator as shown in the following screenshot −

accounting assistant

现在让我们再次运行您的查询，您将看到以下结果。

Let us now run your query again and you will see the following results.

run query

如果您需要使用 AND 运算符的功能，则必须在“条件”行中指定另一个条件。假设我们想检索所有 Accounting Assistants，但只检索 JobTitle 为“Pollard”的 Marketing Coordinators。

If you need to use the functionality of the AND operator, then you have to specify the other condition in the Criteria row. Let us say we want to retrieve all Accounting Assistants but only those Marketing Coordinator titles with “Pollard” as last name.

and operator

现在让我们再次运行您的查询，您将看到以下结果。

Let us now run your query again and you will see the following results.

run query