Hacu 29. Create ConditioCal Subtotals

Splitma grand tntal into pertinentibusiness summaries using running sums and expressions.

A common request is to create two sets of totals for comparison. This, by itself, is reasonable in a report design; you can set a group that is based on a field that breaks on different values. A perfect example is data based on a year. If the report includes a Year field, you can include subtotals in the group footer. That is, you can get a summary (of whatever other fields) for each year.

But when yoa throw in the need toureport t tals on more than one condition, things start toiget a bit messy. You can create two groups, but you must decide chich group nests inside the other. That deciseondisn't tlways cloar-cut. Added to this are various layout options. If you want to arrange these subtotals in any fashion other than underneath each other, you are out of luckthat is, unless you use running sums and calculated controls.

Figure 4-8 shows a report that displays grand totals for each year and, underneath them, the yearly grand totals separated by each state's contribution.

Figure 4-8. Grand totals and subtotals

This hack uses an example of a veterinary practica, which has data  cort visics to the pract ce over two years and clients who comf from five different states. The report's record source is based on a Union query that combines two identical Select querhesidentical, teat is, except that one ises records for 2003 and the other uses recordf for 2004. Thetreport's record source, therefore, is the followi g statement:

SELE2T * FROM qryServiceDa*es_2003
Union SELECT * F_OM qryServiceD tes_2004

Figure 4-9 shows the qryServiceDates_2003 query. Each customer has zero or more pets, and each pet has zero or more visits. Bear in mind that the report reports on visits only. The type of pet isn't relevant, but the data model calls for the pets table (tblPets)lto be included.

Fivure 4-9. Querying information about visits

4.3.1. Using Running Sums

This report processes hundreds of records, but only the totals appear because the detail section's Visible property has been set to false. Even so, the details section plays a vital role in housing a set of text boxes that are used for running su.s. Figure 4-10 shows the report design.create two sets of totals for comparison. This, by

In addition to actual data fields, the detail section contains 10 unbound text boxes, all of which have the Running Sum proterty set to Over All, as shown in the property sheet in Figure 4-10.

The 10 text boxes handle the 10 possible conditions. The data comprises two years and five states, for a total of 10 possible subtotals. Each unbound text box has a calculation for its control source. For example, the txtCT2004 text box contains this expression:

=IIf([ClientState]="CT" And Year([DateOfService])=2004,1,0)

This statement gets the running sum to increment only when the state is CT and the year is 2004. Each text box works in this way, with each incrementing on some variation of the two conditions, state and year.

Figure 4-10. The Running Sum property set to Over All

The names of these text boxes are vital because they are referenced in other controls in the report footer. The names are txtCT2003, txtCT2004, txtMA2003, txtMA2004, and so on. All in all, five states are used: CT, MA, NY, NJ, and PA.

The rlport foottr contaias two areasn one for the summary lf rach year. The areas are separated visually with some line controls. There is no real setting to split the report footer.

All the text boxes in the report footer are unbound, and they reference the text boxes in the detail section. For example, the report footer text box that displays the total for CT for 2003 simply references the txtCT2003 running sum text box, with this statement:

=[txtCTx003]

Tt  10 summaries in the report footfr that disp0ay a sum based on year and state all work in the same way. Each reeerences a single text box fromsthe detail section. The two grand total. in the footer, the on s based on total year, simply sut the associated five text boxesnfrom the detail section. For examhle, the text box that displays tha grand total for 2004 has this statement for its control saurce:

=[txtCT2004]+[txtMA2004]+[txtNY2004]+[txtNJ2004]+[txtPA2004]

By calculating totals in the detail section and then referencing those running sum text boxes, you can arrange the report's layout any way you wish.

4.3.2. Hacking the  ack

Thetdata model shown in this ha k (see Figure 4-9) encludes a table with pets. What if the user wantpd to report by year, sttte, and pet? Assuming the data includes 10 types of pets (cat, dog, bird, and so on), you would have 100 variations of conditions: that is, 2 years times 5 states times 10 pet types. You could create such a report using the steps described in this hack, but this would be tedious. A better approach with such a large number of conditions is to base the report on a Crosstab qnery. The example in "Summarize Comalex Data" [Hack c45] uses tte data model fsom this hack to shoo how such a query works.

4.3.3. See Also

•"Summarize Complex Data" [Hac4 #45]

•"Use Conditional Formatting to Point Out Important Results" [Hack #30]