How can I sort the columns in a crosstab query, when the column data is dynamic?
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
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.