Creating Queries  «Prev  Next»

Lesson 13 Creating calculated fields
Objective Add a calculated field to a query.

Creating Calculated Fields in Access

There may be times when you want to add a calculated field to your database. Perhaps you want to use two fields to calculate a third, or want to use one of Access’s functions to calculate a result. Although creating calculations in Access is not quite as simple as calculating in a spreadsheet such as Excel, they can be implemented in Microsoft Access. Your data will be more consistent and accurate if you learn how to perform calculations within your database. In order to create a calculated field you type an expression (sometimes called a formula) into the Field row in query Design view. The new field is given a name, and as long as you save the query, that new field is available for you to use in other queries as well as in forms and reports. This SlideShow shows you how to create a calculated field.

  1. This table contains information about sales.
  2. In order to create a calculated Commissions field
  3. In order to see all the fields in the Sales table, I double-click the asterisk(*) in the table box
  4. Type the expression for the new field into the Field row of the first empty column.
  5. The datasheet shows the new field, Commission

How To Create Calculated Fields
When you are creating an expression you must tell Access what kind of terms you are using. Remember that Access encloses some kinds of data between special characters. Well, when you are creating an expression you must be sure to type those special characters in, or Access will not understand the expression.

Special Characters
This type of data Looks like this in an expression
Text “text”
Date #1/1/00#
Time #1:00am#
Number 10
Field Name [field name]

If you need a refresher on those special characters, click the View Table button to look at the chart again.
Access also has a feature called Access Zoom Box to view your expression if it gets too long.

Creating Query-in Design View - Exercise

Try your hand at adding a calculated field to the Consulting Database in this exercise.
Creating Query-in Design View - Exercise