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.

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.

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.

Some expressions can get long, and the query grid does not give you much room to see them. Fortunately, Access has a nifty feature called a Zoom Box that displays your expression so that you can see it while you work on it. To display your expression in a Zoom Box, place the cursor somewhere in the expression and press Shift+F2. Click OK on the Zoom Box to return to Design view.

For many years, we have created calculated columns using an expression in a query. In many cases, calculating the value each time in a query is desired as values in a row may change. In this case, each time you run the query, the expression is reevaluated for all rows. Since the expression is evaluated every time, this may slow down the query depending on the number of rows and the complexity of the calculation.
In Access 2010, a new type of field was added called a calculated field. In a calculated fi eld, the expression for the calculation is defined in the field, and the calculated values are stored in the table. These values are updated only when a field used in the expression is updated, making calculated fields nearly as fast as other fields. At first glance, it appears that storing calculated values would violate database normalization rules. This is true.
However, there are scenarios when data may not change very often where this type of denormalization is valuable. For example, first and last name are often concatenated to form a person's full name.
Names may not change very often, so this example is a good candidate for using the new calculated field type. Also, calculated fields can be indexed if desired, allowing faster retrieval and sorting of records based on the calculated value.

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