Microsoft Access Queries – Calculated Fields

Calculated fields 

There are going to be times when calculations need to be done in Access.  They are done in a very similar way to Excel, the main difference being that rather than refering to cell references field names are used instead.


To work out the stock value, you need to multiply the units in stock by the unit price.  This information needs to be displayed in the next column and you need to provide a column heading.  To set this up a formula needs to be entered in the the next blank field.

The entry needs to be :

Stock Value:[unit price]*[units in stock] 

Stock Value is the alias – what that field will then be known as.

[unit price]*[units in stock] is the formula.  Notice that it does not need to start with an equals sign as it would in Excel.  The individual field names need to be enclosed in square brackets. If there were no spaces in the field names you would not need to type in the square brackets they would be inserted automatically.

The formula can be entered directly into the field name.  Alternatively you can zoom in on the field by clicking on Shift f2.

The formulae that are entered can be as complex as you wish them to be.  The structure of the functions that can be used do not have to be learnt, the expression builder can be used to to see the functions available and the structure of each layout.  Save the query before selecting the expression builder

The Expression Builder can be activated by  CTRL-F2.  Then the selection needs to be made from the first column depending on what is being worked upon.




When calculations are to be entered then functions need to be selected in the first column.  This displays the different type of functions in the middle column.  By selecting the category required then the functions are displayed in the third column.

By clicking on a function in the third column, the format of the function is displayed at the bottom of the window.  By double clicking on the function, the function is inserted into the formula at the cursor position.

Totals in Queries 

A new feature of Access 2007 is the facility of adding a totals row on dynasets, allowing more Excel like features.   This is done in the datasheet view by clicking on the totals button in the records group of the Home ribbon.

This displays a totals row at the bottom of the window.  When you click on the cell under particular fields the drop down options will be dependent on the type of data in the field.  On text fields the two options are none or count.  On numerical data there are more statistical functions to choose from.