Creating a Totals Query


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.

Table 4.1. Aggregate Operations Available for Totals Queries

Operation

Purpose

Group By

Groups the records according to the unique values in the field.

Sum

Sums the values in the field.

Avg

Averages the values in the field.

Min

Returns the smallest value in the field.

Max

Returns the largest value in the field.

Count

Counts the number of values in the field.

StDev

Calculates the standard deviation of the values in the field.

Var

Calculates the variance of the values in the field.

First

Returns the first value in the field.

Last

Returns the last value in the field.

Expression

Returns a custom total based on an expression in a calculated column.

Where

Tells Access to use the field's criteria to filter the records before calculating the totals.


The next few sections show you how to use these operations in your queries.

More Info

I adapted some of the material in this chapter from my book Microsoft Access 2003, Forms, Reports, and Queries (Que Publishing, 2004). If you need more detail about queries, forms, or reports, you can check out the book at your local store or online at www.mcfedries.com/AccessForms/.


Displaying the Total Row in the Design Grid

Before you can work with the aggregate operations, you need to do one of the following in the query design window:

  • Choose View, Totals.

  • Click the Totals button in the toolbar.

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.


Downloading This Chapter's Examples

You'll find the database file that contains this chapter's examples at www.mcfedries.com/OfficeGurus/.


Setting Up a Totals Query on a Single Field

In 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:

1.

Display the Total row, if it's not already displayed.

2.

In the field's Total cell, use the drop-down list to click the function you want to use.

3.

If you want to restrict the records involved in the aggregate operation, enter the appropriate expression in the field's Criteria cell.

4.

(Optional) In the dynaset, Access displays OperationOfFieldName in the field header, where Operation is the aggregate operation you chose in step 2 and FieldName is the name of the field you're working with. If you'd rather see a more readable name, change the Field cell to the following, where FieldName is the name you want to use:Field Alias:FieldName

5.

Run the query.

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 Fields

If you want to see more data in the totals query, you can add more fields:

  • Add other fields and apply any mathematical aggregate operation to each field.

  • Add other copies of the same field and apply different mathematical aggregate operations to each field.

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 Totals

I 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:

1.

Add the nonaggregate field to the query design grid.

2.

Clear the field's Show check box.

3.

In the field's Total cell, drop down the list and click Where.

4.

Add the required expression to the field's Criteria cell.

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 Records

In 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:

1.

Add the field you want to use for the groupings to the design grid.

2.

In the field's Total cell, drop down the list and click Group By.

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 Fields

You 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 Field

So 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.


Access Converts the Query

If you apply an aggregate operation to a calculated field in the manner shown in Figure 4.11 and then close and reopen the query, you'll see that Access has changed the calculated field by "moving" the aggregate operation into the field's expression and changing the Total cell to Expression. Access has converted the totals query so that it uses an aggregate function. See the next section for an explanation of the aggregate functions.


Creating a Totals Query Using Aggregate Functions

The 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.

Table 4.2. Aggregate Functions Available for Totals Queries

Function

Returns

Avg(field)

The average of the values in field.

Sum(field)

The sum of the values in field.

Min(field)

The smallest value in field.

Max(field)

The largest value in field.

Count(field)

The number of values in field.

StDev(field)

The standard deviation of the values in field, where those values are a sample of a larger population.

StDevP(field)

The standard deviation of the values in field, where those values represent the entire population.

Var(field)

The variance of the values in field, where those values are a sample of a larger population.

VarP(field)

The variance of the values in field, where those values represent the entire population.


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 Totals

The 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 Stock

If 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:

  • In case a reorder is necessary, the records are grouped first by SupplierID and then by ProductName (both from the Products table).

  • The query also includes the UnitsInStock field and the Sum of the Quantity field from Order Details.

  • The Left In Stock calculated field is set up with the Expression operation in the Total cell and uses the following expression in the Field cell to create a custom total:

     [UnitsInStock] - Sum([Quantity]) 

  • The records are filtered to include only those orders from a specific date, using the Where operation applied to the nonaggregate OrderDate field.

Figure 4.16 shows the results.

Figure 4.16. The dynaset produced by the query in Figure 4.15.




Tricks of the Microsoft Office Gurus
Tricks of the Microsoft Office Gurus
ISBN: 0789733692
EAN: 2147483647
Year: 2003
Pages: 129

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net