A totals query includes a column that performs an aggregate operationsuch as summing or averagingon the values of a particular field. A totals query derives either a single value for the entire dynaset or several values for the records that have been grouped within the dynaset. Table 4.1 outlines the aggregate operations you can use for your totals queries.
The next few sections show you how to use these operations in your queries.
Displaying the Total Row in the Design GridBefore you can work with the aggregate operations, you need to do one of the following in the query design window:
As shown in Figure 4.1, Access adds the Total row to the design grid, and each Total cell contains a list of the aggregate operations. Figure 4.1. Choose View, Totals to display the Total row in the query design grid.
Setting Up a Totals Query on a Single FieldIn the simplest totals query case, you can apply one of the mathematical aggregate operations to a single field. Access will then display the mathematical result for that field. The following steps are required to create a totals query on a single field:
Figure 4.2 shows the result when the Sum aggregate operation is applied to the UnitsInStock field of Northwind's Products table. As you can see, the datasheet consists of a single cell that shows the result of the aggregate operation. Figure 4.2. The datasheet shows only the result of the calculation applied to the single field.Setting Up a Totals Query on Multiple FieldsIf you want to see more data in the totals query, you can add more fields:
Note, however, that you can only add fields to which you want to apply an aggregate operation; you can't add nonaggregate fields to the query. Figure 4.3 shows a query with five columns. These include a Count of the ProductID field, Sum of the UnitsInStock field, and three operations on the UnitPrice fieldMax, Min, and Avg. Figure 4.4 shows the result. Figure 4.3. A totals query showing five aggregate operations on three fields.Figure 4.4. The result of the totals query shown in Figure 4.3.Filtering the Records Before Calculating TotalsI mentioned earlier that you can add criteria to any of the aggregate columns, and Access will perform the operation only on the records that match the criteria. What if you want to filter the table based on a field that isn't part of any aggregate operation? You can't include a nonaggregate field in the query results, but it is possible to use a nonaggregate field to filter the records. Here are the steps to follow:
In this case, Access filters the records based on the criteria and then performs the aggregate operation. For example, in the query shown earlier in Figure 4.3, suppose you want to run the aggregate operations on only those products in the Beverages category. To do this, you add the Categories table to the query and then set up the CategoryName field with the Where operation and the criteria "Beverages," as shown in Figure 4.5. Figure 4.6 shows the results for the filtered records. Figure 4.5. A totals query that uses the Where operation to filter the records before performing the other aggregate operations.Figure 4.6. The result of the totals query shown in Figure 4.5.Creating a Totals Query for Groups of RecordsIn its basic guise, a totals query shows a single total for all the records in a table (or all the records in a subset of the table, depending on whether the query includes criteria). Suppose, however, that you prefer to see that total broken out into subtotals. For example, instead of a simple sum on the UnitsInStock field, how about seeing the sum of the orders grouped by category? Grouping your totals requires just two steps:
Figure 4.7 shows the query from Figure 4.5 changed so that the CategoryName field is now set up with the Group By operation. Running this query produces the result shown in Figure 4.8. As you can see, Access groups the entries in the Category Name column and displays subtotals for each group. Figure 4.7. To group your totals, add the field used for the grouping and click Group By in the Total cell.Figure 4.8. Access groups the records and displays subtotals for each group.Grouping on Multiple FieldsYou can extend this technique to derive totals for more specific groups. The general idea is that as you apply the Group By operation to more fields, Access groups the records from left to right. For example, suppose you want to see subtotals for each supplier within the categories. You can do this by adding the Suppliers table to the query, adding the SupplierName field to the right of the CategoryName field, and clicking Group By in the Total cell. Figure 4.9 shows the revised query, and Figure 4.10 shows the result. Access creates the groups from left to right, so the records are first grouped by Category and then by Supplier. Figure 4.9. You can refine your groupings by applying the Group By operation to more fields, where the grouping occurs from left to right.Figure 4.10. The dynaset produced by the query in Figure 4.9.Creating a Totals Query Using a Calculated FieldSo far you've seen aggregate operations applied to regular table fields. However, you can also apply them to calculated fields. For example, you've seen how to use the following expression in the Northwind Order Details table to calculate the extended price of an item given its unit price, quantity, and discount percentage: [UnitPrice] * [Quantity] * (1 - [Discount]) This gives you the total charge per product, but what if you want to know the total charge for the entire invoice? You can calculate this by applying the Sum operation to the calculated field that's based on the previous expression. Also, because Order Details usually includes a number of records for each invoice, you need to group the records by the unique OrderID field to get the per-invoice total. Figure 4.11 shows a query with this setup, and Figure 4.12 shows the results. (For good measure, the query also displays the customer and order date from the joined Orders table and displays the total units in each order.) Figure 4.11. To calculate the total charge for each invoice, apply the Sum operation to the calculated Extended Price field and group the Order Details records by OrderID.Figure 4.12. The dynaset produced by the query in Figure 4.11.
Creating a Totals Query Using Aggregate FunctionsThe collection of Access built-in functions also includes a category called SQL Aggregate that includes all the mathematical aggregate operations. There are nine aggregate functions in all, as shown in Table 4.2.
The most straightforward way to use an aggregate function is to apply it to a single field using an expression in the Field cell instead of entering an operation in the Total cell. For example, instead of clicking Sum in the Total cell of the UnitsInStock field, you can use the expression in the Field cell (see Figure 4.13): Sum([UnitsInStock]) Figure 4.13. This query uses the Sum function to calculate the sum of the UnitsInStock field without using the Total row.The advantage here is that you don't need to display the Total row, so your query is a little less cluttered. (In fact, you must turn off the totals to use this method; otherwise, Access will convert the Sum function to a Sum aggregate operation.) You can also include in the query other calculated fields that use aggregate functions. For example, if you also want to know the maximum unit price, you can create a second calculated field that uses the following aggregate expression: Max([UnitPrice]) Combining Aggregate Functions and TotalsThe problem with using the aggregate functions without the Total row is that there's no function that's equivalent to the Group By operation, so you can't group the records. If you need to use the Group By operation, or if you want to filter the records before the aggregate calculation by using the Where operation, you need to use the Totals feature. This means you can't apply the aggregate functions on a single field, because Access will just convert the function to an operation in the Total row. However, it does mean that you're free to create custom totals. These are totals that you create yourself by building expressions that combine one or more aggregate functions with the other query operators and operands. This is a calculated field, so you enter the expression in the Field cell. Note, too, that you must also choose Expression in the Total cell. Figure 4.14 shows the query from Figure 4.11 converted to use the following aggregate function expression: Sum([UnitPrice] * [Quantity] * (1 - [Discount])) Figure 4.14. The Sum of Extended Price calculated field uses the Sum function in the Field cell and Expression in the Total cell.Calculating Units Left In StockIf you manage inventory, you always need to know how many units of each product you have left in stock. You might take a physical inventory once or twice a year, but in between these counts you still need to keep tabs on the stock in case you need to reorder. The easiest way to do that is to take the existing number of units in stock and subtract the day's order quantities. The result is the number of units left in stock. Figure 4.15 shows a query set up to make this calculation using the Northwind sample database. Figure 4.15. This query uses a custom total to calculate the number of units left in stock after subtracting a day's orders from the current inventory.Following are some features of this query to note:
Figure 4.16 shows the results. Figure 4.16. The dynaset produced by the query in Figure 4.15. |