Creating Calculated Fields in Microsoft Access 365
In many Access applications you need values that are derived from existing data—for example, sales commission, line totals, or full names. Instead of calculating these values manually or exporting to Excel, you can add a calculated field to a query so that the result is computed every time the query runs.
In Microsoft Access 365, a calculated field in a query is created by typing an expression (sometimes called a formula) into the Field row of an empty column in Query Design View. The calculated field:
Is given a name (an alias) followed by a colon.
Uses existing fields, constants, and functions on the right-hand side of the expression.
Can be reused as the record source for other queries, forms, and reports.
This lesson walks through adding a calculated “Commission” field to a sales query and then introduces best practices for working with expressions and Access’s special characters.
Example Scenario: Calculating Commission
A sample Sales table contains the fields ID, Name, Price, and Date of Sale. Salespeople receive a 10% commission, and you want your query to show a new Commission column calculated as 10% of the Price for each row.
Instead of storing commission in the table, you will create a query that calculates Commission on the fly.
Step 1: Create a Query That Includes the Sales Table
Start by creating a new query in Design View and add the Sales table:
On the Create tab, choose Query Design.
In the Show Table dialog, select Sales and click Add, then click Close.
The Sales table now appears in the upper pane of the query design window, ready for you to add fields.
To display all the existing fields from the Sales table in the query output, double-click the asterisk (*) in the Sales table box. This tells Access to include all fields from Sales in the query datasheet.
Step 2: Add a Calculated Field Expression
To create the calculated Commission field, type an expression into the Field row of the first empty column. The general pattern is:
NewFieldName: expression
Note that you use a colon (:) after the field name, not an equal sign. For a 10% commission on the Price field:
Commission: [Price] * 0.10
When you run the query, the Commission column will display the calculated result for each row based on the value in [Price].
When you switch to Datasheet View, the query results include the new Commission field alongside the original Sales fields. Each row shows the calculated commission value based on the expression you defined.
Writing Expressions That Access Understands
When building expressions for calculated fields, Access must be able to recognize each value as text, date/time, number, or field name. This is done using special characters:
Special Characters
This type of data
Looks like this in an expression
Text
"Seattle"
Date
#1/1/2025#
Time
#1:00 AM#
Number
10
Field Name
[Price]
Examples:
Commission: [Price] * 0.10 – numeric multiplier, no quotes.
FullName: [LastName] & ", " & [FirstName] – text concatenation using field names.
RecentSales: IIf([DateOfSale] >= #1/1/2025#, "Recent","Older") – date in # signs.
Using the Access Zoom Box and Expression Builder
Long expressions are hard to read in the small cells of the query grid. Access provides two helpful tools:
Zoom Box – Place the cursor in the expression and press Shift+F2 to open a larger dialog where you can see and edit the entire expression. You can resize this dialog and change the font for readability.
Expression Builder – Right-click the Field row and choose Build… to open the Expression Builder. This tool lets you insert fields, functions, and operators without remembering all the syntax.
The Access Zoom Box (Shift+F2) enlarges your expression so you can read and edit long formulas more easily. You can adjust the font and resize the dialog for comfort.
Query Calculated Fields vs. Table Calculated Fields
In Access 2010 and later (including Access 365), you can also define a calculated field in a table. The expression is stored with the table, and the result is automatically maintained as other fields change.
Guidelines:
Use query-level calculated fields for most reporting and analysis—values are always up to date when the query runs.
Consider table-level calculated fields for relatively stable expressions such as a full name (First + Last) or a precomputed display value. These can be indexed and may improve performance for repeated filtering and sorting.
Be cautious about storing calculated values that duplicate other data (denormalization); use it only when you clearly understand the trade-offs.
