I am going to talk about Microsoft Access calculated query fields.
What is a calculated query? If you have two or more value fields in your query you
can calculate a third field based on the other fields. This field is known as an MS Access calculated field.
For example if you wanted to find out the value of a customer who had bought
goods. It certainly is not practical to go in and manually calculate each line.
You would have the unit price and quantity, but would like to find out the total value.
So how can we do this?
I am going to use the sample database that comes with Access for this tutorial. Load
up the Northwind database. Usually the file will be nwind.mdb
Open the query designer.
Select the following tables
• Order Details
Access will link them in the query designer.
Double click or drag the following fields to the query grid.
From the Customers table add in CompanyName.
From the Orders table add in OrderDate.
From the Order Details table add in UnitPrice and Quantity.
In the query designer go to a blank column and on the field line type in the following:
When you click away from the grid notice how Access adds the square brackets around the UnitPrice and Quantity fields.
This is ok.
At this point you should make sure you have put a tick in the ‘Show’ box.
You have now created an MS Access calculated field.
Now run the query.
Access has now totalled the column called ‘TotalValue’ a result of multiplying the
Quantity by the Unit Price.
Because I am in the UK my settings are set to the UK currency of pounds which is
why there is a dollar symbol in UnitPrice and a pound symbol in TotalValue. Don’t
worry about this, it can easily be changed. – the point here is the calculation.
If you have hundreds or thousands of records an MS Access calculated field is a great timesaver. Imagine having to manually go in and calculate each line?
You could now base your form or report around this query.
I hope this has given you an insight into the power of MS Access calculated queries. It can really help to automate calculations within a system.
Access makes tasks like this so easy.