Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Creating Calculated Fields

One of the rules of data normalization is that the results of calculations shouldn't be included in your database. You can output the results of calculations by building those calculations into your queries, and you can display the results of the calculations on forms and reports by making the query the foundation for a form or report. You can also add controls to your forms and reports containing the calculations you want. In certain cases, this can improve performance. (This topic is covered in more depth in Chapter 15, "Debugging: Your Key to Successful Development.")

The columns of your query result can hold the result of any valid expression, including the result of a user-defined function. This makes your queries extremely powerful. For example, you can enter the following expression:

Left([FirstName],1) & "." & Left([LastName],1) & "."

This expression would give you the first character of the first name followed by a period, the first character of the last name, and another period. An even simpler expression would be this one:

[UnitPrice]*[Quantity]

This calculation would simply take the UnitPrice field and multiply it by the Quantity field. In both cases, Access would automatically name the resulting expression. For example, the calculation that results from concatenating the first and last initials is shown in Figure 4.18. Notice that in the figure, the expression has been given a name (often referred to as an "alias"). To give the expression a name, such as Initials, you must enter it as follows:

Initials:Left([FirstName],1) & "." & Left([LastName],1) & "."

The text preceding the colon is the name of the expressionin this case, Initials. If you don't explicitly give your expression a name, it defaults to Expr1.

Follow these steps to add a calculation that shows the unit price multiplied by the quantity:

NOTE

You can enter any valid expression in the Field row of your query design grid. Notice that field names included in an expression are automatically surrounded by square brackets, unless your field name has spaces. If a field name includes any spaces, you must enclose the field name in brackets; otherwise, your query won't run properly. This is just one of the many reasons why field and table names shouldn't contain spaces.