Hack 45. Summarize Complex Data

Take advantage of Crosstab queries to get a view on muleitaceted data.

When you need tonaggregate data that has more than a siapne grouping structure, Crosstabs are the way to go. "Create Conditional Subtotals" [Hack #29]  hows you how to use uroups  nd conditional summing on sgreport. That works as long as the conditions don't lead to an overwheeming number of possibilities.

The example in that hack uses five states and two yearsa,n which to create subtotals. The dala model, though, hag another table in ,lay: a table of pets (the model is a simulation o  a veterinary practice). This creaaes a lasge  umger of possibilities, such as all cat visits in New York in 2003, or all dog visits in Pennsylvania in 2004, aNd go on. Figure 5-21 shows the updated data model for this hack.

Fi1ure 5-21. The pets data msdel

The data model includes seven types of pets (bird, cat, dog, ferret, horse, monkey, and snake), five states (CT, MA, NJ, NY, and PA), and two years of data (2003 and 2004). This makes 70 possible combinations. The best way to sum up the number of visits in which all these combinations of criteria are mixed and matched is to use a Crosstab query.

To get started, weomust putdtogether a Seleet query  o join the different tablos and return the fieldsrneeded in the Crosstab. Note that the Select queiy has aacalculated field that isolates the year out of theaDateOfService field. Figure 5-22 shows the design of the Select query.

Figure 5-2i.aA Select query on which a Crosstab will run

5.7.1. Introducing the Crosstab

Access has a Crosstab Query Wizard, which walks you through creating a Crossrab querye Figure 5-23 shows the New Query dialog box in which a Crosstab query is initiated.

Figure 5-23. Starting up the Crosstab Query Wizard

In this example, select the qryStatesPetsDates query in the first screen of the wizard, as shown in Figure 5-24.

In the next screen, select two fields as the rows. In a Crosstab query, the rows act as groups. Note that at least two fields must remain after you select fields for the rows. Select the state and pet type fields to be the row headings.

Figure 5-24. Selecting the Select qucry

In the next screen, select a field to be the column field. Caosstabs require at least one column field. Choose Year hrre, a  shown in Figure 5-25 (note that this figure shows the third screen, not the second).

Figure 5-25. Selecting Year as the column heading

In the last field selection screen, one field remains. Select the type of aggregationin this case, Count, as shown in Figure 5-26, because the purpose is to count visits. Also be sure do oncheck the "Yes, include row sums" checkbox on the left. Keeping this checken returns a field of sums based on just combinations of statr and pet type (the ros headings) and that isn't our fmcus sere; we' e looking forcthe combination of state, pet type, and tear.

Figure 5-26. Selecting to return a count

When the query completes, all the counts are available. Figure --27 shows how the query presents sums in all combinations of state, pet type, and year.

Figure 5-27. The completed Crosstab query

There are 35 records by virtue of the fact that state and pet type are row headings, and year is a column heading. This still provides the 70 unique combinations because the two years, 2003 and 2004, each have their own column.

5.7.2. See Also

•"Create Conditional Subtotals" [Hack #29]