Access Cookbook - Ken Getz [16]
Crosstab
AlbumType
tblAlbums
Group By
Row Heading
Month: Format([DateAcquired], "mmm")
Group By
Column Heading
Album ID
tblAlbums
Count
Value
Select View → Properties if the properties sheet is not already visible. Click on any part of the background of the upper-half of the query screen. This will select the properties for the query itself (as opposed to the Field or FieldList properties). Enter the values of the formatted date, in the order in which you want them to appear, into the ColumnHeadings property. For the qryAlbumTypeByMonth2 query, add three-letter strings for each month of the year (see Figure 1-8). Separate each entry with a comma.
Figure 1-8. The query properties sheet for qryAlbumByMonth2
Save and run the query. The date columns should be ordered chronologically.
Now run qryAlbumTypeByMonth2, which you'll also find in 01-04.MDB. In this query, the months are ordered chronologically (see Figure 1-9).
Figure 1-9. The months in qryAlbumTypeByMonth2 sort chronologically
Discussion
When you convert a date/time field to a formatted date using the Format function, Access converts the date into a string. This means that the formatted date will sort alphabetically, like any other string. Access includes a special query property, ColumnHeadings, to make it easy to work around this unpleasant side effect of using the Format function.
You aren't limited to using fixed column headings with formatted date strings. This crosstab query property comes in handy for several other situations. For example, you might use the ColumnHeadings property to:
Force a crosstab to always contain a column heading, even if no values exist for that column. For example, you could use the ColumnHeadings property to include all employee names in a crosstab report, even if one of the employees has no sales for the reporting period.
Force a unique ordering for the columns of a crosstab query. For example, if your Column Heading field is made up of the names of regions, you can use the ColumnHeadings property to ensure that the home region always appears as the leftmost column.
Eliminate a column value. If the ColumnHeadings property contains any values, any column headings in the result set that are not listed in the property will be left out of the crosstab query. You can also accomplish this by using query criteria.
WARNING
When you use the ColumnHeadings property, you must spell the column heading values exactly as they appear in your data. If you misspell a column heading value, that value will not appear in the crosstab query. For example, if you use Format(datefield, "ddd") as the Column Heading field and create fixed column headings of Mon, Tue, Wed, Thr, Fri, Sat, and Sun, the Thr column will be completely blank because the Format function returns Thu (not Thr) for day of week.
You cannot set the ColumnHeadings property programmatically. Setting this property in the Access user interface causes an IN clause to be added to the SQL of the query, and the only way to accomplish this in code is to add or modify that IN clause yourself.
1.5. Group Mailing Labels by Address
Problem
You need to print mailing labels intended for the general public. If your mailing list contains multiple occurrences of the same last name at the same address, you want to print only one label (addressed to the entire family). Otherwise, you need to print one label for each person in the table.
Solution
To avoid sending duplicate mailings to multiple members of a family, you can use a totals query to group label data so that people with the same last name who live at the same address will make up only one row in the output query. In addition, if you count the number of occurrences of combinations of last name, address, and zip code, you can create the mailing-label text with different text for mailings to a family based on that count.
To create this grouping in your own data, follow these steps:
Create a new query (qryCountNames, in this example) based on your table. Turn this query