You can calculate averages and sums and perform various other calculations on the columns in a table (see Performing calculations in tables). In addition, you can carry out calculations on a selection of the records rather than all of them by defining a filter condition in conjunction with the calculation.
When you select Average, Sum or Calculate from the Data menu, the "Calculate Totals" dialog is displayed with a Condition tab. This allows you to average, sum or perform calculations on a subset of the records in the table. Basically, the Condition box enables you to filter a table according to conditions you have specified and then extracts data from the filtered table according to which fields you selected in Average, Sum or Calculate. A Condition is always set in conjunction with one of Average, Sum or Calculate.
To set a Condition follow these steps:
First choose the fields you want to perform the calculation on (see Performing calculations in tables)
Next, click on the Condition tab to go to the page where you design the condition expression.
The three boxes along the top line - Field, Operator, Value - are used to define a filter condition for each of the fields that is to be used in the filter. Ability takes the field you select and compares all the values in this field to the condition you specify for the filter. The filter selects only those records where the field value fulfils the specified condition.
Click on the arrow in the Field box to display the full list of fields in your table and then click on one of the fields to select it.
Click on the arrow in the Operator box to display the full list of operators that can be used to construct a condition. These include operators for:
Equals (=)
Contains ($)
Begins With (<$)
Ends With (>$)
Less Than (<)
Greater Than (>)
Less Than or Equal To (<=)
Greater Than or Equal To (>=)
Not Equal To (< >)
Is Empty ("")
Not Empty (!!)
In the Value box you type the value that is to be compared, using the operator, to the values in the field you selected in Field.
For example, in a table that contains details on customers, you might select, say, Customer_Name in Field and < in Operator, and type S in Value. This would filter the table to show only the records where the Customer_Name begins with any letter less than S, that is, any letter before S in the alphabet.
Next, you need to insert the condition into the Condition box. Click on the Insert button to do this.
You can construct more complex conditions by using the Group Start, And, Or and Group End buttons. These allow you to use And and Or to link condition expressions, and to control the order of their evaluation using opening and closing brackets Group Start ( and Group End). Whenever you insert a condition into the Condition box, it is placed after the current contents of the box. Therefore, if you have already inserted a condition, you need to insert either an And or an Or before inserting the next condition. Also, judicious use of brackets will ensure that complex condition expressions are evaluated properly.
Once the conditions making up a filter are inserted and the relevant brackets have been placed, you can test the filter to check that it is well-formed, that is to say, it follows the syntax rules or "grammar" for a condition expression. Click on Test. If the condition expression is well-formed a message is displayed: "The Query is Correct!"; if it is not well-formed, an error message is displayed with an indication of what the error is e.g. brackets don't match, syntax error, etc. Note that a well-formed filter expression is merely "grammatically" correct, but doesn't necessarily mean what you want it to mean. For that you need to make sure the filter is well designed according to the purposes you set for it.
To delete the contents of the Condition box as a whole, click on the Clear button. If you only want to delete selected text, first select the text and then right-click your mouse and select Delete (or press Del on the keyboard).
After you have constructed the filter from the constituent conditions and tested it to check that it is well-formed, click on OK to apply the condition to your table. The calculation you defined in Average, Sum or Calculate will only be performed on the filtered table.
Note: The conditions set through the Data menu in this way are temporary and cannot be saved. If you wish to perform certain calculations regularly on a particular set of selected records, it may be better for you to design a named filter and apply it to your table before using the options in the Data menu. In this case you would not have to bother setting any conditions in the Condition box of the "Calculate Totals" dialog.
See: