Sometimes you aren’t interested in each and every row in your table-you’d rather see calculations across groups of data. For example, you might want the total product purchase amount for all companies in a particular state. Or you might want to know the average of all sales for each month in the last year. To get these answers, you need a totals query.
If you’re the housing facilities manager, you might be interested in producing sales and usage numbers by facility or by date range. For this series of exercises, open HousingDataCopy.accdb and start a new query with tblFacilities and tblReservations in the query design grid. Include in the Field row the FacilityName field from tblFacilities and the CheckInDate and TotalCharge fields from tblReservations.
|Inside Out-When Totals Queries Are Useful|| |
We might occasionally build totals queries to display high-level summaries in a report. More often, we create a regular query that fetches all the detail we need and then use the powerful summarization facilities in reports to calculate totals. You’ll learn more about summarizing data in a report in Chapter 16, “Advanced Report Design.”
A totals query groups the fields you specify, and every output field must either be one of the grouping fields or the result of a calculation using one of the available aggregate functions. (See Table 8–1.) Because all fields are calculated, you cannot update any fields returned by a totals query. So, you’re not likely to find totals queries useful in forms.
This does not mean that learning about how to build totals queries is not useful. You need to understand the concepts of grouping and totaling to build reports. You will also find that constructing and opening a totals query in Visual Basic code is useful to perform complex validations.
Calculates the sum of all the values for this field in each group. You can specify this function only with number or currency fields.
Calculates the arithmetic average of all the values for this field in each group. You can specify this function only with number or currency fields. Access does not include any Null values in the calculation.
Returns the lowest value found in this field within each group. For numbers, Min returns the smallest value. For text, Min returns the lowest value in collating sequence (“dictionary” order), without regard to case. Access ignores Null values.
Returns the highest value found in this field within each group. For numbers, Max returns the largest value. For text, Max returns the highest value in collating sequence (“dictionary” order), without regard to case. Access ignores Null values.
Returns the count of the rows in which the specified field is not a Null value. You can also enter the special expression COUNT(*) in the Field row to count all rows in each group, regardless of the presence of Null values.
Calculates the statistical standard deviation of all the values for this field in each group. You can specify this function only with number or currency fields. If the group does not contain at least two rows, Access returns a Null value.
Calculates the statistical variance of all the values for this field in each group. You can specify this function only with number or currency fields. If the group does not contain at least two rows, Access returns a Null value.
Returns the value for the field from the first row encountered in the group. Note that the first row might not be the one with the lowest value. It also might not be the row you think is “first” within the group. Because First depends on the actual physical sequence of stored data, it essentially returns an unpredictable value from within the group.
Returns the value for the field from the last row encountered in the group. Note that the last row might not be the one with the highest value. It also might not be the row you think is “last” within the group. Because Last depends on the actual physical sequence of stored data, it essentially returns an unpredictable value from within the group.
 You can change the sort order for new databases you create by clicking the Microsoft Office Button, clicking Access Options, and then using the New Database Sort Order list in the Popular category. The default value is General, which sorts your data according to the language specified for your operating system.
