How can I sort the columns in a crosstab query, when the column data is dynamic?

Question

I've been doing a bit of research on this topic and I can't seem either find a workable solution, or one that is explained well enough for me to implement.

If you've ever created a crosstab query in Access, you are aware that by default Access sorts your columns in alphabetic order. You can change this order by going to the Properties dialog and entering the Column Headings in the order that you prefer. This is a real pain but, as one answerer mentioned on another site, "It's only a pain once!"

Well... this isn't true if your columns are dynamic. In my case, I have a second column on the table that contains the column headings that I'd like to use that field for sorting. I guess I could append the details of my sort column, to the front of the description column (which has been suggested else where) but I don't feel that this is the most elegant means of solving the problem. This is especially an issue since the sort information is system data and useless to the end-user of the crosstab.

Does anyone know of a solution to this problem? If so, could you spell out the steps to sort the dynamic columns of a crosstab query?

I think the problem is persistent across all versions of Access that are in common use (Access 2003+) but just in case it makes a difference, I am using Access 2010.

---

UPDATE

Here is some very simplistic, sample data that helps express the problem. There are a few other complexities surrounded around my live scenario but this data set certainly gets the point across.

Table #1 This is where the headings come from. The Key is the sort for the column order, and the Descriptions is the outputted heading in the crosstab.

+---------+---------------------------------------+
| Key     | Descriptions                          |
+---------+---------------------------------------+
| Kfsg2E  | Hey, this is accounting code X!       |
+---------+---------------------------------------+
| abR3    | This is yet another accounting code!  |
+---------+---------------------------------------+
| Gruu!   | Yet another accounting code           |
+---------+---------------------------------------+

Table #2 This is the store of data P_Key + F_Key is unique and the two are a primary key on the table.

+---------+---------+-------+
| P_Key   | F_Key   | Value |
+---------+---------+-------+
| 1001    |Kfsg2E   | 1.0   |
+---------+---------+-------+
| 1001    |abR3     | 1.1   |
+---------+---------+-------+
| 1001    |Gruu!    | 1.2   |
+---------+---------+-------+
| 1002    |Kfsg2E   | 2.0   |
+---------+---------+-------+
| 1002    |abR3     | 2.1   |
+---------+---------+-------+
| 1002    |Gruu!    | 2.2   |
+---------+---------+-------+
| 2001    |Kfsg2E   | 3.0   |
+---------+---------+-------+
| 2001    |abR3     | 3.1   |
+---------+---------+-------+
| 2001    |Gruu!    | 3.2   |
+---------+---------+-------+

Crosstab Results These are exported to Excel for the user to update.

+---------+---------------------------------+--------------------------------------+-----------------------------+
| P_Key   | Hey, this is accounting code X! | This is yet another accounting code! | Yet another accounting code |
+---------+---------------------------------+--------------------------------------+-----------------------------+
| 1001    | 1.0                             | 1.1                                  | 1.2                         |
+---------+---------------------------------+--------------------------------------+-----------------------------+
| 1001    | 2.0                             | 2.1                                  | 2.2                         |
+---------+---------------------------------+--------------------------------------+-----------------------------+
| 1001    | 3.0                             | 3.1                                  | 3.2                         |
+---------+---------------------------------+--------------------------------------+-----------------------------+

This is how Access sorts these columns. However, what I need it to look like is the table below, which is sorted off of the key in Table #1, rather than Description.

+---------+--------------------------------------+-----------------------------+---------------------------------+
| P_Key   | This is yet another accounting code! | Yet another accounting code | Hey, this is accounting code X! |
+---------+--------------------------------------+-----------------------------+---------------------------------+
| 1001    | 1.1                                  | 1.2                         | 1.0                             |
+---------+--------------------------------------+-----------------------------+---------------------------------+
| 1001    | 2.1                                  | 2.2                         | 2.0                             |
+---------+--------------------------------------+-----------------------------+---------------------------------+
| 1001    | 3.1                                  | 3.2                         | 3.0                             |
+---------+--------------------------------------+-----------------------------+---------------------------------+

Answer 1

Having encountered the same scenario various times, I prepared a repeatable way to add an In list to the end of the PIVOT clause. Doing so will sort the columns in the crosstab query by order of the elements in the pivotfield In list. Documentation for this construct is available from MSDN. The solution is a procedure that needs triggered by a command button on a form or another event. Please see the screen shots below the Sub.

Public Sub SortPivotColumns(querynameSource As String, queryname As String, SortName As String, SortColumnNameField As String, SortIndexName As String, NonPivotFieldCount As Integer, ParamArray ParamArr() As Variant)

' This sub goes through several steps to effectively adds an In list that sorts the 'Columns' of a crosstab query in MS Access
' 13 November 2012
' E Easterly
'
' This technique uses several components.
' 1) The original unmodified cross tab query (querynameSource)
' 2) The resulting, columns-have-been-sorted query (query)
' 3) An index table which has two columns, a numeric index used for sorting and the column name
' 4) A table or query that can be joined on the column names of the cross tab query to update the index table
'    The name of the table or query would be 'SortName'
'    The field in 'SortName' that the crosstab query columns are joined against is the 'SortColumnNameField'
'    The field in 'SortName' that has the desired order is the SortIndexName
' 5) A number which specifies the count of non-pivot/row heading columns (NonPivotFieldCount)
' 6) An optional array that contains any parameters needed for the query
'
'
'   USE:
'
'   SortPivotColumns "qryCrosstab_Initial", _
'                 "qryCrosstab_Sorted", _
'                 "tblKeyDescriptions", _
'                 "Descriptions", _
'                 "NumericIndexForSorting", _
'                  1
'
'
'
'
Dim rs As DAO.Recordset
Dim db As Database
Dim fld As DAO.Field
Dim sql As String
Dim ColumnHeading As Variant
Dim qdf As QueryDef
Dim qdfSRC As QueryDef
Dim UpdateIndexSQL As Variant

