Microsoft KB Archive/210004

= ACC2000: How to Use Code to Change Column Headings in a Crosstab Query =

Article ID: 210004

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210004



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

This article applies only to a Microsoft Access database (.mdb).



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

NOTE: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

207626 ACC2000: Access 2000 Sample Queries Available in Download Center



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.

 Start Microsoft Access and open the sample database Northwind.mdb. Create a new query in Design view, based on the Customers and Orders tables. On the Query menu, click Crosstab. 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

 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. On the View menu, click SQL View. Note that the PIVOT clause at the end of the SQL statement reads "PIVOT Customers.CompanyName;".</li> Save the query as qryOrdersByCountry and then close it.</li> Create a new module and then type the following line in the Declarations section if it is not already there:

Option Explicit

</li>  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 </li> To test this function, type the following line in the Immediate window and then press ENTER:

?ChangeColumnHeadings

</li> 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.</li> 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.</li></ol>

<div class="references_section">