To turn this into a totals query, click the Totals button in the Show/Hide group of the Design contextual tab under Query Tools to open the Total row in the design grid, as shown in Figure 8–21. When you first click the Totals button in the Show/Hide group, Access displays Group By in the Total row for any fields you already have in the design grid. At this point the records in each field are grouped but not totaled. If you were to run the query now, you’d get one row in the recordset for each set of unique values-but no totals. You must replace Group By with an aggregate function in the Total row.
Figure 8–21: The Total row in the design grid allows you to define aggregate functions.
Access provides nine aggregate functions for your use. You can choose the one you want by typing its name in the Total row in the design grid or by clicking the small arrow and selecting it from the list. You can learn about the available functions in Table 8–1.
Let’s experiment with the query you started earlier in this section to understand some of the available functions. First, you probably don’t want to see information grouped by individual date. Data summarized over each month would be more informative, so create an expression to replace the CheckInDate field as follows:
CheckInMonth: Format([CheckInDate], "yyyy mm")
The Format function works similarly to the table field Format property you learned about in Chapter 4. The first parameter is the name of the field or the expression that you want to format, and the second parameter specifies how you want the data formatted. In this case, we’re asking Format to return the four-digit year and two-digit month number.
For more information about Format settings, see “Setting Control Properties” on page 651.
Change the Total row under TotalCharge to Sum. Add the TotalCharge field from tblReservations three more times, and choose Avg, Min, and Max, respectively, under each. Finally, add the ReservationID field from tblReservations and choose Count in the Total row under that field. Your query design should now look like Figure 8–22.
Figure 8–22: This query design explores many different aggregate functions.
Switch to Datasheet view to see the results as shown in Figure 8–23. The sample data file has 306 available rooms in four different facilities. From the results of this query, you could conclude that this company has far more housing than it needs! Perhaps the most interesting row is the second row (the row that is highlighted in Figure 8–23). The five reservations for Housing A in April 2007 show how the various functions might help you analyze the data further.
Figure 8–23: Running the query in Figure 8–22 returns total revenue, average revenue, smallest revenue per reservation, largest revenue per reservation, and count of reservations by facility and month.
“I didn’t specify sorting criteria, so why is my data sorted?
A totals query has to sort your data to be able to group it, so it returns the groups sorted left to right based on the sequence of your Group By fields. If you need to sort the grouping columns in some other way, change the sequence of the Group By fields. Note that you can additionally sort any of the totals fields.
In the list for the Total row in the design grid, you’ll also find an Expression setting. Select this when you want to create an expression in the Total row that uses one or more of the aggregate functions listed earlier. For example, you might want to calculate a value that reflects the range of reservation charges in the group, as in the following:
Max([TotalCharge]) - Min([TotalCharge])
As you can with any field, you can give your expression a custom name. Notice in Figure 8–23 that Access has generated names such as SumOfTotalCharge or AvgOfTotalCharge. You can fix these by clicking in the field in the design grid and prefixing the field or expression with your own name followed by a colon. In Figure 8–24, we removed the separate Min and Max fields, added the expression to calculate the range between the smallest and largest charge, and inserted custom field names. You can see the result in Datasheet view in Figure 8–25.
Figure 8–24: In this figure we are adding an expression and defining custom field names in a totals query.
Figure 8–25: This is the result in Datasheet view of the query shown in Figure 8–24.
You might filter out some records before your totals query gathers the records into groups. To filter out certain records from the tables in your query, you can add to the design grid the field or fields you want to filter. Then, create the filter by selecting the Where setting in the Total row (which will clear the field’s Show check box), and entering criteria that tell Access which records to exclude.
For example, the manager of the Sales department might be interested in the statistics you’ve produced thus far in the query in Figure 8–24, but only for the employees in the Sales department. To find this information, you need to add tblEmployees and tblDepartments to your query (and remove the extra join line between the EmployeeNumber field in tblEmployees and the ManagerNumber field in tblDepartments). Add the Department field from tblDepartments to your design, change the Total line to Where, and add the criterion “Sales” on the Criteria line under this field. Your query should now look like Figure 8–26.
Figure 8–26: Use the Department field to select the rows that will be included in groups.
Now, when you run the query, you get totals only for the employees in the Sales department. The result is shown in Figure 8–27.
Figure 8–27: This figure displays the recordset of the query shown in Figure 8–26.
You can also filter groups of totals after the query has calculated the groups. To do this, enter criteria for any field that has a Group By setting, one of the aggregate functions, or an expression using the aggregate functions in its Total row. For example, you might want to know which facilities and months have more than $1,000 in total charges. To find that out, use the settings shown in Figure 8–26 and enter a Criteria setting of >1000 for the TotalCharge field, as shown in Figure 8–28. This query should return five rows in the sample database. You can find this query saved as qxmplSalesHousingGT1000 in the sample database.
Figure 8–28: Enter a Criteria setting for the TotCharge field to limit the records to months with more than $1,000 in total charges.
Access 2007 supports a special type of totals query called a crosstab query that allows you to see calculated values in a spreadsheet-like format. For example, you can use this type of query to see total revenue by month for each facility in the Housing Reservations application. If you were entering the data in a spreadsheet, the layout of the result you want might look like Figure 8–29.
Figure 8–29: A spreadsheet mockup shows the result you want in your crosstab query.
We have pointed out the key components in the mockup that you’ll design into your query.
Open the HousingDataCopy.accdb database. To see revenue by facility, you’ll need tblFacilities and tblReservations. Start a new query on tblFacilities and tblReservations. Add the FacilityName field from tblFacilties to the design grid. Revenue gets collected when the employee checks out, and you want to summarize by month. So, enter RevMonth: Format(CheckOutDate, “yyyy mmm”) in the next empty field in the design grid. This expression returns the year as four digits and the month as a three-character abbreviation. Finally, add the TotalCharge field from tblReservations.
Click the Design contextual tab below Query Tools on the Ribbon. Then, click the Crosstab command in the Query Type group. Access changes your query to a totals query and adds a Crosstab row to the design grid, as shown in Figure 8–30. Each field in a crosstab query can have one of four crosstab settings: Row Heading, Column Heading, Value (displayed in the crosstab grid), or Not Shown.
Figure 8–30: This is a crosstab query in Design view.
You must have at least one row heading in a crosstab query, and you can specify more than one field as a row heading. Each row heading must be a grouped value or expression, and the expression can include one or more of the aggregate functions-Count, Min, Max, Sum, and so on. The row heading fields form the columns on the left side of the crosstab. Think of the grouped values of the row heading fields as forming the horizontal “labels” of the rows. In this example, we’ll be grouping by FacilityName. We’ll later modify the basic query we’re building here to add a second row heading using Sum-the total value of each facility’s reservations.
You must also have one (and only one) field defined as a column heading, and this must also be a grouped or totaled value. These values form the headings of the columns across the crosstab datasheet. Think of a regular totals query where one of the columns “pivots,” and the values in the rows become labels for additional columns in the output. These columns appear sorted in value sequence immediately following the columns you define as row headings. Because the values in the data you’re selecting determine the column names when you run the query, you cannot always predict in advance the field names that the query will output.
Finally, you need one (and only one) field designated as the value. This field must be a totaled value or an expression that contains one of the aggregate functions. The value appears in the cells that are the intersections of each of the row heading values and each of the column heading values. In the following example, the facility names will appear down the left side, the year and month values will appear as column headings across the top, and the sum of the reservation charge for each group for each month will appear in the intersection.
How do I display more than one value in a crosstab?
The fact that a crosstab query can display only one value field in the intersection of row and column headings is a severe limitation. What if you want to display both the total reservation value as well as the count of reservations? One way is to build two separate crosstab queries-one that provides the sum of the total charge as the value field, and one that provides the count of reservations as the value field-and then join the two queries on the row heading columns. That’s an inelegant way to do it.
Another solution is to create a simple query that includes all the detail you need and then switch to PivotTable view to build the data display you need. You’ll learn about PivotTable and PivotChart views later in this chapter.
As in other types of totals queries, you can include other fields to filter values to obtain the result you want. For these fields, you should select the Where setting in the Total row and the Not Shown setting in the Crosstab row and then enter your criteria. You can also enter criteria for any column headings, and you can sort on any of the fields.
To finish the settings for the crosstab query that you started to build in Figure 8–30, under the FacilityName field in the Crosstab row, click the small arrow and select Row Heading from the list, select Column Heading under the RevMonth expression, and select Value under the TotalCharge field. Also change the Group By setting under the TotalCharge field to Sum.
Switch to Datasheet view to see the result of your query design, as shown in Figure 8–31.
Figure 8–31: This is the recordset of the crosstab query you’re building.
Notice that although you didn’t specify a sort sequence on the dates, Access sorted the dates left to right in ascending collating order anyway. Notice also that the month names appear in alphabetical order, not in the desired chronological order.
Access provides a solution for this: You can specifically define the order of column headings for any crosstab query by using the query’s property sheet. Return to Design view and click in the upper part of the Query window, and then click the Property Sheet button in the Show/Hide group of the Design tab to see the property sheet, as shown in Figure 8–32. (You can verify that you are looking at the property sheet for the query by examining what you see after Selection Type at the top of the property sheet. For queries, you should see “Query Properties.”)
Figure 8–32: These entries in the property sheet fix the order of column headings for the query shown in Figure 8–31.
To control the order of columns displayed, enter the headings exactly as they are formatted and in the order you want them in the Column Headings row, separated by commas. In this case, you are entering text values, so you must also enclose each value in double quotes. Be sure to include all the column headings that match the result of the query. (Notice that we specified all the months in 2007 even though the sample data covers only March to September.) If you omit (or misspell) a column heading, Access won’t show that column at all. When you run the query with formatted column headings, you see the recordset shown in Figure 8–33.
Figure 8–33: This crosstab query recordset has custom headings and custom column order, as defined in Figure 8–32.
Specifying correct column headings can be difficult You must run your query first to determine what headings you’ll see. You might be able to define criteria in your query that guarantee the column headings-for example, you could filter the query to return rows only from a specific year. If you misspell a column heading in the query property sheet, the real danger is that Access gives you no warning that your query returns columns that aren’t in your column heading specification. You’ll see blanks in your misspelled columns, and you could mistakenly assume that no data exists for those columns.
Let’s add a grand total for each row (the total per facility regardless of month) and do something about the blank cells. Wouldn’t it be nice to see a zero in months when there were no reservations?
Switch back to Design view and add another TotalCharge field to the Field row. Give it a name of GrandTotal, and choose Sum on the Total row and Row Heading on the Crosstab row.
Remember the little trick we used earlier to use a plus sign (+) arithmetic operator in a concatenation to remove extra blanks? In this case, we want to do exactly the reverse- wherever there are no values, the Sum returns a Null that we want to convert to a zero. Also remember that when you concatenate two values with the ampersand (&) operator, that operator ignores Nulls. You can force a Null to a zero by concatenating a leading zero character. If the Sum is not Null, adding a zero in front of the value won’t hurt it at all.
In the TotalCharge field you chose as the value field, change Sum to Expression and change the Field line to use this expression:
0 & Sum(TotalCharge)
Any concatenation returns a string value, so you’ll need to convert the value back to a currency number for display. There’s a handy “convert to currency” function (CCur) that will perform this conversion for you. Further modify the expression to read:
CCur(0 & Sum(TotalCharge))
Switch back to Datasheet view, and your query result should now look like Figure 8–34.
Figure 8–34: Your crosstab query now shows a grand total on each row as an additional row heading, and all empty cells are filled with zero values.
As with most tasks in Access, there’s usually more than one way to solve a problem. You can also generate the missing zero values by using the Null-to-zero function (NZ) in your expression instead of using concatenation. Your expression could look like
If you’re not quite getting the result you expect, you can check what you have built against the qxmplRevenueByFacilityByMonthXtab sample query you’ll find in the database.
The total sales by month is interesting, but what can you do if you want to break the data down further? For example, you might want to know the value of sales across a range of room prices. This sort of information might be invaluable to the operator of a commercial hotel. What amount of revenue is the hotel receiving from various room prices?
You’ll learn later in Chapter 16 that you can ask the report writer to group data by data ranges. Well, you can also do this in a totals or crosstab query. Let’s continue to work in the HousingDataCopy.accdb database to see how this works.
Start a new query with tblFacilities and tblReservations. Add the FacilityName field from tblFacilities, and create a CkOutMonth field by using the Format function to return a four-digit year and month abbreviation as you did earlier. Add the TotalCharge field from tblReservations to the query grid twice. Click the Crosstab button in the Query Type group of the Design tab to convert your query to a crosstab query.
In the Crosstab row, select Row Heading under the FacilityName field, your CkOutMonth expression, and the first TotalCharge field. Change the name of this first TotalCharge field to GrandTotal, and select Sum in the Group By row. For the second TotalCharge field, select Sum in the Group By row and Value in the Crosstab row.
You still don’t have a Column Heading field or expression defined, but here’s where the fun begins. In this query, your sales manager has asked you for a breakdown of amounts spent per month based on ranges of the DailyRate field. In this database, the lowest daily charge is $40 a day, and the highest is $100 a day. The manager has asked you to display ranges from $40 to $119 in increments of $20 ($40 to $59, $60 to $79, and so on). It turns out there’s a handy function called Partition that will split out numbers like this for you. The syntax of the function is as follows:
Partition(<number>, <start>, <stop>, <interval>)
The number argument is the name of a numeric field or expression you want to split up into ranges. Start specifies the lowest value you want, stop specifies the highest value you want, and interval specifies the size of the ranges. The function evaluates each number it sees and returns a string containing the name of the range for that number. You can group on these named ranges to partition your data into groups for this crosstab query. So, the expression you need is as follows:
Partition(DailyRate, 40, 119, 20)
The function will return values “40:59”, “60:79”, “80:99”, and “100:119”. Add that expression to your query grid and select Column Heading in the Crosstab row. Your query should now look like Figure 8–35.
Figure 8–35: This crosstab query uses partitioned values.
Switch to Datasheet view to see the result that should satisfy your sales manager’s request, shown in Figure 8–36. Note that we didn’t use the trick discussed earlier to fill blank cells with zeros. In this case, the blank cells seem to visually point out the rate ranges that had no sales. You can find this query saved as qxmplRevenueByFacilityByRateRangeXtab in the sample database.
Figure 8–36: Run the crosstab query shown in Figure 8–34 to see the result of partitioning sales totals on ranges of room rates.