Chapter 15
In This Chapter
Doing more with the Total row
Uncovering the Total row in your query grid
Organizing with Group By
Summing your results
Counting everything at one time
Ever need to know how many orders were placed in the past month? Or total donations for last year? How about the top ten best-selling products for the current year? If you answered yes to any of these questions (or have similar questions that need answering), then this chapter is for you. Here I discuss the fabulous Total row. The Total row does it all for your data! Well, actually, it summarizes your data via the select query. (If you don’t know what a select query is or how to create one, I suggest you go back and read Chapter 13 before beginning the material in this chapter.)
In Chapter 13, I show you how simple select queries can fetch data, such as a list of volunteers who reside in California or all the details of tofu sales. The Total row takes the select query one step further and summarizes the selected data. The Total row can answer questions such as, “How many of our volunteers reside in California?” and “How much money did we make in tofu sales last month?” It can also do statistical calculations, such as standard deviations, variances, and maximum and minimum values. For a complete list of what the Total row can do for each field selected in the Field row, see Table 15-1.
Table 15-1 Total Row Functions
Instruction |
What It Does |
Group By |
Groups the query results by the field’s values. |
Sum |
Totals all the values from this field in the query results. |
Avg |
Averages the values in this field in your query results. |
Min |
Tells you the lowest value found in the field. |
Max |
Reports the highest value found in the field. |
Count |
Counts the number of records that match the query criteria. |
StDev |
Figures the statistical standard deviation of the values in the field. |
Var |
Calculates the statistical variance of the values in the field. |
First |
Displays the first record that meets the query criteria. |
Last |
Displays the last record that meets the query criteria. |
Expression |
Tells Access that you want a calculated field. (See Chapter 16 for the different calculations Access can perform.) |
Where |
Uses the field for record-selection criteria, but doesn’t summarize anything with it. |
To make Access perform these calculations, you must first group your records together by using the Total row’s Group By function. (The Total row makes an appearance in Figure 15-1.) As you might imagine, Group By treats multiple repeated instances of information as one. It puts all the Californians together on one row so you can count the number of Californians in your database. Typically, you apply Group By to a text or ID field and the remaining functions in the Total row on numeric fields.
The most commonly used items among the Total row’s offerings are Group By, Sum, Avg, Count, and the odd-sounding option Where. Later sections in this chapter go into more depth about these items — explaining what they do, how to use them, and why you really do care about all this stuff.
By default, Access always assumes you want a simple select query. You must tell it specifically that you want to summarize your data; adding the Total row to your query does the trick.
Okay, enough chatter about the Total row; it’s time to get busy. Make sure you’re in Design view, and then follow these steps to create summary queries with the Total row:
Create a new select query or open an existing select query that contains the data you want to summarize.
If you’re scratching your head at this point, refer to Chapter 13 for information on creating select queries.
Turn on the Total row by clicking the Totals button in the Show/Hide group of the Design Ribbon’s Query Tools tab.
The Total row appears between the Table and Sort rows on the query grid. For every field already in your query, Access automatically fills the Total row with its default entry, Group By.
The Totals button displays the Greek letter sigma (Σ). Mathematicians, engineers, and others with questionable communication skills use this symbol when they mean “give me a total.”
To change a field’s Total entry from Group By to something else, click that field’s Total row.
The blinking-line cursor appears in the Total row, right next to a down-arrow button.
Click the down-arrow button in the field’s Total row and then select the new Total entry you want from the drop-down menu that appears.
The new entry appears in the Total row.
Make any other changes you want and then run the query.
By setting values in the Total row, the query results automatically include the summary (or summaries) you selected. How about that?
The following section shows how to use the most popular and useful Total row options.
This section focuses on the most commonly used items in the Total row’s toolbox: Group By, Sum, Count, and Where. Remember, the choice made in the Total row applies to the field selected in the Field row above it.
The Group By instruction has two functions:
When you turn on the Total row in your query grid, Access automatically puts in a Group By for every field on the grid. Group By combines like records so that the other Total row instructions (such as Sum and Count) can do their thing. So to make effective use of the Total row, your query must return one or more fields that contain duplicate information across records.
Put the Group By instruction into the field you want to summarize — the one that answers the question, “What do you want to count by?” or “What needs totaling?” To count California volunteers (for example), group by the State
field in your table. To produce a list of total dollar donations by volunteer, you need to group by the FirstName
and LastName
fields.
When you use Group By, Access sorts the results automatically, in an order based on the field you specified with the Group By instruction. If you put Group By in the State
field, for example, Access sorts your results alphabetically by the contents of that field. To override this behavior and choose a different sorting order, just use the Sort row in your query grid. Here’s how:
Put the appropriate sorting command (Ascending or Descending, depending on your needs) in that field’s Sort row, as shown in Figure 15-3).
Access organizes the query results in the indicated order.
Sum finds the total value of numeric fields:
Group By
field.Use the Count instruction in the query when you want to know how many entries are in the group, instead of performing mathematical calculations on numeric fields for the group.
Because Count doesn’t attempt any math on a field’s data, it works on a field of any data type in your tables.
The Where instruction works a bit differently from the other options in the Total row. The Where instruction lets you add criteria to the query (such as showing volunteers from certain states, or including orders placed only after a certain date) without including additional fields in your results. In fact, Access won’t allow you to show a field in your query results that contains the Where instruction.
The query in Figure 15-8 uses a Where instruction to limit which records appear in the query results. Normally, that query would count donations by volunteer, using every record in the table. Adding a Where instruction to the City
field tells the query that it must test the data before including it in the results. In this case, the Where instruction’s criteria include records for those people living in Lancaster or Marietta.
Here’s a problem that is a snap to solve with Access. Suppose you need a list of your top ten volunteers, ranked by donation amounts. Or a list of the top five best-selling products last year. Or a list of the top whatever. The query property Top Values takes all the dirty work out of this chore. Simply set it and forget it — the dirty work, that is! You can return the top values (for example, the top 5 out of a list of 40) or top percentage of values (say, the top 5 percent, which returns the top 2 out of 40) with the Top Values property.
Follow these instructions to make a top-ten (or whatever number you choose) list:
Open the query containing the data for your top-values list in Design view.
The query must contain at least one numerical field so that a set of top values can be selected. Usually, it’s a summary query, such as total donation amount by volunteer. The query must be sorted on the numerical field.
Click the drop-down list arrow next to the Return button and select a choice from the list or type your own number in the box.
Because 10 isn’t on the list, you’ll have to type 10 in the box to generate a top-ten list. (See Figure 15-9.)
Switch to Datasheet view.
The list is limited to the range of top values determined by the number or percentage you entered in the box.
Deciding which field gets a Sum, Count, or other Total row instruction greatly affects your query results. If you choose the wrong field, Access fails to tally things correctly.
Data type mismatch
error message if you do.Group By
fields. For example, if you group by donation date and count the VolunteerID
field, you get a count of donations per day.To see the kinds of miscues that can happen, check out Figure 15-10. There you’ll see a summary query that has been written to count volunteers and zip codes. The Count instruction has been applied to both the VolunteerID
and Zip
fields of the Volunteers table. The Zip
field returns 17, whereas the VolunteerID
field returns 19. Which is right? The latter is correct because each record in the table has a volunteer ID. Not every record has a zip code specified. Therefore, Access counts only the records for which a value appears in the Zip
field for a customer. Choose your fields wisely to avoid this problem.
The Total row takes some getting used to. But in no time, you’ll master the power of this tool in your queries. When that day comes, say goodbye to your old friend Mr. Spreadsheet forever!