Access 2010 – Calculated Data Type

One of the long held principles of data base design has been to not store calculated values. In the past it has always made sense not to do this for a couple of reasons. Foremost is that, you have to ensure that you recalculate the value when the components of the calculation change. Second, when storage space was at a premium, it didn’t make sense to store a value that could be displayed with an expression in a control or a column in a query.

In today’s technology, storage space is not much of an issue. With storage space costing around $100 per Terabyte, developers do not have to scrimp to save space. So Microsoft provides for a new data type in Access 2010 that allows the user to store an expression that displays a value. This satisfies the primary reason against storing calculations.

With the Calculated data type, you store a formula or expression that is updated automatically when the components of the expression change. So there is no need for the developer to ensure that the value is updated. It is handled automatically by Access. Let us look at how the Calculated field data type works.

To illustrate Calculated fields I’m using the Order Details table in the Northwind Traders sample data base. I’m going to add a field to calculate the extended price and another to display a discounted price.  First open the table in Table Design mode. At the first blank line enter ExtendedPrice as the fieldname. And select Calculated as the data type (1).

Once you select Calculated as the data type, the expression builder opens  for you to build the expression to store in the field.

The expression builder opens with the table you are working in highlighted (1).  In the middle pane is a list of fields in that table (note: calculated fields can only use fields in the same table within its expression). So the first step is to select the first field in your expression. In this instance we will select the Quantity field.

The next step is to enter an operator to perform the calculation. First select Operators, from the Expression Elements (1). Then select Arithmetic from the Expression Categories (2). Finally, double click the asterisk (multiplication operator) from the Expression Values (3). The result is to add an asterisk to the expression (4).

You then go back to the Expression Elements and select the table again. Then double click the Unit Price field to add it to your expression.

Finally press OK and the expression will be added to the properties of the field (1).

Switch to Datasheet view of the table, saving the table if prompted. You will now see the Extended Price field added to the end of the table (1).

Notice that it is correctly calculating the extended price at 1400 (100*14). To test the field change the Quantity to 10 and you will see the field recalculated to 140 (1).

Similarly, you can add a discounted price by using the expression: [ExtendedPrice]*(1-[Discount]).

I must admit I am a bit ambivalent about this new Calculated data type. On the one hand, it certainly short cuts doing calculations. By putting the calculation in the table, you do not have to repeat the calculation wherever it is needed. Just add the field to your form, report or query and it is done for you.  Another advantage is compatibility with Sharepoint lists. But it will present problems, like some of the new data types introduced in Access 2007 (multi-value fields and Attachment fields) if you need to migrate your database to SQL Server, Oracle or the like. And it is an unnecessary shortcut. But, for the non developer, for the user making a database on their own for their own use, it does provide an advantage.