Performing calculations in tables

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).

  1. 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.

  2. 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.

  3. 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).

  4. Once you have constructed the formula, click on the Add button to insert it into the large box at the bottom of the dialog.

  5. 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.

  6. 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.

  7. Finally, click on OK to apply the formula.

  8. 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.