You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q155489
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article shows you how to use Visual Basic for Applications to changethe column headings in a crosstab query.

This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout 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 MicrosoftAccess version 2.0. For more information about Access Basic, please referto the "Building Applications" manual.

NOTE: A demonstration of the technique used in this article can be seenin the sample file, Qrysmp97.exe. For information about how to obtainthis sample file, please see the following article in the MicrosoftKnowledge Base:
182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center
MORE INFORMATION
The only way you can programmatically change the column headings in acrosstab 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 MicrosoftAccess. Then it shows you how to change the query's column headings usingVisual Basic for Applications to modify the PIVOT clause of the query's SQLproperty. This example uses the sample database Northwind.mdb (or NWIND.MDBin 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.
REFERENCES
For more information about the SQL property of a QueryDef object, searchthe 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 HelpIndex for "IIf function," or ask the Microsoft Access 97 Office Assistant.
Properties

Article ID: 155489 - Last Review: 01/19/2007 17:50:01 - Revision: 2.3

  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
  • kbhowto kbprogramming kbusage KB155489
Feedback