创建查询

出自OOo 中文指南手册翻译

Queries are used to get specific information from a database. Using our CD-Collection table, we will create a list of albums by a particular artist. We will do this using the Wizard. The information we might want from the Fuel table includes what our fuel economy is. We will do this using the Design View.

Top of page

目录 1 使用向导创建查询 1.1 第一步：选择字段。 1.2 第二步：选择排列顺序。 1.3 第三步：选择搜索条件。 1.4 第四步：选择查询类型。 1.5 第五步：排列别名。 1.6 第六步：预览。 1.7 第七步：修改查询。 2 使用设计视图创建查询 2.1 第一步：在设计视图中打开首个查询。 2.2 第二步：添加表格。 2.3 第三步：在表格底部添加字段。 2.4 第四步：设置查询标准。 2.5 第五步：保存并关闭查询。 2.6 第六步：创建查询，计算燃油费用 。 2.7 第七步：在查询底部添加字段到表格。 2.8 第八步：输入燃油ID差额字段。 2.9 第九步：运行查询，并进行修改。 2.10 第十步：关闭，保存，并命名查询。 3 相关链接

[编辑] 使用向导创建查询

由向导创建的查询，可以提供一个或多个基于用户需求的信息清单。获得一个还是多个清单取决于环境。需要进行运算的查询，使用设计视图来创建是最好的。

在数据库主窗口，单击数据库栏的查询图标，然后在任务栏，单击使用向导创建查询。查询向导窗口开启。此处需要的信息是，音乐专辑是哪个团体或是哪个人的（这张专辑的作者）。还可以包含每张专辑是何时购买的。

Queries created by the wizard provide a list or lists of information based upon what one wants to know. It is possible to obtain a single answer or multiple answers, depending upon the circumstances. Queries which require calculations are best created with the Design view.

