Try the Complete Access Course for Free!

Create a Calculated Field in Access – Instructions

/ / Access 2013, Access 2016, Access 2019, Access for Office 365, Latest, Microsoft, Office 2013, Office 2016, Office 2019, Office 365

Video Lesson on How to Create a Calculated Field in Access:

            This video lesson, titled “Creating a Calculated Field,” shows how to create a calculated field in Access. This video lesson is from our complete Access tutorial, titled “Mastering Access Made Easy v.2019 and 365.”

Overview:

            You can easily create a calculated field in Access queries. A calculated field is a field that derives its value by performing a function on values from other table fields. It can also calculate values entered by hand. The field’s data only appears for the duration of the query. It is not actually stored in the database tables. When you create a calculated field in Access, you can perform almost any available function. You can also use any available query field or data entered by hand as values for the calculations.

            To create a calculated field in Access queries, open the query into which to insert the calculated field in design view. In the “Field Name” text box in the first available, blank column within the QBE grid, type a name for the new calculated field. Follow the name you enter with the colon symbol (:) and a space.

            Then enter the expression or formula to evaluate and show in the new field. If referencing table fields, the name of the field must appear in brackets. If you have two fields with the same name in two different tables, then you must specify the name of the table in brackets first ([]), then a period (.), followed by the field name enclosed in brackets ([]). For example: [Table 2].[Field4]. You can only refer to fields available in the tables added to the query, not just any table in your database.

            Finally, click the “Save” button in the Quick Access toolbar to save your changes.

A picture that shows how to create a calculated field in Access in query design view.

A picture that shows how to create a calculated field in Access in query design view.

Instructions on How to Create a Calculated Field in Access:

  1. To create a calculated field in Access queries, open the query into which to insert the calculated field in query design view.
  2. Then click into the “Field Name” text box in the first available, blank column in the QBE grid and type a name for the new calculated field, followed by the colon symbol (:) and a space.
  3. Next, type the expression or formula to evaluate and display in the new field.
  4. If referencing table fields, the name of the field must appear in brackets.
  5. If you have two fields with the same name in two different tables, then you must specify the name of the table in brackets first ([]), then a period (.) followed by the field name enclosed in brackets ([]). For example: [Table 2].[Field4].
  6. You can only refer to fields available in the tables added to the query, not just from any table in your database.
  7. Finally, click the “Save” button in the Quick Access toolbar to save your changes.
TOP