Home > Sample chapters

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.

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

FIGURE 6-21

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

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