MS Access —

Creating a Calculated Field

Normally, when you create a database, you should only have to enter the information you need and not worry about data or values that Access calculates based on information already in the database. A calculated field performs some type of arithmetic on one or more fields in a database to come up with a completely new field.

Creating a field.

To enter fields in an expression, type the field name in brackets ([Order Total]).

If a field name exists in more than one table, you will need to enter the name of the table that contains the field in brackets ([Customer Tours]) followed by an exclamation mark (!). Then type the field name in brackets ([Order Total]).

For example, if your database has an Order Total field and a Tax Rate field, Access can calculate these two fields to find out the Sales Tax for each order: [Order Total] x [Tax Rate] = [Sales Tax].

You must create an expression (or formula) to perform a calculation. To enter fields in an expression, type the field name in brackets ([Order Total]), as described in figure. If a field name exists in more than one table, you will need to enter the name of the table that contains the field in brackets ([Customer Tours]) followed by an exclamation mark (!). Then type the field name in brackets, such as [Order Total], as described in figure. For example if an Orders table and a Shipping table both contain a Date field, you would tell Access which of the two Date fields you want to use by typing the table name ([Orders]), an exclamation mark (!), and then the field name ([Date]) or, in other words, [Orders]![Date]. Yes, can be a little confusing at first...

This lesson will show you how to add a calculated field to a query.

  1. If necessary, open the Lesson 6 database.

    Now let's open the qryEmployeeSales query in Design view.

  2. Click the Queries icon in the Objects bar if necessary, click the qryEmployeeSales query, and then click the Design button.

    Instead of creating a query from scratch, you can modify an existing query and save it with a different name.

  3. Click the blank Field cell of the fourth column and type Bonus:[Cost]*[Commission], as shown in figure.

    You can also use the Expression Builder to help you create your calculated fields. Click the Builder button on the toolbar, double-click the field you want to use in the calculation, click the button that corresponds to the calculation you want, and then click or type any other fields or values you want to use.

    The expression you entered will create a new calculated field named "Bonus" that will display the results of the Cost field in the tblCustomerTours table multiplied by the Commission field in the tblEmployees table.

    Let's see the results of our calculated field.

  4. Click the Run button on the toolbar.

    Other ways to run a query are to open the query from the Database window, or click the View button on the toolbar in Design view, or select Query » Run from the menu.

    Access displays the results of the query. The "Bonus" calculated field multiplies the Cost field by the Commission field in each record and displays the results.

    Save your changes in a new query with a different name. Here's how:

  5. Select File » Save As from the menu.

    The Save As dialog box appears.

  6. Type qryEmployeeBonus in the Save Query 'qryEmployeeSales' To: box and click OK.

    Access saves your changes in a new query named "qryEmployeeBonus."

  7. Close the query.

You're already familiar with some of the used in expressions; including math symbols such as the plus sign (+) to perform addition between values, and the minus sign (-) to perform subtraction between values. Check out table below for a refresher on the various arithmetic operators.

Arithmetic Operators

* Multiplication

+ Addition

- Subtraction

/ Division

^ Exponentiation

To create an expression or calculation in a query:

  1. display the query in design view.

  2. click the field row of a blank column in the design grid.

  3. enter the field name for the calculated field followed by a colon (:).

  4. enter the expression you want access to calculate, using the proper syntax.

    or...

    click the build button on the toolbar, double-click the field you want to use in the calculation, click the button that corresponds to the calculation you want, and then click or type any other fields or values you want to use. when you're finished, click ok.

  5. click the view button or run button on the toolbar to see the results.