Totals Queries


Totals queries enable you to find sums of values by using aggregate functions. Their capacities are extremely important and useful. You can count the number of orders in March, find the average freight per order, calculate the total number of units on hand for a product category, and so on.

How is creating a totals query different from creating other queries? In terms of technique alone, the major difference is the addition of the Total row to the design grid. You make selections in the Total drop-down list to designate the aggregate function you want to use, the grouping you want, and which fields are to be used for criteria. Although the utility of each function obviously depends on database and user, I focus on three of the most important aggregates: Sum, Avg, and Count.

Finding a Sum for All Records

Let's first create a simple query that sums a field. The Merchandise table has a field for the units in stock for each product. You can create a totals query that sums the field. Then you'll compare this method with typing an expression using the Sum function to find the same number.

1.

In the Database window, select the tblMerchandise table. Choose Insert, Query. With Design View selected, click OK in the New Query dialog box.

2.

Choose File, Save. Save the query as qryUnitsInStock.

3.

Click the Totals button on the toolbar, which has an image of the Summation symbol () on it (see Figure 9.7). Alternatively, you can choose View, Totals.

Figure 9.7. You can select the aggregate function from a drop-down list in the Total row.


The Total row is added to the query.

4.

From the field list, double-click UnitsInStock to add it to the grid.

5.

Open the drop-down list in the Total row and choose Sum (see Figure 9.7).

6.

Click View. Access has totaled the field.

Let's give the field an easier-to-understand caption than SumOfUnitsInStock.

7.

Click View to return to Design view. Right-click anywhere in the first column and choose Properties.

8.

In the Caption property, type Total Units. Close the property sheet.

Now you'll find the same total by creating an expression with the Sum function:

1.

Click in the first row of the second column.

2.

Type Sum([UnitsInStock]), which includes these elements:

  • Sum is the function that sums the field.

  • [UnitsInStock] is the field that's being summed.

  • Parentheses enclose the entity being summed.

3.

Click View. The results in both columns are the same.

You could similarly edit the caption of the second column.

TIP

To quickly resize a column so you can see the full title, move your pointer to the right border. When the pointer is a double arrow with a bar, double-click for a "best fit."

4.

Click View to return to Design view.

Note that Access recognizes that the expression in the second column is a totals query and that the first and second columns are the same.

5.

Close the query and save your changes.

Finding Sums for Groups

You can also use a totals query to find aggregates for groups. One extremely useful aggregate function is Count, which you can use to find the number of orders Nifty Lions received each day.

1.

In the Database window, select the tblOrders table. Choose Insert, Query. With Design View selected, click OK in the New Query dialog box.

2.

Add the OrderDate and OrderID fields to the design grid.

3.

Choose File, Save. Save the query as qryOrdersByDate.

4.

Click View and review the records.

As it stands, the query is rather useless, but it does show you the data that you'll be working with. On most days, only one order was made, but on a few days, several orders were made. Notice, for example, that on 8/23/04, eight orders were made. If you add the number of OrderIDs for each day, you get the number of orders by date. Put more formally, you'll count the OrderIDs and group them by date.

5.

Click View to return to Design view. Click the Totals button on the toolbar. The Total row is added to the grid.

6.

In the Total row of the OrderDate field, the Total row setting should be Group By.

GROUP BY is a keyword in SQL for dividing data into groups.

7.

Click in the Total row of the OrderID field. Open the drop-down list and select Count (see Figure 9.8).

Figure 9.8. The Count function can be used to find the number of orders on each day.


8.

Click View and review the records.

You have the total orders for each date. Notice, for example, that on 8/23/2004, eight orders were made.

9.

Click View to return to Design view.

Findings Sums with Two Groups

You can easily add a second group to the totals query. For example, the Overnight field in the Orders table is a Yes/No field that shows whether an order was shipped overnight. Add it to the grid as a second group:

1.

Click the Overnight field in the field list. Drag and drop it into the OrderID column.

The Total row setting is Group By.

2.

Click View and review the records.

The records are grouped first by the date and then by overnight and nonovernight shipments. For example, take a look at August 23 orders. Of the eight total orders, three were shipped overnight and five were shipped by regular freight.

3.

Click View to return to Design view.

4.

Move your pointer to the column selector at the top of the Overnight column. When the pointer becomes a down arrow, click to select the column. Click and drag the column to the left of the Order Date column.

5.

Click View and review your records (see Figure 9.9).

Figure 9.9. The orders are grouped first by whether they were sent overnight and then by date.


TIP

You can change the display of the Yes/No field from a check box to text that displays Yes for positive and No for negative. In Design view, right-click in the Overnight? column and select Properties. Click the Lookup tab. Open the drop-down list and select Text Box.


Using WHERE to Set Criteria

You can add criteria to totals queries so that only records that meet specific conditions are summed. For example, suppose you want to count only orders for customers who live in California, Colorado, Nevada, or Washington. You can use the WHERE clause to set criteria.

In this query, you'll also learn about the IN operator, which makes it easy to specify multiple values for a single field. Here's how to modify the query:

1.

Choose Tools, Relationships to open the Relationships window.

Note that the Customers and Orders tables have a one-to-many relationship. The CustState field designates the customer's state. You can add the CustState field and specify criteria to limit the records to those customers for those four states.

2.

Close the Relationships window.

3.

In the qryOrdersByDate query, click View to return to Design view.

4.