DoCmd.SetWarnings False 'Turn off warnings

Set db = CurrentDb

Set qdfSRC = db.QueryDefs(querynameSource)
Set qdf = db.QueryDefs(queryname)
qdf.sql = qdfSRC.sql

If Not (IsEmpty(ParamArr)) Then
    Dim i As Integer
    For i = 0 To UBound(ParamArr)
        qdf.Parameters(i) = ParamArr(i)
    Next
End If


' First, get the list of fields from the query

Set rs = qdf.OpenRecordset

' Then, create a temporary indexing table
If Not IsNull(DLookup("Name", "MSysObjects", "Name='ttblSortCrosstabColumns' And Type In (1,4,6)")) Then
    db.Execute "DROP TABLE ttblSortCrosstabColumns"
End If

db.Execute "CREATE TABLE ttblSortCrosstabColumns (FieldIndex INTEGER , ColumnName TEXT(250))"

' And populate it with the current index and column names from queryname
  For Each fld In rs.Fields
    If fld.OrdinalPosition > (NonPivotFieldCount - 1) Then
        DoCmd.RunSQL "Insert into ttblSortCrosstabColumns VALUES(" & fld.OrdinalPosition & ", """ & fld.Name & """)"
    End If
  Next fld
  Set fld = Nothing
  rs.Close
  Set rs = Nothing


' Now, the temporary table is joined with the sort table/query and the indexes are updated
UpdateIndexSQL = ("  UPDATE ttblSortCrosstabColumns " & _
                  "  INNER JOIN " & SortName & " ON ttblSortCrosstabColumns.ColumnName=" & SortName & "." & SortColumnNameField & _
                  "  Set ttblSortCrosstabColumns.FieldIndex = [" & SortIndexName & "]")
DoCmd.RunSQL (UpdateIndexSQL)


' Then, the column headings are added to a string to prepare the In list
sql = "SELECT ttblSortCrosstabColumns.ColumnName FROM ttblSortCrosstabColumns ORDER BY ttblSortCrosstabColumns.FieldIndex"
Set rs = db.OpenRecordset(sql)
    rs.MoveFirst
    ColumnHeading = "'" & rs.Fields(0).Value & "'"
    rs.MoveNext

    Do While Not rs.EOF
    ColumnHeading = ColumnHeading & ", '" & rs.Fields(0).Value & "'"
    rs.MoveNext
    Loop

rs.Close
Set rs = Nothing
' db.Execute "DROP TABLE ttblSortCrosstabColumns"

Dim cs As Variant

' Set qdf = db.QueryDefs(queryname)   ' may not need this

' The query is updated with the In list
cs = Left$(qdf.sql, Len(qdf.sql) - 3) & " In(" & ColumnHeading & ");"

qdf.sql = cs

' Take a look at the resulting query sql by uncommenting the below section
' Debug.Print cs


DoCmd.SetWarnings True  'Turn warnings back on

End Sub

In the below screen shot, note the tblKeyDescriptions and the tblPFValues. These are from the question. qryCrosstab_Initial is analogous to the query provided in the above question. The form is used to run the procedure and open the before and after queries.

An integer field (NumericIndexForSorting) was added to tblKeyDescriptions because the sub requires a numeric index for sorting the column names.

Now, inspect the In list highlighted in the SQL view of the initial and sorted queries.

This is all that is needed to order the columns in a crosstab query. Dynamically generating the In list is the purpose of the sub.

Note: the sub needs to be run each time the query is run, so using an event such as a command button On Click event to tie the sequence together is helpful.

Answer 2

If you know the expected results of your query and can predict the number of columns, the simplest way to order the results of a crosstab query is to specify the correct order in the Column Headings field in the Property Sheet.

Answer 3

Here is a less than perfect solution that uses some Access & Excel:

1. When making the crosstab, use Table1.Key for the columns.
2. On a new tab (call it "Lookup"?) in the Excel file, make Table#1
3. On the first row of the main tab (i.e. where the dataset is pasted) in your Excel file, create a bunch of Vlookup() formulas to look at row#2 and pull in the correct description from your lookup table.
4. Paste the dataset into row#2. The result will be the table below, where the first row is actually a bunch of Vlookups that pull in the correct column description.
5. Ask the user to just ignore or delete row#2.

I don't know how complex your script is, but if this data is pasted into the Excel file by automation, then you can just hide row#2 and skip step 6.

P_Key	This is yet another accounting code!	Yet another accounting code	Hey, this is accounting code X!
PasteHere	abR3	Gruu!	Kfsg2E
1001	1.1	1.2	1.0
1001	2.1	2.2	2.0
1001	3.1	3.2	3.0