Ms Office Tune Up

Most queries simply display the raw data for the fields included in the design grid, proba­bly filtered by some criteria. In a business environment, however, you often need to

perform some kind of analysis on the dynaset. To do that, you need to introduce calcula­tions into your query by creating what is called a calculated column. This is a column in the dynaset where the “field” is an expression and the field values are the results of the expres­sion for each record.

With calculated columns, you can create extremely sophisticated queries that use the full power of Access’s expression-building features. Here are just a few examples:

• Suppose you have a table of products with a UnitPrice field and you want to display a new column that shows the unit price for each product plus a five percent increase. You can do that by creating a calculated column based on the following expression:

[UnitPrice] * 1.05

• Suppose you have a table of orders that contains a UnitPrice field and a Quantity field and want a new column that shows the order total. You can do that by creating a calcu­lated column based on this expression:

[UnitPrice] * [Quantity]

• Suppose you have a table of employees that includes FirstName and LastName fields and you want to see the names combined. You can do that by basing a calculated col­umn on the following expression:

[FirstName] & ” ” & [LastName]

The expression you use for a calculated column can be any combination of operator, identi­fier, and literal values, and there are many built-in functions you can use.

Building a calculated column is straightforward: Instead of specifying a field name when adding a column to the dynaset, you enter an expression. You type the expression directly into the column header in the design grid using the following general form:

ColumnName:expression

Here, ColumnName is the name you want to use for the calculated column, and expression is the calculation.

TIP

Calculated column expressions are routinely long, which means you do not see the entire expres­sion in the Fieldcell. One solution is to widen the column to fit the length of the expression. Alternatively, click anywhere inside the expression and press Shift+F2 to display the expression inside the Zoom window.

Figure 1 shows two examples of custom calculated columns. The first is the left hand column, which includes the following in the column header:

Employee: [FirstName] & ” ” & [LastName]

As described earlier in this section, this column combines the FirstName and LastName fields with a space in between, the results of which are displayed in a column named Employee.

Figure 1 Two examples of custom calculated columns in a query.

The second example is in the far right column, and it’s a bit more complex:

Years Employed: DateDiff(”yyyy”,[HireDate],Date())

This expression uses the DateDiff function to calculate how many years each person has been employed with the company. In this case, DateDiff calculates the difference between the employee’s hire date (given by the HireDate field) and the current date (given by the Date function). The yyyy string argument tells Access to return the number of years (rounded up to the next highest year).

The DateDiff function uses the following simplified syntax:

DateDiff(interval, date1, date2)

Table 1 Acceptable Values for the DateDiff Function’s interval Argument

Figure 2 The results generated by the query shown in Figure 1.

Technorati Tags: custom, calclated column, query

Popularity: 3% [?]