A table control can be filtered to display only a subset of values by adding a WHERE clause in the Query Wizard.
Step 1: In the Page Layout Spreadsheet, select a table control.
Step 2: In the Data Sources tab, select the appropriate query and open the Query Wizard (Edit…).
Step 3: In the Query Wizard, add a new WHERE clause containing a formula that restricts the contents. For example, you can show only orders with a UnitPrice greater than $20 using the “is greater than” operator coupled with this formula:
=20
The resulting WHERE clause is:
Order Details.UnitPrice is greater than $20
Step 4: Build and run your application.
Table control functions can be very useful in formulas. For example, you can display only records which have a UnitPrice lesser than average of all the UnitPrice values with this WHERE clause coupled with the “is less than” operator.
= ProductsTableControl.Average("UnitPrice")