ACC2000: How to Use Code to Change Column Headings in a Crosstab Query (210004)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

The only way you can programmatically change the column headings in a crosstab query is to modify the PIVOT clause of the query's SQL property.

The following example shows you how to create a crosstab query in Microsoft Access, and how to change the query's column headings using Visual Basic for Applications to modify the PIVOT clause of the query's SQL property.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new query in Design view, based on the Customers and Orders tables.
3. On the Query menu, click Crosstab.
4. In the query grid, add the following fields:

   Field: Country
   Table: Customers
   Total: Group By
   Crosstab: Row Heading
   Sort: Ascending
   
   Field: Country Total: OrderID
   Total: Count
   Crosstab: Row Heading
   
   Field: CompanyName
   Table: Customers
   Total: Group By
   Crosstab: Column Heading
   
   Field: OrderID
   Table: Orders
   Total: Count
   Crosstab: Value

5. On the Query menu, click Run.
   
   The query counts each customer's orders and groups them by country. Note that each customer's name appears as a column heading.
6. On the View menu, click SQL View. Note that the PIVOT clause at the end of the SQL statement reads "PIVOT Customers.CompanyName;".
7. Save the query as qryOrdersByCountry and then close it.
8. Create a new module and then type the following line in the Declarations section if it is not already there:

   Option Explicit

9. Type the following procedure:

   Function ChangeColumnHeadings()
   Dim db As DAO.DATABASE
   Dim qd As QueryDef
   Dim strSQL as string
   Set db = CurrentDb()
   Set qd = db.QueryDefs("qryOrdersByCountry")
   strSQL = "TRANSFORM Count(Orders.[OrderID]) AS [CountOfOrder ID] "
   strSQL = strSQL & "SELECT Customers.Country, "
   strSQL = strSQL & "Count(Orders.[OrderID]) AS [Country Total] "
   strSQL = strSQL & "FROM Customers INNER JOIN Orders ON "
   strSQL = strSQL & "Customers.[CustomerID] = Orders.[CustomerID] "
   strSQL = strSQL & "GROUP BY Customers.Country ORDER BY "
   strSQL = strSQL & "Customers.Country PIVOT "
   strSQL = strSQL & "IIf(Customers.[CompanyName] Like 'A*', "
   strSQL = strSQL & "'A', 'B-Z');"
   qd.SQL = strSQL
   End Function
   					

10. To test this function, type the following line in the Immediate window and then press ENTER:

    ?ChangeColumnHeadings()

11. Run the qryOrdersByCountry query. Note that there are two column headings. One column heading is called "A", which counts the orders for company names starting with letter A. The other is called "B-Z", which counts the orders for company names starting with letters B through Z.
12. On the View menu, click SQL View. Note that only the wording of the PIVOT clause has changed from the SQL statement in the original query.