Add tables

Combine fields

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Use calculated fields to concatenate data from separate fields, like first and last name; or to calculate dates, like order date plus five days.

Use calculated fields to:

  • Calculate values that don’t exist in your data.

  • Specify criteria for queries.

For example, combine first and last name data to display in a full name field, like this:

[First Name] + " " + [Last Name]

The plus signs (+) combine the value in the First Name field, a space character (a space enclosed in quotation marks), and the value in the Last Name field.

Or calculate the quantity, price, and tax rate for each item in an order to display the total price, like this:

([Quantity]*[Unit Price])+([Quantity]*[Unit Price]*[Tax Rate])

Note: The calculation can’t include fields from other tables or queries. The results of the calculation are read-only, but they are usable as values in other calculated fields.

Calculated fields display results based on the data type you chose.

Data type

Results

Example

Text

Displays calculated data as a text string, exactly as typed in the referenced fields.

To display full names, concatenate values from first and last name fields with a space between them. To ensure sortability, remove “A” or “An” from the beginning of a book title.

Number

Displays calculated data as a number, which can be used in other mathematical calculations.

To display minimum inventory level, subtract average quarterly breakage from average quarterly sales.

Currency

Displays calculated data in the selected currency format.

To display order total in the specified currency, multiply order quantity by price per unit.

Yes/No

Displays calculated data as Yes or No. Stores data as a Boolean value.

To display Yes if the discounted total exceeds the minimum needed for free shipping and No if it doesn’t, multiply order total by discount and validate against the minimum purchase amount that qualifies for free shipping.

Date/Time

Displays calculated data as a date or a time stamp.

To display estimated delivery date, add estimated shipping time to order date.

  1. Select a table.

    Selecting a table in Access
  2. Select Click to Add > Calculated Field, and then select a data type.

    Add a new field with the Calculated Field data type
  3. Enter a calculation for the field, and then click OK.

    Create the calculation in the new calculated field

    Type the expression yourself, or select expression elements, fields, and values to put them into the expression edit field.

    Note: In a calculated field, don’t start the expression with an equal sign (=).

  4. In the field heading, type a name for the calculated field and then press Enter.

    Naming a field in an Access table

    Note: To change or edit the output of a calculated field, select the column. Then, select Fields > Modify Expression.

After you add a calculated field to a table, the calculation is done each time you add or change data.

Want more?

Create an expression

Excel training

Outlook training

You can use the Calculated data type in a table to operate on fields and constant values in a variety of ways. Let’s see a quick demonstration.

To start, let’s select a blank field and apply the Calculated data type to it. For this example, let’s choose text. The Expression Builder opens.

An EXPRESSION is any combination of table fields, math operators like plus and equals, functions like Sum, and constant values such as customer names.

In the middle pane here, select first name and then last name. Add two plus signs between them. Between the plus signs, add a pair of double quotation marks with a space. This expression combines—or concatenates—the first and last names in this table.

Here's what’s going on.

THESE are the table fields and the plus sign combines the value in each field. If we didn't want a space between the names, we could just use a single plus sign. But because we do want a space between the names, we add one between the double quotation marks.

In an expression, quotation marks display anything you put between them—even spaces.

So we use two plus signs to combine the names with a space, select OK, and name the field customer name.

Let’s see it work. We type a first name and a last name. Now the calculated field displays the entire name. This calculated field can be really handy when you create the relationship between these two tables.

Now you see how you can use the Calculated data type in a table and the Expression Builder to operate on table fields and constant values.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×