Ms Access 简明教程

MS Access - Grouping Data

在本章中，我们将介绍如何在 Access 中按一组记录进行计算。我们已经创建了一个按行或按记录进行计算的字段来创建行总计或小计字段，但是如果我们希望按一组记录而不是按各个记录进行计算该怎么办？我们可以通过创建称为 Aggregate Query 的内容来执行此操作。

In this chapter, we will be covering how to calculate on a group of records in Access. We have created a field that calculates row by row or record by record to create a line total or subtotal field, but what if we wanted to calculate down by a group of records rather than by individual ones. We can do this by creating what’s known as an Aggregate Query.

Aggregate Query

聚合查询也称为总计或汇总查询是一种求和、求总量或分组查询。它可以是总金额或总量，也可以是一组记录或记录子集。聚合查询可以执行多种操作。这是一个简单的表格，列出了根据一组记录求总和的一些方法。

An aggregate query also known as a totals or summary query is a sum, mass or group particulars. It can be a total or gross amount or a group or subset of records. Aggregate queries can perform a number of operations. Here’s a simple table listing some of the ways to total on a group of records.

S.No	Aggregate Functions & Description
1.	Sum Adds the field values
2.	Avg Average of the field values
3.	Min Lowest (minimum) field value
4.	Max Highest (maximum) field value
5.	Count Count of the values (records)
6.	StDev Standard deviation of the field values including date/time fields
7.	Var Variance of the field values including date/time

让我们打开数据库，转到查询设计并包含以下表格 -

Let us open your database and go to the Query Design and include the following tables −

关闭此对话框，以下两个字段将显示在查询网格中，如下面的屏幕截图所示。

Close this dialog box and the following two fields will be displayed as shown in the query grid in the following screenshot.

这是一个简单的查询，我们只显示了两个字段—— book title 和 quantity ，当我们运行它时，我们将在我们的数据库中看到每一个单个订单。

This is a simple query and we are displaying only two fields — book title and quantity and when we run it we’re seeing every single individual order in our database.

现在让我们运行这个查询，您将看到以下结果。

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

这就是书名重复的原因。此处为每本图书分别下了不同的订单，并且它们订购的数量不同。让我们假设我们只想查看只列出一次的书名的摘要。然后列在其旁边的数量之和。

This is why book titles are repeating. Separate orders have been placed for each of these books here and they were ordered in different quantities. Let us assume we want to see a summary of only that book title that has been listed only once. And then the sum of the quantity listed beside it.

现在让我们转到设计视图，在设计选项卡中，您会看到一个西格玛符号。这是您的总计按钮。

Let us now go to the Design View and in the Design tab, you will see a Sigma symbol. This is your totals button.

单击西格玛符号，它将在表格行中的字段下方打开另一行，您可以在此指定如何对该查询进行分组。

Click the sigma symbol which will open another row underneath your field in the table row and from here, you can specify how you are grouping this query.

现在我们按书名进行分组，并对数量字段进行求和。

We will now group by book title and also sum our quantity field.

如果我们单击 group by area 并进一步单击下拉菜单，所有的选项都将被列出来。在本例中，我们将选择选项 Sum ，然后运行您的查询。

If we click on group by area and further click on the drop-down menu, all the options will be listed down. In this case, we will choose the option Sum and then run your query.

现在您可以看到每本书，以及所有单笔订单，这些订单显示在书名旁边。

You can now see each individual book and also all the individual orders displayed beside the book name.

Concatenation in Access

我们学习了规范化的过程，将信息存储在不同的字段中。有时您希望一起查看或查看数据，例如将名字和姓氏字段合并为单个字段。您可以通过创建一个连接一个或多个字符串的计算字段来一起显示这些信息。您还可以添加您想要的其他字符，如逗号或句点。

We learned the process of normalization, storing information in separate fields. Sometimes you want to see or view data together like combining the first name and last name fields as a single field. You can display that information together by creating a calculated field that concatenates one or more strings. You can also add other characters like a comma or period that you may want.

To concatenate in Access, there are two different operators you can use the ampersand (&) and the plus (+) sign.
The ampersand will combine two strings into one whereas the plus sign will combine two strings and propagate NULL values, for example, if one value is NULL the entire expression evaluates to null.

Example

让我们举一个简单的例子来理解使用查询设计创建新查询的过程。这将是一个非常简单的查询，它将从客户表中提取信息。

Let us take a simple example to understand the process of creating a new query using query design. This is going to be a very simple query that is pulling information from our customers’ table.

现在，让我们添加 tblCustomers 关闭表的表格对话框。我们尝试将一些简单的命名字段与一些连接进行连接。

Let us now add tblCustomers table close that show table dialog box. We will try some concatenation with some simple named fields.

现在，让我们添加名和姓字段并运行查询。

Let us now add the first name and last name fields and run the query.

如你所见，名和姓被分成了两个不同的字段。

As you can see, the first name and the last name are separated into two different fields.

我们需要一起显示这些信息。

We need to display this information together.

回到 Design View ，并在第三个字段创建一个名为 full name 的新字段。现在，添加全名并输入将这两个字段连接在一起的表达式。

Go back to the Design View and in the third field create a new field called full name. Now, add the full name and type the expression that concatenates those two fields together.

让我们运行查询，然后你将看到一个新的计算字段。

Let us run the query and you will see a new calculated field.

它可以连接来自这两个字段的信息，但它并非完全按照我们希望的方式显示。它将所有文本运行在一起，因为它正在按照我们要求的方式执行。现在，我们将不得不要求 Access 在这两个字段的信息之间添加空格。

It can concatenate the information from these two fields together, but it doesn’t exactly appear the way we want it to. It runs all of that text together because it’s performing the way we have asked it to. We will now have to ask Access to add space in between the information from these two fields.

现在让我们回到设计视图并向该表达式添加另一个部分。

Let us now go back to the Design View and add another section to this expression.

在引号内添加一个空格和另一个和号。这使得 Access 取自名字段的信息；用空格将它添加，然后在最后添加姓字段的信息。现在，再次运行查询，然后你将看到以下结果。

Add a Space inside quotes and another ampersand. This makes Access take the information from that first name field; add it with a space and then add the information from the last name field at the very end. Now, run the query again and you will see the following results.