Ms Access 简明教程

MS Access - Calculated Expression

在 MS Access 中，表达式类似于 Excel 中的公式。它包含许多元素，可以单独或组合使用以产生结果。表达式可以包括运算符、常量、函数和标识符。

In MS Access, an expression is like a formula in Excel. This consists of a number of elements that can be used alone or in a combination to produce a result. Expressions can include operators’, constants, functions and identifiers.

  1. Expressions can be used to perform a variety of tasks from retrieving the value of a control or supplying criteria to a query to creating calculated controls and fields or defining a group level for report.

  2. In this chapter, we will be focusing on creating a very specific kind of expression called a calculated expression. We will create several calculated fields that will calculate and display data that is not stored anywhere within the database itself but calculated from separate fields that are stored.

我们现在有一个包含更多要计算数据的新 Access 数据库。

We now have a new Access database that contains more data to calculate.

Example

让我们尝试一个示例来理解这一点。我们将从各种表—— tblCustomers, tblOrders, tblOrdersDetails 和 tblBooks 中检索一些信息并查看各种域。

Let us try an example to understand this. We will be retrieving some information from a variety of tables — tblCustomers, tblOrders, tblOrdersDetails and tblBooks and reviewing a variety of fields.

reviewing

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

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

subtotal

我们想要一个非常简单的子计计算，该计算将采用订购的书籍数量并乘以该书籍的零售价。最好在每个表达式或任何表达式开头为要调用的字段指定一个名称。我们称之为 subtotal 。

We want a very simple calculation for a subtotal which will take the quantity of the books ordered and multiply that by the retail price of the book. It is a good idea to begin every expression or any expression with a name to call that field. Let us call this one subtotal.

expression

完整表达式 (Subtotal: [tblOrdersDetails]![QTY] [tblBooks]![RetailPrice])* 包括表名，首先是 tblOrdersDetails 放在方括号内，因为那里是我们数量域所在的地方。现在一个感叹号告诉 Access 在该表中查找 QTY 域，并将其乘以 tblBooks 中的零售价域。

The full expression (Subtotal: [tblOrdersDetails]![QTY][tblBooks]![RetailPrice])* includes the table name, first tblOrdersDetails inside square brackets because that is where our quantity field lives. Now an exclamation is telling Access to look inside that table for a field QTY and multiply that by the retail price field in tblBooks.

让我们尝试运行您的查询，您将在末尾看到子计字段。

Let us try to run your query and you will see at end the subtotal field.

subtotal field

假设我们要计算我们的销售税。在查询网格中，我们会以与之前完全相同的方式操作，这次我们将把该域简单地称为 Tax，然后将 Subtotal 乘以百分之九的税率。

Let us say we want to calculate our sales tax. In the query grid we will do in the exact same way we did before and this time we will call the field simply Tax and Let us multiply Subtotal by nine percent tax rate.

simply tax

让我们再次运行您的查询，您将在末尾看到新的 Tax field 。

Let us run your query again and you will see the new Tax field in the end.

subtotal field