Calculating in Access Queries

Sooner or later you will want to perform calculations with your data. It is not good practice to store these calculations in tables, as part of the data itself, but to create them when needed.

The reason for this is quite logical. Providing the data itself contains everything necessary to perform the calculation, to store the calculated result would be a waste of space. Also, should the data change, the result would have to be recalculated and edited to remain correct.

You can create calculations in queries, reports and forms (but not in tables). Instead of making a table containing fields for, for example, Quantity, Unit Price, and Total, we build a table containing the data (Quantity and Unit Price) and a query showing these fields plus and additional calculated field which multiplies the two together. No calculations are stored, saving space in the database, but the results are instantly accessible by running the query which calculates them as they are needed.

This tutorial shows you how to perform simple calculations in a query. For more examples of calculations look at Calculating Dates in Access Queries and Calculating Totals in Access Queries.

When you perform a calculation you create and name a new field that contains the calculation you need. To begin, open a new query in design view and specify whatever criteria you need (if any). Then…
 

Name the New Field

In the Field row of a new column type the name of your new field followed by a colon. For example: New Field:
 

Enter the Calculation

Type the name(s) of the field(s) to be calculated using the appropriate mathematical operators (+, -, *, / etc.). For example if you wanted to multiply two fields together: [Field 1]*[Field 2]

Here are some examples…
 

Calculating with Different Fields

Total Cost: [Quantity]*[Unit Price]

In this example the query creates a new field called Total Cost and displays in it the value in the Quantity field multiplied by the value in the Unit Price field.

Calculating with Fields and Constants

Discount Price: [Unit Price]*0.9

In this example the query creates a new field called Discount Price and displays in it the value in the Unit Price field multiplied by 0.9.

Note: This is the same as multiplying by 90% or subtracting 10% but the query does not recognise the % symbol and we have to devise an alternative expression.
 

Formatting the New Field

When you are performing calculations, you may wish to see your results formatted in a particular way - currency for example. Normally field formatting is specified in a the design of a table, but as the field is being created by the query there is another way…

In the query's design view, right-click anywhere in the new field's column and choose Properties… from the shortcut menu (or click somewhere in the new field and choose View > Properties from the menu). This opens the Field Properties dialog box...

Click the format box, and then click the down-arrow to display a choice of formats. Choose from the list, in this example Currency is selected. Finally, click [X] to close the dialog box. 

When you run the query your new field will appear, containing the calculated values and formatted as you specified.
 

Dos and Don'ts for Calculated Fields

• In new field definition what goes to the left of the colon becomes the new field's name and what goes to the right becomes the calculation.
• Remember to follow the usual rules for naming fields (no illegal characters) and choose a name that does not already exist amongst those in the table(s) being calculated.
• Make sure you spell the names of the calculated fields correctly (a good reason for keeping field names short) and remember to enclose field names in square brackets.

Calculations are simple and straightforward. You just substitute field names for numbers. If you use a calculation in a Make-Table query the new table will include a new field containing the new calculated data.

©2000 Martin Green - www.fontstuff.com - martin@fontstuff.com - All rights reserved