MS Access for the Business Environment: Create a Calculated Field with the Expression Builder

Thursday Jun 5th 2003 by William Pearson

Extend the power of your database with calculated fields. Author Bill Pearson guides a step-by-step tutorial for building calculated fields using the Expression Builder.

About the Series ...

This is the first article of the series, MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. The series is designed to provide guidance in the practical application of data and database concepts to meet specific needs in the business world. While the majority of the procedures I demonstrate will be undertaken with Access 2002, many of the concepts that we expose in the series will apply to numerous versions of MS Access.

As we progress through the series, we will build upon previous lessons and the concepts we have introduced therein, in many cases. However, one of my objectives is to make each lesson as "standalone" as possible, in an attempt to prevent cases where one cannot complete a given lesson without having components or objects that were created in previous lessons. This should make it easier for "casual" visitors to join us with any lesson, and still successfully complete that session, given an existing understanding of concepts and principles that we have accumulated up to that point.

To get the most out of the MS Access for the Business Environment series, you will need to have installed MS Access 2002, although, again, the majority of the concepts we take up will certainly apply to earlier versions. Make sure that the PC that you plan to use meets the system requirements, including hardware and operating systems, of MS Access, as well.

Introduction to this Tutorial

This tutorial will introduce calculated fields in MS Access. We can use calculated fields both in queries and tables; here, we will focus upon their creation and use in general. This lesson will include:

  • A brief introduction to calculated fields;
  • An introduction to the MS Access Expression Builder;
  • A practical walkthrough of the process of creating a calculated field.

Let's begin by discussing the reasons we might want to create a calculated field and how such a component can help us to extend the power of our Access databases.

Introduction to Calculated Fields

Many occasions arise in the design of a database where calculated fields are useful. The most common scenario surrounds fields that contain derived data--that is, fields that are made up of data that already exists elsewhere in the database. An example might be the net price of a product that an organization sells, after discounts. A table under consideration might house the retail price and the discount information, but the net price, or retail price less the discount, is a derived amount. We can more efficiently meet a business need for the net price by installing a calculated field that would provide the information as needed.

Using the Expression Builder to Create Calculated Fields

Let's open the Northwind sample database, and begin by creating a rudimentary calculated field. We will use the Expression Builder, which can often assist us in the creation of expressions when we are learning about expressions, and perhaps Access, in general. We could also enter the expressions directly, if we were certain of the syntax required and how to add it, but for now, we will use the Expression Builder.

We will start Access and proceed, taking the following steps:

1. Go to the Start button on the PC, and then navigate to the Microsoft Access icon, as shown in Illustration 1.

Illustration 1: The Microsoft Access Icon from the Start Menu

2. Click the icon to start Access.

Access opens, and, depending upon whether you have opened it before, may display an initial dialog. If so, close it.

3. Select File -> Open from the top menu, and navigate to the Northwind sample database (the file might also be accessed from the Open a File menu atop the task pane, if it has not been disabled previously, at the right side of the main window in Access 2002.)

4. Select Northwind.mdb, closing the splash screen if it appears.

The main switchboard appears as shown in Illustration 2.

Illustration 2: Inside Access, Northwind Main Switchboard

5. Click the Display Database Window, or get there by an alternative approach.

We arrive at the Database Window, which appears as depicted in Illustration 3.

Illustration 3: Inside Access, Northwind Main Switchboard

6. Double-click the Order Details table to open it.

The Order Details table opens, as partially shown in Illustration 4.

Illustration 4: The Order Details Table (Partial View)

We see that the information we want to drive and present in our example, Net Price, for each of the line items, is composed of data currently existing in two separate fields, Unit Price and Discount, as we have indicated. Now that we have a precise understanding of the source fields involved, let's close the Order Details table.

7. Close the Order Details table.

We are returned to the Database Window.

8. Click the Queries icon in the left pane.

9. Click the New button that appears in the menu, as shown in Illustration 5 below.

Illustration 5: Select New to Create a New Query

The New Query dialog appears, as shown in Illustration 6.

