[ Team LiB ] Previous Section Next Section

Creating Calculated Fields

One of the rules of data normalization is that you shouldn't include the results of calculations in a database. You can output the results of calculations by building those calculations into 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 to forms and reports controls that contain the calculations you want. In certain cases, this can improve performance.

The columns of a query result can hold the result of any valid expression. This makes queries extremely powerful. For example, you could 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 multiply the UnitPrice field by the Quantity field. In both cases, Access would automatically name the resulting expression. For example, Figure 10.19 shows the calculation that results from concatenating the first and last initials. Notice in the figure that Access gives the expression 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) & "."
Figure 10.19. The result of using the expression Initials:Left([FirstName],1) & "." & Left([LastName],1) & "." in a query.

graphics/10fig19.jpg

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

graphics/book_icon.gif

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


Task: Adding a Calculation to a Query

graphics/todo_icon.gif

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

  1. Scroll to the right on the query design grid until you can see a blank column.

  2. Click the Field row for the new column.

  3. Type TotalPrice:UnitPrice*Quantity. If you want to see more easily what you're typing, press Shift+F2 (Zoom). The Zoom dialog box, shown in Figure 10.20, appears.

    Figure 10.20. Expanding a field by using the Zoom function (Shift+F2).

    graphics/10fig20.jpg

  4. Click OK to close the Zoom window.

  5. Run the query. The total sales amount should appear in the far-right column of the query output. The query output should look as shown in Figure 10.21.

    Figure 10.21. The result of a total price calculation.

    graphics/10fig21.jpg
    [ Team LiB ] Previous Section Next Section