Using Calculated Columns in Table Design in Access 2010

Although Access has always supported calculated columns in queries, sometimes you may perform calculations in tables so that you can use the calculated fields in any object based on the table. In addition, SharePoint supports calculations in its tables; because of the integration between Access and SharePoint, Access must support the same sort of calculations in Access tables. Calculated columns in tables in Access 2010 provide a new feature that is designed to help end-users and developers quickly build applications that are easier to maintain.

For example, a table that contains book titles─each row in the table contains information about a single book, including its title, information about the author, and so on. Although the table stores the author first name and last name, it is likely that in any situation where you might be using book information, you must also have the author's full name. It would be useful if the table itself could contain this information. In addition, you might want to sort the table that is based on the book titles. Because many book titles begin with "A", "An", or "The", you most likely want to ignore these and sort on the book title without them. It might be useful to use a calculated column for the author’s full name, and for the sortable book title. This Visual How To shows how to create both of these calculated columns.

Consider the alternatives─without calculated columns in tables, end-users may create these same calculations in multiple locations throughout their applications. Even developers may need to define similar expressions in multiple locations throughout their applications. Repetitive creation of similar expressions leads to errors, of course. Placing the expression at the lowest possible level─in the table itself─reduces some of these sorts of problems.

To investigate calculated columns in tables in Access 2010, start by creating a new table in a database that is named Book. In general, to create a calculated field, you must create a new column and specify its type as Calculated (see Figure 1). After you have created the column, Access presents the Expression Builder, as shown in Figure 2. (Although you may have avoided the Expression Builder for other tasks in Access, because you may find it easier to write the code yourself by using the Expression Builder for the purposes of creating a calculated field actually makes the task easier.)

Figure 1 shows how to select the Calculated field type.

Figure 2 shows how to Use the Expression Builder to help create a calculated field.

For example, to show how to create a FullName field, start by creating three Text fields in the Book table that is named FirstName, MiddleInitial, and LastName. Save the table, and then create a new calculated column named FullName, and create the following expression in the Expression Builder.

[FirstName] & " " & 
([MiddleInitial] + " ") & 
[LastName]

(The Expression Builder relies on the fields in the saved version of the table--therefore, you must save the table before you try to use the Expression Builder to create a calculated field.)Be aware that this expression uses null propagation─that is, if MiddleInitial is not null , the expression includes the MiddleInitial field followed by a space. If MiddleInitial is null , using the + operator to concatenate the field and a space causes the whole expression to be null . This way, if MiddleInitial is null , you will not have an additional space.Add some rows to the table, inserting names with and without middle initials. The FullName field should accurately reflect the full name of the book author.

Creating a More Complex Calculation

Creating a sortable title field requires more effort, although it uses the same concepts as the previous example. In this case, create a Text column named Title and then save the table. Add a new calculated column named SortableTitle, and create the following expression using the Expression Builder.

IIf(Left([Title],2)="A ",
 Mid([Title],3,Len([Title])-2),
 [Title])

This expression compares the left two characters of the book title with "A ", strips the "A " from the beginning of the title if it is necessary. The expression uses the original title if the first characters do not match "A ".

Open the table, enter a Title value starting with "A ", and you will see the calculated value in the SortableTitle field.

To extend the calculation to include books that begin with "An ", modify the expression by clicking the Build button to the right side of the Expression property, as shown in Figure 3.

Figure 3 shows how to Click the Builder button to modify the expression.

In the Expression Builder, modify the existing expression so that it resembles the following code example.

IIf(Left([Title],2)="A ",
 Mid([Title],3,Len([Title])-2),
 IIf(Left([Title],3)="An ",
  Mid([Title],4,Len([Title])-3),
  [Title]))

Add a book to the table that begins with "An ", and verify that the expression works. Finally, modify the expression one more time, adding support for books that begin with "The ".

IIf(Left([Title],2)="A ",
 Mid([Title],3,Len([Title])-2),
 IIf(Left([Title],3)="An ",
  Mid([Title],4,Len([Title])-3),
  IIf(Left([Title],4)="The ",
   Mid([Title],5,Len([Title])-4),
   [Title])))

Add a book title that begins with "The ", and verify that the calculated field works as expected.

There are those who would argue that adding calculated fields in a table violates rules of normalization, and in some cases, they are right. In some cases, it is worth breaking the rules: If you know that you will need the calculation in every object based on the table, if you know that the expression cannot change over time, or if having the calculation in the table would make your data clearer, then use the feature. You can always, as in Access 2003 or Access 2007, include calculations in queries, also. Doing so requires you to base all forms and reports on the query, instead of on the table─end-users may miss this important point, so if end-users are creating their own forms and reports, it is best to consider using calculated fields in tables.

Be aware that calculated fields cannot call user-defined functions, only built-in functions. In addition, you must supply all parameters for methods that you call, even if the parameters are optional.

If you use calculated fields, be aware that earlier versions of Access will be unable to open the table─verify that you will need the table only in Access 2010 before you use this new feature.

Length: 10:51

• Backward Compatibility Between Access 2010 and Access 2007

• Access Developer Center

• Access Blog

About the Author

Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP.NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).