If you've used previous versions of Microsoft Access you might be aware that you can only create calculation fields in queries and not in tables. The query can then be used in place of the table to create forms and reports which show the values of calculation fields. For example if you have a table with a list of staff, including dates of birth, you can create a query to show the same table and an extra field to show staff ages, calculated from the dates of birth. In Access 2010 that's all changed and now youcan create calculation fields in a table. This article describes how to do this with examples and summarises some key benefits.

Where to see the new data type

When you create a new table in Access 2010 or open an existing one, you'll find that when you add a new field there is an extra data type available. This new data type is for calculations. This can best be seen if you switch to table design view. You'll see the familiar columns for field names and data types. If you click the pop down in any one of the data type entries, you'll see "calculated" lower down in the list, second from the bottom.

Creating a new calculated field

Suppose you've created and saved an Access 2010 table containing a list of several products, with the headings Ref, name, location, and cost. You want to create a new field to show the vat payable for each product. The vat will be the cost times 0.2. To do this switch to table design view and under the list of the current fields create a new field name VAT. Then in Data Type select "calculated". At this points Access launches the expression builder to help you create the calculation. In the lower part of the builder look in the first column and you'll see the current table selected.

In the second column you'll see all the fields in the table. In this second column double click on the Cost field, and you'll see the expression now contains [COST]. Access has added the square brackets to identify the field in the expression. Alternately you can type the open square bracket yourself, then the field name, then the close square bracket. Then type an asterisk followed by 0.2 and click OK to finish. The asterisk symbol is used to multiply the value in the Cost field by 0.2. The Builder closes and you'll see the expression [COST]*0.2 in the field properties in the lower part of the view. That's the calculation field finished. To view the results, save the table and switch back to regular table view. All the records now show the calculated VAT values.

Viewing a calculated field

Because calculated fields are derived data, they cannot be edited directly and are therefore read only. You can click on calculated values in the table to select them, but you'll find you cannot change them. If you add new data to the table or edit existing data used in the calculation, such as cost data in our example, the VAT field will automatically update to show values based on the latest data.

Access 2010 rules for calculated fields

Access 2010 does limit you in certain respects when you create a calculation field. You can only include fields in the current table rather than from another table or query. And once you've saved a calculation field type, you cannot then change it to a different data type. Similarly if you create and save a field of a different data type, you cannot then change that field to a calculation type. So a calculation field is always created as a brand new field. By the way you can include one calculation field in another calculation field if you want to.

Benefits of using calculation fields in tables

Calculation fields can be used based on different data types. So for example a numerical calculation might show total cost calculated from number sold multiplied by unit cost for a list of sales items. A date calculation might show Age calculated from Date Of Birth. A text calculation might show employees full names (first name and surname) calculated by combining existing firstname and surname fields. Another text calculation might show a custom primary key which combines text and an autonumber field, so the custom field autonumbers.

One drawback of using calculation fields in an Access 2010 table is that the table will not open in previous Access versions, so only use calculation fields if the database will only be used in Access 2010.

Interested in learning more about Access 2010? Attending a training course can be an excellent way to take your skills forward and the best courses are flexible and hands on.