Creating a Computed Column

A computed column is a column whose content is computed from an expression using values from other columns in the table. The computed column is then filled with the results.

Simple computed expressions can be entered directly in the Computed Expression pane on the Detail tab of the column property sheet. For more complex expressions, use the SQL Editor available through the Edit tool found on the same tab.

Computed columns are not available in all DBMS.

Assume that you want to automatically fill a column with the total sales of widgets. To do this, you can create a computed column that will use the number of widgets multiplied by the widget price:

Column name

Contents

Action on data

Number of widgets

Number of widgets sold

Widget price

Price of widgets when sold

Widget sales

Total widget sales

Computed by multiplying the first two columns

While our example is very simple, the SQL Editor allows you to define very complex computed column expressions.

For more information on the SQL Editor, see SQL Code Definition Toolbars.

  1. Double-click a table to open its property sheet, and click the Columns tab.
  2. Click the Add a Row tool, and then click the Properties tool to open the property sheet for the new column.
  3. On the General tab, select the Computed checkbox, and then click the Detail tab.
  4. Enter an expression in the Computed Expression box to define the computed column. Alternatively,you can click the Edit with SQL Editor tool to use the SQL Editor.

    In our example, we use the asterisk (*) as an arithmetic operator to multiply the number of widgets by their price.

  5. Click OK to return to the column property sheet.

    The expression is displayed in the Computed Expression pane.

  6. Click OK in each of the dialog boxes.