How To Use Calculation Fields In Microsoft Access

Once you've created table and queries in Microsoft Access you might want to use calculation fields in your data. A calculation field is a field derived from data in one or more existing table fields. However a calculation field cannot be added to a table, but it can be added to a query. You can then base any forms or reports on the query rather than the table. This article summarises how to create a calculation field in a query by describing two examples; a bonus calculation and an age calculation.

What is a calculation field?

Suppose you have an Access database with a table called TblStaff showing details of all staff such as name, post, salary and date of birth. You decide to award all staff a 5% bonus. You could add another field called BONUS to the table, and then manually type in all the bonuses. However you might make a mistake, and this is no fun if there are many staff records in the table. Also if you change your mind and decide to make the bonus 6% you have to do the whole thing again. A better way to do this is to use a calculation field.

A calculation field is an extra field which calculates a value from one or more existing field. However you cannot add a calculation field to an Access table. But you can add a calculation field to a query. So first we'll create and save a query called QryStaff using all the fields from the original staff table. If you run the QryStaff you will see all the original staff data just as if you're viewing the TblStaff. Now switch QryStaff back to query design view.

A first example calculation: Staff Bonus

We decide to give all staff a 5% bonus and want to add a calculation field to our query to show this. Our extra bonus field will show the calculation [SALARY]*.05 which is the original salary times 5%. In query design view, in the lower part of the display, select the top cell in first empty column to the right of the existing fields. In this cell type SALARY*0.05 and then click into the next cell down. Access will add the square brackets round the word "Salary" because it recognizes the name as an existing field in the query. Save the query.

Then run the query, and voila, you'll see the extra field showing the bonus value for each member of staff. To complete this exercise we want to a label "BONUS" to this new field. So return to query design view and click into the same cell with the calculation, and carefully move the insert point to the far left of the cell. Then type "BONUS:" without the quotes, but with the colon. Save the query and run it again - now you'll see the new label on the calculation field. Now you can close the query. If you change any of the salary figures in the original TblStaff and run the query QryStaff again, you'll see all the updated bonuses, as a query will always uses the latest data from the underlying table.

A second example calculation field: Staff age.

This calculation is not essential to understand a calculation field, but it's an interesting one anyway and is often used in calculations. If you have a date of birth field, e.g. DOB, then you can work out each person's age by subtracting DOB from today's date. In Access today's date is calculated by DATE() in the calculation field. So if we use DATE()-DOB for the calculation we'll get age. Dates in Access are actually stored as whole number (of days from 1 Jan 1900), so the result of our calculation will be each person's age in days! To calculate age in years we divide the calculation by 365.25. The .25 is to allow for a leap year.

So now the calculation looks like (DATE()-DOB)/365.25 in the calculation field. However this will give each person's age but with lots of decimal places after the number. In reality if a person is aged 20 and 8 months, their age is still 20. So we complete the calculation by using INT which shows only the whole number from the calculation. Lastly we add a label AGE. So the completed calculation looks like AGE:INT((DATE()-DOB)/365.25) without any extra spaces. Note there are two open brackets after INT.

To create this in our query QryStaff, open the query in design view, and choose the top cell in the next empty column in the lower part of the display. Now type in the complete calculation AGE:INT((DATE()-DOB)/365.25) and click one cell down. We do this to ensure Access detects the existing field names in the calculation and square brackets them, and also to check that we have typed the details correctly. If there are any typing mistakes Access will prompt you with an error message. Hopefully the typing is correct and there are no error messages. Save the query and run it. You should now see each member of staff's age. Changing any DOB in the original table and running the query again will result in the updated age.

Hopefully this article has given you an insight into how to create calculation fields in an Access query. If you then base any forms or reports on the query rather than the table, then the calculations will show in the form or report. Want to learn more about Access? A really effective way is to attend a training course. There are lots available and the best ones are hands on and can really help you boost your Access skills.