Calculated Field In Access Query – Work with Dates in Access Query

Last updated on October 8th, 2023 at 02:49 pm

A calculated field in Access Query is used to generate new information. It does not affect the information already in the table. Instead, it allows the user to generate additional information by doing calculations.

For instance, what if you had a database table containing the date of birth of all the students in a school, and you wished to find the age of each child? This would be quite easy to do using a calculated field. We demonstrate this example below.

Are you new to databases? Visit our article on database terminologies to learn many different terms associated with databases. 

How to find a person’s age from their date of birth using a calculated field in Access query

Even if you know how to do calculations in database queries, finding a student’s age from date can seem like a tall order. In this article, we will help you to create a calculated field in an Access query in a few easy steps. For our query, we will show the name, age, and age of all the students in the class. Here are the steps:

1. First, go to the Create toolbar and click query design.
2. Choose the table you will use for your query.
3. For our query, we will use the BioData table below.
4. In the field section of your query design, choose each field you will use in your table.
5. For the calculated field, which we will call Age. Type Age: Date()-[DateOfBirth].
6. This query will show the number of days since a person was born.
7. To change this to your actual age in years, divide by 365.5. Please note, because of BUDMAS rule, you need to put brackets around the first two values to ensure subtraction will be done first. As such, your new formula will look like this: Age: (Date()-[DateOfBirth])/365.5.
8. If you run this query, you will see the age and days displayed as a real number like this 16.5362517099863.
9. Finally, to display this as years only, set the value to integer by adding the int keyword. Be sure to enclose the entire formula in brackets like this Age: int((Date()-[DateOfBirth])/365.5).

That’s it! You just calculated the ages of all the students. Run this query to see the result. It should look like the one in the table below.

Do you wish to learn more about doing calculations in databases? Visit setting up an update query in Microsoft Access to learn how to create update queries.

Setting up a calculated field in an Access query, example 2

In this example, we will assume that we are using the information in the academic table shown below with the grades of all of the students in a class. We will create a query that lists all of the students’ academic information and adds a field to calculate the average grade for all of the students in the table.

To create a query that displays the students’ average, follow these steps:

1. On the Create toolbar, go to query design.
2. In the menu to the right, choose the table that has the information you wish to use for your query. In this instance, we will use the academic table.
3. Now choose the fields you wish to use in your query. In this instance we will use all of the fields in the academic table.
4. To create the Average field for the query type Average: then the formula to calculate the average. The formula should look like this: ([English]+[Mathematics]+[Science]+[Social Studies])/4.
5. That’s it. You just calculated the average grade for all of the students.

In the design view, your query should look like this:

Database reports and forms are very useful tools in databases. Visit creating a database report and form to learn how to work with them.

Before you go

We do our best to be as thorough as possible in the information we provide, however, if you have any questions or comments, be sure to leave them in the section provided below.