Ms Access 简明教程

MS Access - Summarizing Data

如果你只想要一个特定的数字，汇总查询非常棒，但是，如果你想要汇总信息，比如电子表格式汇总，你可能想要考虑尝试交叉表查询。当您想要重组汇总数据，以便更轻松地阅读和理解时，请考虑使用 Crosstab Query 。

Aggregate queries are great if you are looking just for one specific number, but if you want summarizing information like a spreadsheet-like summary you might want to consider trying a crosstab query. When you want to restructure summary data to make it easier to read and understand, consider using a Crosstab Query.

  1. A crosstab query is a type of Select Query. When you run a crosstab query, the results get displayed in a datasheet. This datasheet has a different structure from the other types of datasheets.

  2. The structure of a crosstab query can make it easier to read than a simple select query that displays the same data, as shown in the following screenshot.

迄今为止，创建 Access 交叉查询的最简单方法是直接使用 Access 自带的向导来创建交叉查询。现在我们转到“创建”选项卡，进入“查询”组，然后单击“查询向导”。

By far, the simplest way to create a crosstab query in Access is to simply use the wizard that comes with Access to create your crosstab query. Let us now go to the Create tab, in the queries groups and click on query wizard.

new query

在上面的对话框中，可以看到各种特殊查询。你可以创建一个简单的查询向导，就像我们迄今为止一直从设计视图执行的操作一样。第二个查询向导是我们想要的——以紧凑型电子表格格式显示数据的交叉查询。现在，选择交叉查询，然后单击“确定”。

In the above dialog box, you can see all kinds of special queries. You can create a simple query wizard like how we have been doing so far from the Design View. The second one is the one we want — the crosstab query that displays data in a compact spreadsheet like format. Now, select the crosstab query and click Ok.

crosstab

向导中的第一个屏幕会提示你的交叉查询结果中包含哪些字段的表或查询。单击“ Queries ”单选按钮，然后选择“ qryOrdersInformation ”——这是我们之前创建的包含小计、销售税等的表格。单击“ Next ”。

The first screen in the Wizard is going to prompt what table or query contains the fields that you want for your Crosstab Query results. Click on the Queries radio button and select qryOrdersInformation — this is the one that we created earlier which contain the subtotal, sales tax etc. Click Next.

crosstab query

现在我们将查看该查询中可用的字段。系统会提示你输入要作为行标题的字段值。假设我们希望按我们所有不同的书名生成一个简单列表。现在，选择书名字段，然后将其发送到所选字段区域，并单击“ Next ”。

We will now look into the available fields from that query. It will prompt you to enter the field values that you want as row headings. Let us say we want a simple list of all of our different book titles. Now select the book title fields and send that over to your selected field area and click Next.

transaction

在上面的对话框中，问题是你希望将什么用作列标题，这实际上取决于你的评估内容。假设你要查看我们的“ sales by date ”，选择“ TransactionDate ”，然后单击“ Next ”。

In the above dialog box, the question is what you want to use as column headings and that really depends on what you want to evaluate. Let us assume that you want to view our sales by date, choose TransactionDate and click Next.

transaction date

由于我们选择了日期时间列，因此上面对话框中的问题是一个专门提出的问题，基于我们之前选择字段。它询问我们是否希望按特定的间隔对我们的日期时间列信息进行分组。选择年份选项，然后单击“ Next ”。

The question in the above dialog box is a specialized one based on the field we previously selected since we chose a date time column. It is asking if we want to group our date time column information by a specific interval. Select the year option and click Next.

function

上面对话框屏幕中的下一个问题询问你希望为行交集中的每一列计算什么数字。在这种情况下，我们可以按所售书籍的数量进行计算，选择“ quantity (QTY) ”，从函数中求和，然后单击“ Next ”。

The next question in the above dialog screen asks what number do you want calculated for each column in the row intersection. In this case, we can go by quantity of the books sold by selecting quantity (QTY) and Sum from the functions and click Next.

view query

向导的最后一个屏幕将询问“ what do you want to name your query ”，然后单击“ Finish ”以“ View that query ”。

The very last screen of your wizard is going to ask what do you want to name your query and further, click Finish to View that query.

finish query

我们现在获得按书分类的信息以及总销售额等信息，例如每一销售额何时发生。

We now have book by book information and also information on the total sales like when each one of those sales occurred.