Extract Headers from Crosstab query

[Peter Hibbs]

I have a Crosstab query (qryOrders) which returns a variable number of
rows and columns. For the purposes of this post I have created one
based on the Northwind database as shown here :-

TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblOrders.ShipCountry)="USA"))
GROUP BY Trim([FirstName] & " " & [LastName]), tblOrders.ShipCountry
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

This just shows the number of orders (OrderID) for each employee name
(rows) against each CustomerID code (Columns) with a criteria of
ShipCountry = "USA". This returns data something like this :-

Employee Name GREAL HUNGC LAZYK LETSS etc-
------------------- ------- -------- ------- -------
Andrew Fuller
Anne Dodsworth
Janet Leverling 3 2
Laura Callahan 1 1 1 1
Margaret Peacock 4 1 1
Michael Suyama 2 1
Nancy Davolio 1 1 1 1
Robert King
Steven Buchanan

I am only showing the first five columns here. With this criteria
(="USA") the query returns 9 rows (employees) and 14 columns
(CustomerIDs). If I change the criteria to ="UK" it returns 9 rows and
8 columns and so on.

What I am trying to do is extract this data using VBA code. For
example, the code creates a recordset and then dumps it into an array
where I can copy it and display it, or whatever. Here is an example of
the code :-


Dim rst As Recordset
Dim vArray As Variant
Dim vRow As Long, vCol As Long

Set rst = CurrentDb.OpenRecordset("qryOrders")
vArray = rst.GetRows(1000)

txtOutput = ""
For vRow = 0 To UBound(vArray, 2)
For vCol = 0 To UBound(vArray, 1)
txtOutput = txtOutput & Nz(vArray(vCol, vRow), 0) & " "
Next
txtOutput = txtOutput & vbCrLf
Next


The For - Next loops are just used to show the results in a text box
on a form while I test the code, it won't be used in the actual
program.

The problem is that I need to extract the column headers as well as
the data itself. I can easily calculate the number of rows and columns
returned by the query using the UBound function but I can't see any
simple way of returning the column header names which could be
different for each criteria (apart from the first column which will
always be 'Employee Name' of course).

Interestingly, if I export the query as a text file Access gives you
the option of including the column headers so they must be stored
somewhere.

Does anyone know how to read the column headers from a Crosstab query?

Peter Hibbs.

 

[Douglas J. Steele]

I have a Crosstab query (qryOrders) which returns a variable number of
rows and columns. For the purposes of this post I have created one
based on the Northwind database as shown here :-


What I am trying to do is extract this data using VBA code. For
example, the code creates a recordset and then dumps it into an array
where I can copy it and display it, or whatever. Here is an example of
the code :-


Dim rst As Recordset
Dim vArray As Variant
Dim vRow As Long, vCol As Long

Set rst = CurrentDb.OpenRecordset("qryOrders")
vArray = rst.GetRows(1000)

txtOutput = ""
For vRow = 0 To UBound(vArray, 2)
For vCol = 0 To UBound(vArray, 1)
txtOutput = txtOutput & Nz(vArray(vCol, vRow), 0) & " "
Next
txtOutput = txtOutput & vbCrLf
Next

You can get the column headers using

Set rst = CurrentDb.OpenRecordset("qryOrders")
For vCol = 0 To (rst.Fields.Count - 1)
Debug.Print rst.Fields(vCol).Name
Next vCol

 

[Peter Hibbs]

Douglas,

Thanks very much, that is just what I wanted.

Peter Hibbs.