Click Show Table (see Figure 9.7 if you've forgotten its location). Click tblCustomers and click Add. Close the Show Table dialog box.

5.

Double-click CustState to add it to the grid.

6.

Open the drop-down list in the Total row of the CustState field and choose Where.

You'll remember that WHERE is an SQL keyword used for designating criteria. Also note that the Show box is deselected.

7.

Click in the Criteria row of the CustState field. Type In(ca,co,nv,wa).

The IN operator makes it easy to include all four states in a short expression. You include each of the states you want and separate them by commas, with no spaces between them. Note that you could have typed ca Or co Or nv Or wa for the same results. In other words, any one of the states is a match. You could also have typed each state abbreviation in a separate row in the Criteria area of the design grid (see Figure 9.10).

Figure 9.10. The criteria in the CustState column is correctly but inefficiently entered as OR criteria.


8.

Click View to see the order count for these four states, which are grouped first by their overnight status and then by date.

9.

Save your work and close the query.

Advanced Examples Using Criteria

Abraham Lincoln famously said, "You can fool all the people some of the time, and some of the people all of the time, but you cannot fool all of the people all of the time."

When you use criteria in total queries, things work a little differently. You can sum all the records and show some of them, sum some of the records and show all of them, or sum some of the records and show some of them.

That's hardly Lincoln-esque speech. Let's try to unravel the mysteries of using criteria in total queries.

Start by creating a new query:

1.

In the Database window, select the tblOrders table. Choose Insert, Query. With Design View selected, click OK in the New Query dialog box.

2.

Save the query as qryLateAugustOrders.

3.

Double-click ShippedDate and ShipCost to add them to the grid.

4.

Click the Totals button to add the Total row.

Setting Criteria

You might want to include criteria for a field you're grouping on. For example, you may want to limit the dates that you will show totals for to a certain time-span. Try this query:

1.

In the Criteria row of the ShippedDate column, type Between 8/20/04 and 8/31/04.

2.

Open the drop-down list in the Total row of the ShipCost field and choose Sum.

3.

Click View. You have the total freight cost for each day in the period you specified.

This example works, but many database experts discourage the method used, especially for queries with large amount of records. Specifically, they argue that it is inefficient to group records before applying criteria (as the example does) instead of applying criteria first and then grouping records. They would therefore recommend that criteria be set in a separate column using WHERE, as follows:

1.

Click View to return to Design view.

2.

Double-click ShippedDate to add a second column of this field to the grid.

3.

Open the drop-down list in the Total row of this new column and choose Where.

Note that the Show check box is deselected.

4.

Cut Between 8/20/04 and 8/31/04 from the first ShippedDate field and paste it into the Criteria row of the second ShippedDate field.

5.

Click View. You get the same results as you did in the last example.

Setting Criteria in an Aggregate Field

Let's modify this query a bit to find the average freight cost for each day. In this case, you would likely also want to know just how many orders there were on each day.

1.

Click View to return to Design view.

2.

Click OrderID in the field list. Drag and drop it into the ShipCost column.

3.

Open the Total drop-down list in the OrderID column and select Count.

4.

Open the Total drop-down list in the ShipCost column and select Avg.

5.

Click View to see your records (see Figure 9.11).

Figure 9.11. Records are limited only by date.


The query shows the average freight grouped by date and also the number of orders on that day.

Now suppose you want to see only days when the average freight cost was above $5. Remember, you've included all records between 8/20/04 and 8/31/04 in your calculations. Now you can just show those days when the average freight cost was above $5.

1.

Click View to return to Design view.

2.

Type >5 in the Criteria row of the ShipCost column. Click View to see your records.

The days when the freight cost was below $5 (namely, 8/23/04 and 8/26/04) are no longer included.

Excluding Records Before and After Performing Calculations

Now let's say you'd like to exclude from the calculations any order with a freight cost below $2.75. Now you're excluding records before you do any calculations.

1.

Click View to return to Design view.

2.

Cut >5 from the Criteria row of the ShipCost column (you'll soon paste it back).

3.

Click View to see your records (because you eliminated the criteria from the last example, these should again match Figure 9.11).

4.

Click View to return to Design view.

5.

Double-click ShipCost to add a second column of the same field to the grid.

6.

In the Total row of the second ShipCost column, open the Total drop-down list and choose Where.

7.

In the Criteria row, type >2.75.

8.

Click View to see your records (see Figure 9.12).

Figure 9.12. Records are limited by date and freight cost.


9.

Click View to return to Design view.

10.

Click in the Criteria row of the first ShipCost field. Paste >5.

11.

Click View to see your records (see Figure 9.13).

Figure 9.13. Records are limited by date, freight cost, and $5 threshold for average cost.


12.

Close the query and save your changes.

Compare the three sets of results in Figures 9.11 through 9.13.

In Figure 9.11, the records were limited only by date. In Figure 9.12, orders with freight costs below $2.75 were not included in the calculation. So the order count on 8/24 was only 4, versus 6 in Figure 9.11. Thus, the average freight cost for 8/24 rose. In addition, freight costs for 8/23 were excluded entirely because they amounted to only $2.57 and did not reach the $2.75 threshold.

Figure 9.13 resembles Figure 9.12, but the 8/26 record of freight costs of $2.95 were eliminated from the record set after calculating the average because they did not exceed the required $5 limit.




Hands-On Microsoft Access(c) A Practical Guide to Improving Your Access Skills
Hands-On Microsoft Access: A Practical Guide to Improving Your Access Skills
ISBN: 0321245458
EAN: 2147483647
Year: 2005
Pages: 169
Authors: Bob Schneider

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