ACC: How to Use Code to Change Column Headings in Crosstab Query

MORE INFORMATION

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. Then it shows you how to change the query's column headings using Visual Basic for Applications to modify the PIVOT clause of the query's SQL property. This example uses the sample database Northwind.mdb (or NWIND.MDB in 2.0).

1. Open the sample database Northwind.mdb (or NWIND.MDB in 2.0).
2. Create a new query in Design view and add the Customers and Orders tables.
3. On the Query menu, click Crosstab.
4. In the query grid, add the following fields:

          Field: Country
             Total: Group By
             Crosstab: Row Heading
             Sort: Ascending
          Field: Country Total: OrderID (or Order ID in version 2.0)
             Total: Count
             Crosstab: Row Heading
          Field: CompanyName (or Company Name in version 2.0)
             Total: Group By
             Crosstab: Column Heading
          Field: OrderID (or Order ID in version 2.0)
             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. Note that the PIVOT clause at the end of the SQL statement reads "PIVOT Customers.CompanyName;" (or "PIVOT Customers.[Company Name];" in version 2.0).
7. Save the query as qryOrdersByCountry, and then close it.
8. Create a module, and type the following line in the Declarations section if it is not already there:

   Option Explicit

9. Type the following procedure.
   
   In Microsoft Access 7.0 and 97:

    Function ChangeColumnHeadings()
         Dim db As 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
   						

   In Microsoft Access 2.0:

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

10. To test this function, type the following line in the Debug window (or the Immediate window in version 2.0), 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; one is called "B-Z," which counts the orders for company names starting with letters B through Z.
12. On the View menu, click SQL. Note that only the wording of the PIVOT clause has changed from the SQL in the original query.