[ Team LiB ] Previous Section Next Section

Adding Calculated Fields to Select Queries

One of the rules of data normalization is that you should not 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 controls to forms and reports that contain the calculations you need. In certain cases, this can improve performance.

You can include in the columns of a query result the result of any valid expression, including the result of a user-defined function. 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]

graphics/newterm_icon.gif

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 15.1 shows the calculation that results from concatenating the first and last initials of the employee names in the Employees table of the Northwind database. Notice that in the figure, 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 15.1. The result of the expression Initials:Left([FirstName],1) & "." & Left([LastName],1) & "." in a query.

graphics/15fig01.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.

Task: Creating a Calculation Field

graphics/todo_icon.gif

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

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

  2. Click in 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 to zoom. The Zoom dialog box, shown in Figure 15.2, appears.

    Figure 15.2. Expanding the field with the Zoom function (Shift+F2).

    graphics/15fig02.jpg

  4. Click OK to close the Zoom dialog box. Access supplies a space after the colon and the square brackets around the field names if you omit them.

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

    Figure 15.3. The result of the total price calculation.

    graphics/15fig03.jpg

graphics/book_icon.gif

You can enter any valid expression in the Field row of a query grid. Notice that Access automatically surrounds field names included in an expression with square brackets. This happens automatically unless a field name has spaces. If a 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 you should not include spaces in field and table names.


Getting Help from the Expression Builder

The Expression Builder is a helpful tool for building expressions in queries, as well as in many other situations in Access. To invoke the Expression Builder, you click in the Field cell of the query grid and then click Build on the toolbar. The Expression Builder appears (see Figure 15.4). Notice that Access divides the Expression Builder into three columns. The first column shows the objects in the database. After you select an element in the left column, you can select the elements you want to paste from the middle and right columns.

Figure 15.4. Creating expressions in a query.

graphics/15fig04.jpg

The example in Figure 15.5 shows functions selected in the left column. Under Functions, Access lists both user-defined functions and Built-in Functions; here, I have expanded Functions and selected Built-In Functions. In the center column, I have selected Date/Time. After I selected Date/Time, all the built-in date and time functions appeared in the right column. If you double-click a particular function—in this case, the DatePart function—Access places the function and its parameters in the text box at the top of the Expression Builder window. Notice that the DatePart function has four parameters: interval, date, firstweekday, and firstweek. If you know what needs to go into each of these parameters, you can simply replace the parameter placeholders with your own values. If you need more information, you can invoke Help on the selected function and learn more about the required parameters. In Figure 15.6, I have filled in two parameters: the interval and the name of the field I want Access to evaluate. After I click OK, Access places the expression in the Field cell of the query.

Figure 15.5. The Expression Builder with the DatePart function selected and pasted in the expression box.

graphics/15fig05.jpg

Figure 15.6. The Expression Builder, after two parameters are filled in.

graphics/15fig06.jpg

    [ Team LiB ] Previous Section Next Section