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

ID: Q155489

The information in this article applies to:

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how to use Visual Basic for Applications to change the column headings in a crosstab query.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q182568
   TITLE     : ACC97: Microsoft Access 97 Sample Queries Available on
               MSL

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

    the Immediate window in version 2.0), and then press ENTER.

      ?ChangeColumnHeadings()

    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.

    clause has changed from the SQL in the original query.

REFERENCES

For more information about the SQL property of a QueryDef object, search the Help Index on the phrase "SQL property," and then view "SQL Property," or ask the Microsoft Access 97 Office Assistant.

For more information about using the IIf() function, search the Help Index for "IIf function," or ask the Microsoft Access 97 Office Assistant.

Keywords          : kbusage PgmObj 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998