Page 3 of 10PREVNEXT

Queries IV: Use functions and calculated fields in your queries

Creating a calculated field


The aggregate functions have a couple limitations — they only work on a single field, and you're limited to the functions that Access provides. So what if you need something different? For example, what if you need to raise prices by 10 percent? You can use a calculated field. A calculated field displays the results of an expression instead of the data in your tables.

Because they use expressions, calculated fields are pretty versatile. For example, they can combine first and last names, manipulate dates and times, or perform complex math operations.

Let's look at the syntax of a calculated field.

Callout 1 With your query open in Design View, select a blank field in the Design Grid and enter a name, like you would for any field in your database. Put a colon at the end of the name.
Callout 2 Enter your equation or expression after the colon. The picture shows a typical example. The calculated field multiplies the values in the UnitPrice field by 1.1, which increases the value by 10 percent, and then displays the result as a calculated field called "NewPrice." That calculated field doesn't exist in your database tables. It only appears when you run your query.
Page 3 of 10PREVNEXT