Query.
You can use the ColumnHeadings property to specify the order or limit the number of columns displayed in a crosstab query. For example, in a query that displays the names of countries, you may want to show them in a specific order, such as United States first, Canada second, and the United Kingdom third.
Note The ColumnHeadings property applies only to crosstab queries.
Enter the data values from the column headings field as a string expression in the order you want them displayed in the datasheet for the crosstab query, separated by a comma (or the list separator set in the Regional Settings section of the Windows Control Panel). The following table shows sample ColumnHeadings settings and the result in the crosstab query’s datasheet.
| Sample setting | Result |
| “Qtr 1”, “Qtr 2”, “Qtr 3”, “Qtr 4” | Displays column headings for each quarter |
| “Mexico”, “Canada”, “USA” | Displays column headings for each country |
You can set this property using the query property sheet or Visual Basic. You can also set it in SQL view of the Query window by using a PIVOT clause in the SQL statement.
Note The column headings you specify for the ColumnHeadings property must exactly match the data values in the column headings field in the query design grid.
You use the ColumnHeadings property to obtain more control over the appearance of column headings in a crosstab query. By default, Microsoft Access displays all data values as column headings in ascending order. For example, if your column heading field name is Month, the default column headings displayed will be April, August, December, February, and so on. You can use the ColumnHeadings property to display the data in the correct order with the appropriate setting: “January”, “February”, “March”, and so on.
If you include a column heading in the ColumnHeadings property setting, the column is always displayed in query Datasheet view, even if the column contains no data. This is useful for a report based on a crosstab query, for example, when you always want to display the same column headings in the report.
Tip You can use the ColumnHeadings property to improve the speed of some crosstab queries by limiting the number of columns displayed.
ColumnHeads Property.
This example creates four column headings (Qtr 1, Qtr 2, Qtr 3, Qtr 4) in Datasheet view of the crosstab query listing the quarterly orders by product. You can enter this statement in SQL view of the Query window.
TRANSFORM Sum(CCur([Order Details].[UnitPrice]* _
[Order Details].[Quantity]*(1-[Discount])/100)*100) [Product Amount] Products.ProductName FROM Products JOIN (Orders INNER JOIN [Order Details] Orders.OrderID = [Order Details].OrderID) ON Products.ProductID _
= [Order Details].ProductID (((Orders.ShippedDate) Between #1/1/94# And #12/31/94#))BY Products.ProductName "Qtr " & DatePart("q",[ShippedDate],1,0);