You can extract certain types of data about the records and columns in a table, query or relation, as well as perform some calculations.
Select the Data option on the menu bar to access options for:
Count
Average
Sum
Calculate
Average, Sum and Calculate can be used with a selection of the records rather than the whole table by defining a filter condition on the Condition page (see Performing calculations in a filtered table).
Count has options for All Records - counts the total number of records in the table (filters are disregarded); Current Filter - counts the number of records in the filtered table (if no filter has been applied the result is the same as for All Records); and Marked Records - counts the number of currently selected records. Select one of these and select OK.
Average allows you to find the average value of one or more numeric columns. In the Average page of the "Calculate Totals" dialog, click on the fields (columns) in Available Fields for which you want to find the average, and click on Add to move them to Selected Fields. If you want to move a field into a position between fields that have already been moved over, click on the field in Selected Fields above which you want the field to be inserted and use the Insert button rather than Add. Click on OK. The result is displayed in a "Summary Results" dialog, with an average value for each selected column. You can print this or copy it to the clipboard. Click on Done to close the dialog when you have finished.
Sum allows you to find the total value of one or more numeric columns. In the Sum page of the "Calculate Totals" dialog, click on the fields (columns) in Available Fields for which you want to find the sum, and click on Add to move them to Selected Fields. If you want to move a field into a position between fields that have already been moved over, click on the field in Selected Fields above which you want the field to be inserted and use the Insert button rather than Add. Click on OK. The result is displayed in a "Summary Results" dialog, with a total value for each selected column. You can print this or copy it to the clipboard. Click on Done to close the dialog when you have finished.
Calculate allows you to perform various calculations on values in numeric columns. Select it to display the "Calculate Totals" dialog. In the Expressions group box you design the calculation you want to perform on a particular column:
In the Field box click on the arrow to display a full list of the numeric columns in the table. Click on a field (column) to place it in the Formula box.
Build the formula by clicking on "+", "-", "*", "/", "(", ")" in turn, as required, to insert them into the formula box, and by typing directly into the Formula box any numbers needed.
Note that you can place as many fields as you want in the Formula box. If you make a mistake double-click on the inserted symbol, number or field to select it and then right-click and select Delete (or press Del on the keyboard).
Once you have constructed the formula, click on the Add button to insert it into the large box at the bottom of the dialog.
Click on Test to test that the formula is well formed, that is to say, it doesn't break any of the syntax rules for a formula, such as an unmatched bracket or a "+" sign with no expression following it.
To clear the formula click on the Clear button. To remove selected expressions, first select the expressions to be removed and then click on Remove.
Finally, click on OK to apply the formula.
A "Summary Results" dialog is displayed, with the results of the formula shown in a column. You can copy this to the clipboard or print it. When you have finished, click on Done to close the dialog and return to the table.
You can also perform a calculation on a limited number of the records by using Calculate in conjunction with filter conditions you set in the Condition box.
Example of a formula
If you have two numeric fields, called Income and Outgoings, in a table called Thrift, which records the state of your monthly finances, you can apply the following formula to your table to work out the percentage of your income that you spend month by month:
(Thrift.Outgoings / Thrift.Income) * 100
The formula will produce a summary column, displaying the monthly ratio of spending to income on a percentage basis.