Access Cookbook - Ken Getz [17]
Add a column to the query grid for each column in your table on which you want to group rows. Our example uses [LastName], [Address], and [Zip]. For each column, set the Total field to Group By. If you want to specify column names, place those names, followed by a colon, before the field names, as shown in Figure 1-10.
Figure 1-10. The grouping query, qryCountNames, with new column aliases
Add a column to the query grid in which Access will count the number of rows that it groups together to make a single row in the output. Choose any field that won't have null values (i.e., a required field), place it in the query grid, and set its Total row to Count. (This field is called [Residents] in this example.) This instructs Access to count the number of rows in the same grouping, as shown in Figure 1-10. You can also use the expression Count(*) instead of using a field.
Add any other fields that you want to show on your labels to the query grid. For each field, set the value in the Total row to First. For each column, add a specific title—if you don't, Access will change each title to FirstOf Figure 1-11. The output of the grouping query qryCountNames To create the text for your labels, create a new query (qryLabels, in this example) based on the previous query (qryCountNames). You'll base the mailing label name on the field in which you counted rows ([Residents], in this example), along with the [FirstName] and [LastName] fields. Pull in whatever columns you want in your label, and add one more for the label name. In our example, the expression for this column ([LabelName]) is: LabelName: Iif ([Residents] > 1, "The " & [LastName] & " Family", [FirstName] & " " & [LastName]) On the mailing label itself, use the [LabelName] field instead of the [FirstName] and [LastName] fields. This field (shown in Figure 1-12) shows either the family name or the single individual's first and last name, depending on the value in the [Residents] column. Figure 1-12. The LabelName field showing the family name or the individual's name To see how this works, open the tblNames table in 01-05.MDB. The raw data appears as in Figure 1-13. Note that there are several examples of family members living at the same address, and we want to create only one label for each of these families. There's also an example of two people with different last names at the same address—we don't want to combine these names into one label. Open the rptLabels report (shown in Figure 1-14). This mailing label report groups the people with common last names and addresses onto single labels, using the family name instead of individual names. Figure 1-13. Sample data from tblNames that includes multiple people per address Figure 1-14. Mailing labels, grouped by last name, address, and zip code Discussion If the value in the counted field is greater than 1, the query builds an expression that includes just the family name: "The " & [LastName] & " Family"
By creating a totals query that groups on a combination of fields, you're instructing Access to output a single row for each group of rows that have identical values in those columns. Because you're grouping on last name and address (the zip code was thrown in to ensure that you wouldn't group two families with the same name at the same address in different cities), you should end up with one output row for each household. You included one column for counting (the [Residents] field, in our example), so Access will tell you how many rows collapsed down into the single output row. This way, the query can decide whether to print an individual's name or the family name on the label.