A powerful way to analyze data in a view is to group common data together.
You can group data using an aggregation function.
For example, suppose you have a Tools table that lists tools, including the
type of tool and its price. Your table includes several tools of each type --
power tools, hand tools, specialty tools, and so on. Suppose that you would like
to see the number of tools belonging to each type, and the average price for a
particular type of tool. You can group your data by tool type and quickly find
the data that you're looking for.
First, create a new view on the Tools table. Add the ToolType field, which is
a lookup to another table containing tool types. Also add the Price field. For
now, don't add any other fields.
Select the data region of your view, and set the Query Type property to
Grouped. Your view will automatically update to show your data grouped on
ToolType, with one record for each type of tool. If you select the ToolType
field, you'll see that it has a property called Aggregate Function, and that
this property is set to Group By. This property setting specifies that the data
in the view should be grouped by tool type.
You'll also see the Price field showing the sum of all tool prices for each
type of tool. If you select the Price field, you'll see that its Aggregate
Function property is set to Sum. You can change this setting to Average to show
the average price for each type of tool.
Now all that remains is to count the number of tools in each category. Add
another field from the Tools table to the view, such as the ToolName field. When
you add this field, you'll see that your data is no longer grouped -- you now
have one row for each unique tool name, rather than one row for each tool type.
If you select the ToolName field, you'll see that the Aggregate Function
property is set to Grouped, meaning that the view is grouping data by the
ToolName field. If you change the Aggregate Function property for this field to
Count, your data will once again be grouped by the ToolType field, and the
ToolName field will show the number of tools of each type.
Using aggregation functions to display data the way you want can be tricky.
Try to start by adding the smallest number of fields possible to a new view. If
your data is not being grouped as you expect, check the setting of the Aggregate
Function property for each field. Make sure that only the fields on which you
want to group are set to Group By.