How to Create Calculated Fields in Microsoft Access on Windows 11

Posted on 17th June 2023

Microsoft Access is a relational database management system (RDBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately.

Calculated fields are one of the most powerful features in Microsoft Access. A calculated field is a field in a table that is derived from other fields in that table or from other tables in the database. You can use the information in the calculated field in queries, forms, and reports just as you would any other field.

There are two ways to create a calculated field:

1. Create a calculated field in a query
2. Create a calculated field in a table

Creating a calculated field in a query is the recommended way to create calculated fields. This is because the field is only calculated when the query is run, which makes the query run faster. Additionally, you can use the query design grid to easily see and edit the field’s calculation.

To create a calculated field in a query:

1. Open the query in Design view.
2. In the field list, double-click the field that you want to use in the calculation.
3. In the query design grid, in the Field row of the field that you want to use in the calculation, type an expression for the calculated field.

Creating a calculated field in a table is the recommended way to create a field that you will use often, such as a field that displays the average of several other fields. This is because the field is calculated and saved with the record, which makes the field available for use in other queries, forms, and reports.

To create a calculated field in a table:

1. Open the table in Design view.
2. In the Field row of the field that you want to use in the calculation, type an expression for the calculated field.
3. In the Data Type column of the calculated field, select the data type that you want from the list.
4. Set the field’s Format property, if desired.
5. Set the field’s Decimal Places property, if desired.

You can use the Field Properties section of the table design grid to set the properties for the field.

When you create a calculated field in a table, the field is calculated and saved with the record whenever you move to a new record or save the record.

There are two ways to create a calculated field in Microsoft Access on Windows 11. The first is to use the Expression Builder, and the second is to enter the calculation into the field’s properties.

Using the Expression Builder

To use the Expression Builder, open the table in Design View. Then, click on the field that you want to calculate, and click the “…” button next to the “Field Properties” section.

In the “Field Properties” window, click on the “…” button next to the “Default Value” property.

This will open the “Expression Builder”. In the “Expression Builder”, you can enter the calculation that you want to perform.

For example, to calculate the sum of two fields, you would enter the following expression:

[Field1] + [Field2]

You can also use the functions that are available in the “Function” list. For example, to calculate the average of two fields, you would enter the following expression:

Avg([Field1], [Field2])

Once you have entered the expression, click “OK” to close the “Expression Builder” window, and then click “OK” again to close the “Field Properties” window.

Entering the Calculation into the Field’s Properties

Another way to create a calculated field is to enter the calculation into the field’s properties. To do this, open the table in Design View. Then, click on the field that you want to calculate, and click the “…” button next to the “Field Properties” section.

In the “Field Properties” window, enter the calculation that you want to perform into the “Default Value” property.

For example, to calculate the sum of two fields, you would enter the following expression:

[Field1] + [Field2]

You can also use the functions that are available in the “Function” list. For example, to calculate the average of two fields, you would enter the following expression:

Avg([Field1], [Field2])

Once you have entered the expression, click “OK” to close the “Field Properties” window.

To continue creating calculated fields in Microsoft Access on Windows 11, follow these steps:

1. Open the table in Design view.

2. In the Field Name column, type the name of the new field.

3. In the Data Type column, select Calculated.

4. In the Expression column, type the calculation.

5. To use a field from another table in the calculation, click the Insert Field button.

6. Select the field you want to use, then click OK.

7. To use a built-in function in the calculation, click the Insert Function button.

8. Select the function you want to use, then click OK.

9. To save the field, click the Save button.

When you have multiple fields in your Microsoft Access table that contain numeric data, you may want to create a calculated field. This is a field that contains the result of a mathematical operation performed on other fields in the table. For example, you could create a calculated field that multiplies the values in two fields and stores the result in a third field.

To create a calculated field:

1. Open the table in Design view.

2. Click the field where you want the calculated field to appear.

3. Type the name of the field.

4. In the Field Properties section, click the Data Type list and select Calculated.

5. In the Field Properties section, click the Calculation text box and type the calculation you want to perform.

6. To refer to other fields in the table in your calculation, type the name of the field enclosed in square brackets ([FieldName]).

7. When you have finished entering the calculation, click the Save button on the toolbar.

Your calculated field will now appear in the table.

Create a query in Microsoft Access on Windows 11 by clicking the “Create” tab on the Ribbon and then selecting “Query Design” from the “Queries” group. This will open the “Show Table” dialog box. Select the table or tables that contain the fields that you want to use in your query and then click “Add.” Close the “Show Table” dialog box.

In the query design grid, click the “Field” column of the field that you want to use in your calculation and then select the “Calculated Field” option from the drop-down menu. This will open the “Calculated Field” dialog box.

In the “Field name” text box, type a name for your calculated field. In the “Field type” drop-down menu, select the data type for your calculated field. In the “Total” drop-down menu, select the type of calculation that you want to perform. Your options are “Sum,” “Average,” “Count,” “Minimum,” “Maximum” and “Standard Deviation.”

In the “Decimal places” text box, type the number of decimal places that you want to use for your calculation. Click “OK” to close the “Calculated Field” dialog box and then click “Run” to run your query.