Back
Access

Access 2010: Design more maintainable apps with calculated columns

When I talk to developers about why they love Access, the most frequent response is always about fast they can build applications that track data. This saves time and money. Calculated columns are a new feature in Access 2010 that is designed to help end users and developers build applications faster, and are easier to maintain.

Let me start with a couple scenarios from the Access 2007 templates. In the Contacts database, there are two fairly complicated expressions that conditionally show either the name of the person or the company, depending on which is blank:

FileAs: IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]), IIf(IsNull([FirstName]),[LastName],[LastName] & “, ” & [FirstName]))

ContactName: IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]), IIf(IsNull([FirstName]),[LastName],[FirstName] & ” ” & [LastName]))

The Sales Pipeline database has a similar scenario with a different expression that calculates the value of the potential opportunity:

ForecastValue: [EstRevenue]*[Probability]

In these two scenarios it is highly likely that if you are going to use data from either of the tables, you will also need the calculated fields.

In the templates, we created a base query that did a SELECT * and included the expression columns. Unfortunately, I see many poorly designed databases in which an expression is repeated throughout queries, forms and reports. When the expression requires modification, the database owner must look for the many places it may have been used so all the occurrences can be changed.  With calculated fields, there is only one place to edit, so maintenance is faster and easier.

The main benefit of calculated columns is encapsulation, which leads to cleaner database design. It allows database developers to define the most common fields at the table level. This makes applications easier to maintain as inevitable bugs creep up in expressions (at least if you write expressions like I do :-) ). The developer can easily track the expression back to one place—the table.

Note: data macros are another example of a feature that allows developers to encapsulate logic in the table rather than behind form events.

Calculated columns are easy to create. Open the table in browse view. From the ribbon, select Table Tools | Fields | Add & Delete | More Fields | Calculated Field or choose the Calculated field type in table design view. Either way, the expression builder will help create the expression.

image

Another benefit calculated columns provide is consistency with SharePoint. Microsoft has made a big investment to make SharePoint a platform for collaboration and data applications. Calculated columns were introduced in SharePoint 2003 as a “calc and store†model, where values are only recalculated when dependencies change. This model is a performance optimization that makes it faster for the server to query against these values, especially when users sort and filter on the calculated field.

The calc and store model cannot be used for volatile expressions such as Date() and Now() because the stored result would only be accurate at the moment it is initially calculated. Functions that reference outside the row of the table, such as DSum, DCount and DLookup are also prohibited because it would be costly from a performance perspective to detect when dependencies have changed. These types of calculated fields are better left in base queries.

Calculated fields can save development time, reduce errors, and increase performance.  This results in better-designed applications.

Enjoy!