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                             |
+---------+--------------------------------------+-----------------------------+---------------------------------+

Awaken

Can you edit your post and give us a example of Before vs After? I just want to make sure I understand your question. Also, how do you use the results of this query? Is it copied out to Excel, is it the data source for a form/report, etc.? – Eudy

PowerUser: The short explanation of this case that, yes, this dataexported to excel. My user is given the data, they update it, and then their data is copied back into a table. The columns of the table that the updates are copied into is created by another crosstab, except the headings are the data from the System Data field. Since the export table is sorted by a description field, and the import table is sorted by another field, the columns never align when I copy-and-past from excel. That's what I ultimately need to fix. This would work IF I could sort my column in the original crosstab. – Awaken

Regardless of the point of my question, I do think sorting the columns should be doable, some how. If a query, like a crosstab query, creates dynamic columns, there is no reason why you shouldn't be able to define a sort. I'm just saying, sorting is quite important with data, and defining criteria to do it is ubiquitous. There may not be an elegant solution, but there should some form of a solution. Anyway, I'll post sample data in a second in a few minutes. Let me define some simplistic, dummy data and I'll post it. – Awaken

PowerUser: I've added sample output. I made the outputted column headings really long because, well, they kind of are. My users need a good description of the context of each field. It's a bit unfortunate but it's necessary. – Awaken

So you need the output sorted by Table1.Key and not Table1.Description... when making the crosstab, can you use Table1.Key for the columns instead of Table1.Description? – Eudy

No, the reason is because the actual data is a bit confusing to the users (they actually never see these values in this system) and the description field is already pretty long. As mentioned, I could append the key to the beginning of the field but this is the least optimal solution. I may have to settle for that but I believe others have solved this problem. Some people have posted comments on blogs saying "Oh, I just did X, Y, & Z to solve the problem..." However, their explanations are so short that I can't figure out what they are trying to communicate. A solution exists, but what! – Awaken

Create an other query on your crosstab query and arrange your field the way you want – Hippocrene

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.

Identify

Answer 2

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

Eudy

Answer 3

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.

Spohr