In the main database window, click the Queries icon in the Databases section, then in the Tasks section, click Use Wizard to Create Query. The Query Wizard window opens. The information we want is what albums are by a certain musical group or individual (the album's author). We can include when each album was bought.

Top of page

[编辑] 第一步：选择字段。

1. 从表格的下拉列表中选择CD专辑表格。
   • 如果选中的表格不是CD专辑 表格， 点击箭头（在图片66中以红色圈中）。
   • 在列表中点击选中CD专辑 表格。
2. 从CD专辑表格的可用字段列表中选取字段。
   • 点击唱片标题，使用 > 按钮来移动其到查询字段列表中。
   • 以同样方式移动艺术家和购买日期字段。
3. 使用箭头up来改变字段顺序：艺术家，音乐专辑，购买日期。
   • 点击CD专辑.艺术家字段。
   • 点击箭头up来移动其到CD专辑.唱片标题之上。
4. 点击 继续。

1. Select the CD-Collection table from the dropdown list of tables.
   • If the Tables selection is not Table: CD-Collection, click the arrow (circled in red in Figure 66).
   • Click Table: CD-Collection in the list to select it.
2. Select fields from the CD-Collection table in the Available fields list.
   • Click AlbumTitle, and use the > button to move it to the Fields in Query list.
   • Move the Artist and DatePurchased fields in the same manner.
3. Use the up arrow to change the order of the fields: artist, album, and date purchased.
   • Click the CD-Collection.Artist field.
   • Click the up arrow to move it above CD-Collection.AlbumTitle.
4. Click Next.

File:QueryFields.png

List of fields added to the query.

Top of page

[编辑] 第二步：选择排列顺序。

四个以上的字段才能够使用排列查询信息。在这一点上，一个简单的逻辑可以帮到我们。哪个字段最重要？

此处的查询，艺术家字段最重要。唱片标题字段次之。购买日期字段的重要性最小。 当然，如果我们对给定日期时购买的是何种音乐感兴趣，购买日期字段将会最重要。

Up to four fields can be used to sort the information of our query. A little simple logic helps at this point. Which field is most important?

In our query, the artist is most important. The album title is less important, and the date purchased is of least importance. Of course, if we were interested in what music we bought on a given day, the date purchased would be the most important.

File:BaseSortBy.png

Sorting order page.

1. 点击首个排列依据下拉清单。
   • 点击CD专辑.艺术家并选中。
   • 如果需要艺术家在清单中按照字母顺序(a-z)排列，在右侧选择升序。如果需要艺术家在清单中按照字母顺序(z-a)排列，在右侧选择降序。
2. 点击第二个排列依据下拉清单。
   • 点击CD专辑.艺术家。
   • 根据排列需求选择升序还是降序 。
3. 对CD专辑.购买日期重复此过程。
4. 点击 继续。

1. Click the first Sort by dropdown list.
   • Click CD-Collection.Artist to select it.
   • If you want the artists to be listed in alphabetical order (a-z), select Ascending on the right. If you want the artist listed in reverse order (z-a), select Descending on the right.
2. Click the second Sort by dropdown list.
   • Click CD-Collection.ArtistTitle.
   • Select Ascending or Descending according to the order you want.
3. Repeat this process for CD-Collection.DatePurchased.
4. Click Next.

Top of page

[编辑] 第三步：选择搜索条件。

可用搜索条件在下面列出。这些使得我们可以将输入的艺术家名称同数据库中的进行比对，并决定在查询中是否要包含指定的艺术家。 The search conditions available are listed below. They allow us to compare the name we entered with the names of the artist in our database and decide whether to include a particular artist in our query or not.

• is equal to：等于
• is not equal to：不等于
• is smaller than：小于
• is greater than：大于
• is equal or less than：等于或小于
• is equal or greater than：等于或大于
• like：类似于

• is equal to: the same as
• is not equal to: not the same as
• is smaller than: comes before
• is greater than: comes after
• is equal or less than: the same as or comes before
• is equal or greater than: the same as or comes after
• like: similar to in some way

1. 此处仅搜索一项，就用 默认设定 匹配所有如下条件。
2. 寻找指定的艺术家，因此选择 等同于.
3. 在 数值 框中输入艺术家的名称。点击 继续。

1. Since we are only searching for one thing, we will use the default setting of Match all of the following.
2. We are looking for a particular artist, so select is equal to.
3. Enter the name of the artist in the Value box.Click Next.

Top of page

[编辑] 第四步：选择查询类型。

仅需要单一信息，就用默认设定：细节查询 即可。在窗口底部点击 继续。 We want simple information, so the default setting: Detailed query is what we want. Click Next at the bottom of the window.

[编辑] 第五步：排列别名。

用默认设定。点击 继续。 We want the default settings. Click Next.

[编辑] 第六步：预览。

命名查询 （推荐：艺术家查询）。这右侧有两个选项。选择 显示查询。点击完成。 Name the query (suggestion: Query_Artists). To the right of this are two choices. Select Display Query. Click Finish.

[编辑] 第七步：修改查询。

不需要修改，就跳过此步。如果选择修改查询选项，查询会在设计视图中打开。要做修改，依照下一章使用设计视图修改查询中的使用说明。 We are skipping this step since we have nothing to modify. If you select the Modify Query choice, the query would open in Design view. To make modifications, follow the instructions in the next section, “Using the Design View to create a query".

Top of page

[编辑] 使用设计视图创建查询

使用设计视图创建查询并不像首次看起来的那样复杂。这要经过多个步骤，但每个步骤都相当简单。

车辆花去的燃油费用如何（在美国每加仑的英里数）？这个问题需要创建两个查询， 首个查询以及以此为起点的二次查询。

Creating a query using Design View is not as hard as it may first seem. It may take multiple steps, but each step is fairly simple.

What fuel economy is our vehicle getting (miles per gallon in the USA)? This question requires creating two queries, with the first query being used as part of the second query.

Top of page

[编辑] 第一步：在设计视图中打开首个查询。

点击 在设计视图中创建查询。

Click Create Query in Design View.

[编辑] 第二步：添加表格。

1. 点击 燃油 高亮显示。
2. 点击 添加。
3. 点击 关闭。

1. Click Fuel to highlight it.
2. Click Add.
3. Click Close.

Top of page

[编辑] 第三步：在表格底部添加字段。

1. 在燃油表格中双击 燃油ID 字段。
2. 双击 里程表字段。
3. 双击 油料数量字段。

查询窗口的表格底部会出现三个列。

1. Double-click the FuelID field in the Fuel table.
2. Double-click the Odometer field.
3. Double-click the FuelQuantity field.

The table at the bottom of the query window should now have three columns.

Top of page

[编辑] 第四步：设置查询标准。

此处需要以数字1开始燃油ID查询。

1. 在查询表格燃油ID的正下方的标准单元格中输入 >0 。
2. 在查询视图工具条中点击运行查询 图标。图标如下以红色圈中处。

File:QueryDesignToolbar.png

Query Design toolbar.

下面的图形包含了输入数据的燃油表，以及基于燃油表格的查询结果。

We want to the query's FuelID to begin with the numeral 1.

1. Type >0 in the Criterion cell under FuelID in the query table
2. Click the Run Query icon in the Query Design toolbar. This icon is circled in red below.

File:QueryDesignToolbar.png

Query Design toolbar.

The figures below contain the Fuel table with my entries and the query results based upon the Fuel table.

[编辑] 第五步：保存并关闭查询。

由于这个查询包含有用计算读取的最终里程表，保存时就把其命名为最终读数。然后关闭查询。 Since this query contains the ending odometer reading for our calculations, name it End-Reading when saving it. Then close the query. Top of page

[编辑] 第六步：创建查询，计算燃油费用 。

1. 点击 使用设计视图创建查询 打开一个新查询。
2. 添加燃油表格到查询中，操作和第二步：添加表格相同。但不要关闭添加表格窗口。
3. 把最终读数查询添加到此条查询中。
1. 点击 查询 雷达按钮来获取数据库中的查询清单。
File:SelectQuery.png
Selecting queries to add to another query.
2. 点击最终读数。
3. 点击 添加，然后再点击 关闭。

1. Click Create Query in Design View to open a new query.
2. Add the Fuel table to the query just as you did in step 2: Add tables. But, do not close the Add Tables window.
3. Add the End-Reading query to this query.
1. Click the Query radio button to get the list of queries in the database.
File:SelectQuery.png
Selecting queries to add to another query.
2. Click End-Reading.
3. Click Add, and then click Close.

Top of page

[编辑] 第七步：在查询底部添加字段到表格。

此处将计算燃油费用。如此，就需要燃油量和行驶距离。由于这里的燃油量在里程表读数的末尾，就用最终读数查询来获取。此处将使用源自燃油表格和最终读数查询中的里程表字段。

1. 在最终读数查询中双击 燃油量 。
2. 在最终读数查询中双击 里程表。
3. 在燃油表中双击 里程表。

File:AddFieldsToQ.png

Added fields to the query.

We are going to calculate the fuel economy. To do this we need the FuelQuantity and distance traveled. Since the FuelQuantity we want to use is at the ending odometer reading, we will use the End-Reading query to get it. We will also use the Odometer field from the Fuel table and End-Reading query.

1. Double-click FuelQuantity in the End-Reading query.
2. Double-click Odometer in the End-Reading query.
3. Double-click Odometer in the Fuel table.

Top of page

[编辑] 第八步：输入燃油ID差额字段。

此处需要燃油表的燃油ID的数值和最终读数查询的燃油ID的数值差额等于一。

1. 在燃油表的里程表字段右侧的字段中，输入最终读数 .燃油ID - 燃油.燃油ID。
   在该列的标准单元格中数字1。

File:BaseCalcFields.png

Typing in calculation of fields.

输入这些字段用于计算时，必须按照这种格式：表或查询的名称，后面跟随一个其中的字段名称。对于带有连字符号的或多个词组的名称（表或查询），在其前后分别加上双引号。该查询会为其余部分增加一对双引号，如上图。 在两者之间使用算术符号。可以通过使用括号分组进行算术运算可以完成一个以上的计算。

When entering fields for these calculations, you must follow this format: table or query name followed by a period follow by the field name. For hyphenated or multiple-word names (table or query), use double quotes around the table or query name. The query will then add the rest of the double quotes as in the figure above. Use the arithmetical symbol between the two. More than one calculation can be done by using parentheses to group the arithmetical operations.




2. 计算行驶距离：
• 在字段单元格输入 最终读数.里程表 - 燃油.里程表。
• 在标准单元格中输入 >0。
File:DistTravel.png
Field for distance traveled calculations.
3. 计算燃油费用：
   在右边下一列的字段中输入 (最终读数.里程表 - 燃油.里程表)/最终读数.燃油量 。
File:FuelEcon.png
Fuel economy calculation field.

We want the difference between the FuelID value of the Fuel table and FuelID value of the End-Reading query to equal one (1).

1. Type "End-Reading".FuelID - Fuel.FuelID in the field to the right of the Odometer field of the Fuel Table.
   Type the numeral 1 (one) in the Criterion cell of this column.

File:BaseCalcFields.png

Typing in calculation of fields.




2. Calculate the distance traveled:
• Type "End-Reading".Odometer - Fuel.Odometer in the Field cell.
• Type >0 in the Criterion cell.
File:DistTravel.png
Field for distance traveled calculations.
3. Calculate fuel economy:
   Type ("End-Reading".Odometer - Fuel.Odometer)/"End-Reading".FuelQuantity in the Field in the next column to the right.
File:FuelEcon.png
Fuel economy calculation field.

Top of page

[编辑] 第九步：运行查询，并进行修改。

为确保执行查询后能正常运行，此处将隐藏的所有不需要的字段。 After we run the query to make sure it works correctly, we will hide all of the fields that we do not need.

1. 在设计查询的工具栏中点击运行查询图标。结果如下。
   请注意，并非所有最后一栏都是可见的，因为有些标签会很长。通过大部分字段使用别名，可以解决这个问题。这些标签会替换成它们的别名。
File:Query1.png
Result of running the fuel economy query.
2. 添加别名：键入别名，清单如下。
File:QTAlias.png
Query table with aliases added.
3. 再次运行该查询。结果如下。
File:Qrunwithalias.png
Query run with aliases.

此处不需要列来显示表和查询两者中燃油ID的区别。因此，将其隐藏。虽然不可见，它仍然会被用于计算。

4. 隐藏不需要显示的字段。
   删除在可见细胞复选框。
File:MakeFieldInvis.png
Making a field invisible in a query run.
5. 重新运行该查询。
File:QwithAliases.png
Query run with aliases.

1. Click the Run Query icon in the Design Query toolbar. The results are below.
   Notice that not all of the last column label is visible because some of the labels are long. We can fix this problem by using an alias for many of the fields. The labels are replaced by their aliases.
File:Query1.png
Result of running the fuel economy query.
2. Add Aliases: Type in aliases as they are listed below.
File:QTAlias.png
Query table with aliases added.
3. Run the query again. The results are below.
File:Qrunwithalias.png
Query run with aliases.

We really do not need the column showing the difference between the FuelID fields from the table and query. So, we will hide it. While it will not be visible, it will still be used in the calculations.

4. Hide a field that does not need to be seen.
   Remove the check in the box of the Visible cell.
File:MakeFieldInvis.png
Making a field invisible in a query run.
5. Rerun the query.
File:QwithAliases.png
Query run with aliases.

Top of page

[编辑] 第十步：关闭，保存，并命名查询。

推荐名称 燃油费用。

显而易见的是，还可以在这个查询中进行其它的计算，如每行驶距离的费用以及各种支付类型的费用是多少。

My suggestion for a name is Fuel Economy.

There are obviously other calculations that can be made in this query such as cost per distance traveled and how much of the cost belongs to each of the payments types.

Top of page

[编辑] 相关链接

本文原文