How To Create A Calculated Field In Access

Summary:

This blog focuses on different ways to create calculated field in Access. 1# create calculated field in Access using expression builder; 2# create a calculated field in Access query. Apart from this we will discuss different aspects related to calculated field in Access.

What's The Use Of Calculated Field In Access?

A calculated field helps in easy calculation of the values from different fields within the table and also from the Access built-in function. After that, it displays the result of the calculation.

Fix Microsoft Access Issues
Run a database Scan with Stellar Access Database Repair Tool to repair inconsistent Access database. After the scan is complete, the repair process will restore the database to its normal state fixing all the errors.

By clicking the button above and installing Stellar Repair for Access (14.8 MB, $79 USD), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

How To Create A Calculated Field In Access?

1# Create Calculated Field In Access Using Expression Builder

Here is an example to show you how to create calculated field in Access using expression builder.

In this example, we are dividing the population of the country through the land area. As a result of which we will get the population density (which means it will calculate the number of people lives in per square kilometer of that country).

Step 1: Select The Calculated Field Option

At first, open your table in the datasheet view. After that scroll down and go to the right side field.
Hit the field heading having the text "Click to Add" and then from the appearing drop-down menu choose the Calculated Field. After that choose the data type which you want to assign for your output.

Step 2: Enter Expression In The Expression Builder

Now you need to launch the Expression Builder. For this you need to select the Fields>More Fields> Calculated Field.
To build the calculations either you can make use of the expression builder or you can type the expression that you need.
As we have to divide the Population field with the Area field. So, you will type [Population]/[Area]. This expression will calculate people's population in per square kilometer.
Hit the OK button once after entering up the expression.

Create Calculated Field In Access Using Expression Builder 2

Step 3: Complete Calculated Field Leftover Work

You will see that the field header will get highlighted. In this you have to assign name to your field.

Create Calculated Field In Access Using Expression Builder 3

Your calculated field is now completed so you can expand the field's width for showing up the values.

Create Calculated Field In Access Using Expression Builder 4

Step 4: Do Modifications If Required

Suppose, the calculated field in Access is showing results with too many decimal places then you can make easy adjustment in this.

For this you need to choose the field first. After that go to the Fields tab and then from the formatting group choose the rounding icon.

If you need to reduce the decimal number places then hit the icon present on right. Or else to increase it just tap over the left icon.

Create Calculated Field In Access Using Expression Builder 5

Step 5: Modify The Expression

Open the Expression Builder to make any changes in the expression.
For this, choose the calculated field which you need to change. After that hit the modify expression option from the fields tab.

Create Calculated Field In Access Using Expression Builder 6

2# Create A Calculated Field In Access Query

Another method that you can try is to create calculated fields in Access query.

A calculated field is the result of each individual record calculation as every single calculation only includes fields in the exact record.

Steps to create calculated fields in Access query

Open your MS Access application.
Create the table or you can use an already existing table.

Create Calculated Fields in Access query 1

Now you need to create the Query.
For creating up the Query, you need to go to the Create tab first. After that go to the Queries group and make a click on the Query Design icon.

Create Calculated Fields in Access query 2

On the right side there is one Add Table window option. So choose the table which you need to calculate.
Hit "Add Selected Tables" option present on the bottom section of Add Table window.
You can see that the table will start appearing on the upper section of the Window.
On the bottom side of window there is a Design Grid icon.
Make use of the design grid tool to create the Query by putting fields from your selected table in the upper to the lower section of the window.
Tap to the arrow sign present in the Field row and then select the fields for each of the column which you wish to include in your Access Query.
Here we are considering an example that we have to calculate the birth rates total in last 3 years i.e 2018, 2019, and 2020.
In the last column, you need to add the field; 'Total Number of Births in Three Years'

Create Calculated Fields in Access query 3

Make a right click on the field 'Total Number of Births in Three Years'.
From the shortcut menu tap to the Zoom option to open it.
In the opened dialog box of Zoom dialog box, you need to watch out the field text within it.

Create Calculated Fields in Access query 4

According to the calculations you want to do just write the formula for it.
e.g – Total counts of Births in 3 Years: [2018] +[2019] +[2020].
Hit the OK button.
Hit the Run option in the left side of Results group.
You will get the output of the calculation.

Create Calculated Fields in Access query 5

How To Add A Total Row In Calculated Field?

Thetotals row will adds the complete column of numbers, it is same like a receipt. The calculated sum will appears in the separate row which is placed at your table's bottom .

Suppose, if you need to add totals row in your calculated field then using the below given steps you can easily perform this task.

Go to the Home tab and then from the Records group hit the Totals command.
In your Access table just go to the last row by scrolling down.

create a totals row 1

For total rows, search for your desired field and then choose the 2^nd empty cell present just below the last record of that field.
Locate the desired field for the totals row, and then select the second empty cell below the last record for that field. When a drop-down arrow appears, click on it.

Add A Total Row In Calculated Field 2

Choose the function which you need to do on the field data. For example, you will have to select the Sum to add entire values of the calculated field.

Add A Total Row In Calculated Field 3

Now on your Access table totals row will start appearing.

Add A Total Row In Calculated Field 4

Go With The Recommended Option:

To repair corrupted MS Access file, the very first solution which I want to recommend you all to use is Access Repair And Recovery tool. It's advanced troubleshooting solutions for all your MS Access Database issue. The tool is a well-suited option to repair corrupted/damaged .accdb and .mdb files. Besides this, the tool can also restore each and every data like reports, tables, queries, macros, forms, etc.

* By clicking the Download button above and installing Stellar Repair for Access (14.8 MB, $79 USD), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

With this tool, one can easily fix any type of error or any corruption issue that occurred in Access Database. Some best features of this software include:

repair corrupt ACCDB and MDB files.
successfully repair Tables, Queries, Reports, Forms, & Indexes.
retrieve password-protected forms and modules.
support restoration of deleted records to a new database.
recovers Macros, Modules, Relational Databases, and other objects.
Compatibility with MS Access 2016, 2013, 2010, and all previous versions.

Steps To Repair Corrupted MS Access Database

Wrap Up:

Now you can easily create calculated field in Access. If meanwhile performing the above trick you are getting any other issue then let's use know by putting a comment in your social media Facebook and Twitter handle.

tip Still having issues? Fix them with Stellar Repair for Access:

This software ensures seamless repair & recovery of ACCDB & MDB database and restores all objects including tables, reports, queries, records, forms, and indexes along with modules, macros, etc. Fix Microsoft Access issues now in 3 easy steps:

Download Stellar Access Database Repair Tool rated Great on Cnet.
Click Browse and Search option to locate corrupt Access database.
Click Repair button to repair & preview the database objects.

Pearson Willey is a website content writer and long-form content planner. Besides this, he is also an avid reader. Thus he knows very well how to write an engaging content for readers. Writing is like a growing edge for him. He loves exploring his knowledge on MS Access & sharing tech blogs.