Extend Microsoft Access Applications to the Cloud: Creating Data Sources by Using Queries

Totals and queries

In this section, we look at how to use the Totals feature to produce summarized data; these queries are read only.

Figure 6-21 shows the new query with the Totals icon clicked on the design ribbon. This results in a new Total row on the query grid. The default choice below each field is GroupBy in the Total row, where the query groups records with similar values. Other options include the aggregate functions SUM, Avg, Min, Max, Count, the statistical function StDev, Var, Expression for calculations, and Where for filtering data.

TIP

When working with a calculation and entering an aggregate such as SUM in the Total row, after saving and re-opening the query, you will see Expression in the Total row and the SUM operation moved into the column title. This is because the web app standardizes the way it saves the SQL. So the look of your layout can change, although the resulting data that is displayed will be unchanged.

In our example we have the expression OrderValue:SUM([UnitPrice]*[Quantity]).

FIGURE 6-21 A totals query allowing data to display summarized calculations.

The datasheet preview for this Totals query is shown in Figure 6-22. In the results of this query, you can see that grouping for each company name displays the count of orders (CountOfOrderID) and sum of order values (OrderValue) as totals.

FIGURE 6-22 Summarized results of a Totals query in the datasheet preview.