Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

712

Notice also that the CompanyName field is set to ascending sort (with A on top), which causes

alphabetic grouping starting at names beginning with A and progressing to names beginning with

Z. With this combination of properties, all companies starting with A will be grouped together,

those beginning with B will be in another group, and so on.

For this example, a slightly different report is used to illustrate prefix character grouping. This

report (rptSalesJanuarayAlpha1, shown in Figure 20.3) shows purchases during the month

of January, sorted by customer name. The order date, the order ID, and the employee filling the

order are shown across the page. The result of the sorting and grouping specification in Figure

20.1 is shown in Figure 20.4. (Hide Duplicates has been set to Yes for the CompanyName

field so that each customer appears only once in the list.)

FIGURE 20.4

A rearrangement of the data shown in Figure 20.13, later in this chapter

It’s important to note that the data shown in Figure 20.4 is identical to the data shown in Figure

20.13. In fact, the same record source (qrySalesJanuary, shown in Figure 20.11, later in this

chapter) is used for both of these reports. Often, a data rearrangement yields useful information.

For example, you can easily see that Bottom-Dollars Market placed three orders in January, one

with salesperson Steven Buchanan, one with Robert King, and one with Nancy Davolio.

Let’s assume you want to refine the rptSalesJanuaryAlpha1 report by labeling the groups

with the letters of the alphabet. That is, all customers beginning with A (Antonio Moreno Tagueria

and Around the Horn) are in one group, all customers beginning with B (Blondel père et fils, Bon

app’ and Bottom-Dollars Market) are in one group, and so on. Within each group, the company

names are sorted in alphabetical order. The sales to each customer are further sorted by order date.