Calculations can be carried out within database programs. Both FileMaker Pro and Microsoft Access provide calculation field types for this purpose; however, in Microsoft Access it is more usual to do calculations within Queries. Queries are linked to tables and they provide more detailed searching and sorting options.
To illustrate doing calculations within a database, a calculation of the total pay for each employee at Joe’s Pizza Palaces can be included.
In Microsoft Access:
Open the CREATE tab of the ribbon and select the QUERY DESIGN icon.
Click on ADD at the base of the SHOW TABLE dialogue box to add the EMPLOYEES table to the query then close the SHOW TABLE dialogue box.
Select all the fields in the table except the ID field and drag them to the first column of cells at the base of the Query window.
Click in the FIELD cell to the right of PAY RATE and enter the formula: Pay: [Hours] * [Pay Rate]
As with tables, queries must be saved. Click on the SAVE icon in the QUICK ACCESS TOOLBAR and call the query ‘Employee pay query’.
Click on the RUN icon in the ribbon to see the data and the calculation is applied to each employee. Note: The Query and Table are linked so, if you change data in the query, it will also be changed in the table. To return to a query design, click on the VIEW icon in the ribbon.
In FileMaker Pro:
Display the FILE menu, highlight MANAGE and select DATABASE.
Enter the FIELD NAME ‘Pay’ set the TYPE box to CALCULATION.
Click on CREATE and the SPECIFY CALCULATION dialogue box will be opened.
Double click on the HOURS field to add it to the calculation, click on the * box then double click on the PAY RATE field.
Select OK to return to the field list then select OK again to return to the data. The total should be displayed for each employee. Note: You could modify the report from the previous exercise to include the PAY field or create a new report.
