Use calculated fields in Access queries and reports

Calculated fields in Access help you design queries and reports based on values that are subject to change over time. Find out how using calculated fields can enhance the information stored in your tables.

This week, I'll explain what calculated fields are, discuss the benefits of using calculated fields, and show you tips for using calculated reports in queries that you can use as the basis for new reports. I'll use Access 2000 for my examples, but the approach is similar in Access 97 and 2002.

Defining a calculated fieldOn your first day of Access training, you become familiar with the term "field" when you set up your first table. You must specify the kinds of fields you want to use to store your information. Your options include text fields, date fields, numeric fields, and logical fields.

Of course, you may from time to time update the information stored in those fields. But for the most part, you can think of the fields you set up to store information in a table record as permanent fields. They store the raw data on which you base your reports.

On the other hand, you use a calculated field to generate values that are subject to change over time. A calculated field typically depends in some part on the entries in your permanent fields; it generates the information you require on an as-needed basis—not necessarily information you need to store on a permanent basis.

Put another way, a calculated field returns a value that isn't saved on your hard disk. The calculated field's result is computed only when you generate it at run-time—at the moment when you execute a query or run a report based on a query.

As you've probably guessed by now, a calculated field consists of a valid Access expression. Let's take a look at a couple of common calculated fields.

Calculating length of serviceHere's a classic example that illustrates the value of a calculated field. Suppose you maintain a table named Employees in which you have established the following permanent fields, among others:

Employee Name

Date Of Hire

Now suppose your CEO requests a report that shows the names of current employees and the number of years each employee has been with the company as of a given date.

The problem is that your table doesn't contain a Length Of Service field. One solution would be to modify the table structure, add a new field, and then compute and enter the length-of-service values for each employee. However, that approach is tedious, and those values must be updated on a frequent basis. So what's the bottom line? There's no reason to make employee lengths of service a permanent part of your table.

Here's where the calculated field comes in handy. Just create a query that generates the length of service for each employee dynamically, when the information is needed. Then, use the results of that query as the basis for the report you print.

I'll use the sample records shown in Figure A to generate the report the CEO requested.

Figure A

Create a query with a calculated field that returns the length of service (in years) for each employee.

To create a query that includes a calculated field called Length Of Service, follow these steps:

Create the query by clicking on Queries in the Objects pane and double-clicking Create Query In Design View.

When the Show Table dialog box appears, select your table (in this case, Employees) and click the Add button. Then click Close to dismiss the Show Table dialog box.

Double-click on the Employee Name field to add it to first column in the query grid.

Click in the Field row of the second column in the query grid, and you're ready to define your calculated field.

Anatomy of a calculated field in the query gridIn the query grid, the calculated field consists of three parts: the name you'll give to the calculated field, a colon, and then the expression that generates the result of your calculated field. In this case, I named the calculated field Length Of Service and made the length-of-service calculation as of Jan. 1, 2003. To do so, enter:Length of Service: (DateValue("1/1/2003")-[Date of Hire])/365.25

The first part of the calculated field's expression, (DateValue("1/1/2003")-[Date of Hire]), subtracts the date of hire that's stored in the table from a known date, Jan. 1, 2003. That subtraction yields the number of days between the two dates. Then, I divided that number by 365.25 to arrive at the approximate number of years of service. (In your date calculations, you may want to use the Round function to convert the result of the division to the nearest whole number.)

Figure B shows what our calculated field looks like on the query grid. To execute the query, click the Run tool on the Query Design toolbar (it's the one that looks like a red exclamation point). Figure C shows the results of this sample calculated field.

Figure B

The query grid lets you specify the name you want Access to use when it displays the results of your calculated field.

Figure C

Here are the results of the calculated fields after I executed the query.

Other expressions for your calculated fieldsHere's another quick example of how calculated fields can come in handy. Suppose your table contains a column named Sale Amount, and you want to calculate the amount of commission earned on each sale. If the commissions were always the same, you'd simply hard code the commission rate as part of the expression. For instance, if the standard commission were 10 percent, the expression for your calculated field would look like this:Commission: [Sale Amount]*.1

Now let's look at what happens if you pay 10 percent commission for sales below $500, and 15 percent commission for sales of $500 or more. In that case, you'd use the immediate-if function in the expression you enter for your calculated field. Your expression would look like this:Commission: Iif([Sale Amount]<500,[Sale Amount]*.1,[Sale Amount]*.15)

The immediate-if function first evaluates the expression [Sale Amount]<500. If that expression evaluates to true, the function returns the value calculated by [Sale Amount]*.1, or a 10 percent commission. When the value in the Sale Amount field is 500 or greater, the function returns the [Sale Amount]*.15, or a 15 percent commission.

Getting calculated fields in reportsI've found that it's far easier to add a calculated field to a query grid than it is to add one to a report. So when your report needs to include calculated values based on entries in a table, create a query, enter the calculated field as part of the query, and use that query as the basis for your report. Figure D shows what the Report Wizard looks like when I select this sample query as the basis for a new report.

Figure D

Enter your calculated field as part of the query. When you launch the report wizard, use the query for the basis of the report, not the original table.

Only the beginningI've just scratched the surface of what you can do with calculated fields. But now that you know how they work, I hope you'll be able to create powerful reports-based queries that contain calculated fields.Calculate this
To comment on this tip, or to share your own advice for working with calculated fields, please post a comment or write to Jeff.