Illustration 6: The New Query Dialog

10.         Select Design View to create a query without using a wizard.

11.         Click OK.

The Show Table dialog appears atop the Select Query dialog, where our actual query construction will take place.

12.         Select Order Details from the tables list.

13.         Click OK.

The Select Query dialog is populated with our selected table, as shown in Illustration 7.

Illustration 7: The Populated Select Query Dialog (Composite View)

We are now prepared to build the expression that will populate our calculated field.

14.         Right-click the top cell in the column on the immediate left of the Select Query dialog.

15.         Select Build from the context menu that appears, as shown in Illustration 8.

Illustration 8: Initializing the Expression Builder

The Expression Builder appears.

16.         Double-click the Tables folder within the left-most pane of the Expression Builder dialog.

17.         Select Order Details in the expanded selection underneath the Tables folder, as shown in Illustration 9.

Illustration 9: Making Selections within the Expression Builder

The fields of the Order Details table appear in the middle pane of the dialog. The elements that appear in this pane can be pasted into an expression.

18.         Click the Unit Price field once to select it, and then click the Paste button in the toolbar immediately above.

The Unit Price field appears in the Expression Box atop the Expression Builder dialog, as shown in Illustration 10.

Illustration 10: The Newly Selected Field in the Expression Box

19.         Click (once) the minus ("-") symbol in the common operators set in the toolbar.

The "-" operator appears in the Expression Box, to the right of the Unit Price field.

20.         Click the left parentheses ("(") symbol in the common operators set in the toolbar.

21.         Ensuring that Unit Price is highlighted, click the Paste button again to add a second Unit Price--this time to the right of the "(" symbol, to the Expression Box).

22.         Click the multiplication operator ("*") button in the toolbar to add a "*" symbol to the expression.

23.         Highlight the Discount field in the middle pane.

24.         Click the Paste button once again to add the Discount field to the expression.

25.         Conclude by clicking the right parentheses (")") symbol to end the expression with a right parentheses.

The expression we have created in the Expression Box now appears as shown in Illustration 11.

Illustration 11: Our Newly Assembled Expression in the Expression Box

26.         Click OK.

The Select Query dialog appears as shown in Illustration 12.

Illustration 12: Partial View of the Select Query Dialog, with New Expression Shown (compressed)

The expression we have created is in place, although it may be compressed, as shown. We can always reopen the Expression Builder to review or edit the expression by right-clicking and selecting Build ... from the context menu, should the need arise.

27.         Right-click the field containing the expression we have created.

28.         Select Build... from the context menu that appears.

29.         Carefully type the caption Net Price: over the existing Expr1:, changing nothing else in the expression.

The Expression Box should now appear as shown in Illustration 13.

Illustration 13: The Expression, after Modification of Caption

30.         Click OK.

31.         Select View --> Datasheet View from the top menu.

(The Datasheet View icon at the left of the Access toolbar, just below the File menu item (as shown in Illustration 14) is an alternative means of achieving the same action.)

Illustration 14: Icon Selection Option for Selecting Datasheet View

The view changes to display the results of the calculated field (Illustration 15) for the entire column. A quick check of the math indicates that the correct Net Price is reflected.

Illustration 15: Datasheet View of the Calculated Field

32.         If desired, Select File --> Save as and save the query, with a meaningful name and location.

Our options abound at this point; we can convert the query to a Make Table query, or insert the calculation into an existing table or report in other ways. MS Access offers us a rich set of options in the creation and use of calculated fields.

Conclusion ...

With this tutorial article, Create a Calculated Field with the Expression Builder, we have begun the new MS Access for the Business Environment series. Our objective in this lesson was to introduce calculated fields in MS Access, focusing on the practical steps of their creation in a simple business scenario. We briefly explored the concept of calculated fields, introduced the MS Access Expression Builder as a straightforward means of creating and editing them, and provided a practical walkthrough of the process of creating a sample Net Price calculated field.

» See All Articles by Columnist William E. Pearson, III

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved