How do I build calculated columns/fields in an Access query?

It typically is not enough to simply display data found in the database but often times it is necessary to manipulate it. This tutorial introduces the concept of calculated query fields. Consider for a moment the Employee database where there exists information on employee's total hours worked and their rate of pay. However, there is no field that contains an employees Yearly rate of pay. This information, though, can be calculated based on hours worked and rate of pay (hourly).

1. First create a query using the Jobs, Employee and Hours tables. First select the EmployeeID field for the first column.

   

2. Then right click on the second column and select Build.

   

3. The Expression Builder offers a friendly user interface for creating calculated fields. The first calculation will be one that will create a field called Employee Name where the First and Last name will be put together to create the employee's full name. The process of joining together two or more text fields is callec concatenation. To concatenate an employee first and last name simply enter the formula; Employee Name: [FName] + " " + [LName] and then click Ok.

   

4. Add the following fields as shown below: Jobs.JobID, Jobs.Title, Jobs.Rate and Hours.Year as shown below. We will now create a calculated field to compute an employee's total regular pay. Right click on seventh column and select Build.

   

5. Regular pay can be calculated by using the Regular field from the Hours table and Rate from the Jobs table. The formula for this step will be: Regular Pay: [Hours].[Regular]*[Jobs].[Rate] note the use of prefixing the field name with the table name ([Hours].[Regular] and [Jobs].[Rate]). Fully qualifying the field name in this manner will avoid potential conflicts when using multiple tables where a field with the same name exists in more than one table. Click Ok.

   

6. For the eighth column compute overtime pay using Overtime Pay: [Hours].[Overtime]*[Jobs].[Rate]*1.5
7. For the ninth column compute vacation pay using Vacation Pay: [Hours].[Vacation]*[Jobs].[Rate]
8. For the tenth column compute holiday pay using Holiday Pay: [Hours].[Holiday]*[Jobs].[Rate]
9. For the eleventh column compute sick pay using Sick Pay: [Hours].[Sick]*[Jobs].[Rate]
10. A calculated field can also utilize other calculated fields. For example, since we have already calculated values for Regular, Overtime, Vacation, Holiday and Sick Pay it would be useful to also calculate a Total. Total Pay can be calculated using this formula; Total Pay: [Regular Pay]+[Overtime Pay]+ [Vacation Pay]+[Holiday Pay]+[Sick Pay].

    

11. Save the query as qryCalculateEmployeeTotalPay.