ColumnHeadings Property

Applies To   Crosstab query.

Description

You can use the ColumnHeadings property to specify the order or to 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.

Setting

Enter the data values from the Column Heading field of the crosstab query as a string expression in the order you want the headings displayed in the datasheet for the crosstab query, separated by a comma (or the list separator set in the Regional Settings Properties dialog box in Windows Control Panel). The following table shows sample ColumnHeadings property 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 by using the query's property sheet. You can also set it in SQL view of the Query window or in Visual Basic 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 Heading field in the query design grid. Otherwise, the data won't appear in the columns.

Remarks

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.

See Also   ColumnHeads property, TRANSFORM statement ("SQL Language Reference").

Example

The following example creates four column headings (Qtr 1, Qtr 2, Qtr 3, Qtr 4) in Datasheet view of a crosstab query listing the quarterly orders by product. You enter the following statement in SQL view of the Query window.

TRANSFORM Sum(CCur([Order Details].UnitPrice * [Order Details].Quantity * 
(1-[Discount])/100) * 100)
AS ProductAmount SELECT Products.ProductName FROM Products
INNER JOIN (Orders INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.ShippedDate) Between #1/1/94# And #12/31/94#))
GROUP BY Products.ProductName 
PIVOT "Qtr " & DatePart("q",[ShippedDate],1,0);