How to sort columns in a crosstab when column data is dynamic?

Question

I am doing a little research on this topic, and I can not find a single suitable solution, or what is explained quite well for me.

If you've ever created a crosstab query in Access, you know that by default, Access sorts your columns in alphabetical order. You can change this order by going to the Properties dialog and entering Column Headers in the order you prefer. This is a real pain, but, as one respondent said on another site: "This is only pain!"

Well ... this is not the case if your columns are dynamic. In my case, I have a second column in the table that contains the column headers that I would like to use in this field for sorting. I guess I could add the details of the sort column before the description column (which was suggested elsewhere), but I don’t feel that this is the most elegant way to solve the problem. This is especially a problem since the sorting information is system data and is useless to the end user of the crosstab.

Does anyone know of a solution to this problem? If so, can you describe the steps for sorting the dynamic columns of a crosstab?

I think the problem is constant in all versions of Access that are widely used (Access 2003+), but in case it matters, I use Access 2010.

---

UPDATE

Here are some very simplified examples of data that help express the problem. There are several other difficulties surrounding my live script, but this dataset certainly makes sense.

Table # 1 This is where the headers come from. Key is the sorting for the order of the columns, and Descriptions is the displayed title 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 P_Key + F_Key data P_Key + F_Key is unique, and two are the primary key in 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 They are exported to Excel for user updates.

 +---------+---------------------------------+--------------------------------------+-----------------------------+ | 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, I need it to look like this is the table below, which is sorted by key in Table #1 , not 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

Faced with the same scenario everywhere, I prepared a repeatable way to add an In list to the end of a PIVOT clause. This will sort the columns in the crosstab query by the order of the items in the pivotfield In list. The documentation for this design is available from MSDN . A solution is a procedure that is launched using a command button on a form or other event. Please view screenshots under 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 screenshot below, pay attention to tblKeyDescriptions and tblPFValues. This is from the question. qryCrosstab_Initial is similar to the query specified in the above question. The form is used to start the procedure and open requests before and after.

An integer field (NumericIndexForSorting) has been added to tblKeyDescriptions because it requires a numerical index to sort the column names.

Now check the In list highlighted in the SQL view of the source and sorted queries.

This is all that is needed to arrange the columns in the crosstab. Dynamically creating an In list is the purpose of a unit.

Note. A routine must be run every time a query is executed, so it is useful to use an event, such as the On Click command button, to bind a sequence together.

Answer 2

Here is a less perfect solution using some Access and Excel:

• When creating a crosstab, use Table1.Key for columns.
• On a new tab (name it "Lookup"?) In the Excel file, make table # 1
• On the first line of the main tab (i.e. where the data set is inserted) in your Excel file, create a bunch of Vlookup () formulas to view line # 2 and display the correct description from your lookup table.
• Insert the data set into row # 2. The result will be the table below, where the first row is actually a group of Vlookups that invoke the correct description of the column.
• Ask the user to simply ignore or delete line # 2.

I don’t know how complicated your script is, but if this data is inserted into an Excel file using automation, you can just hide line 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 | +---------+--------------------------------------+-----------------------------+---------------------------------+ 

Answer 3

I will just solve this problem. Although the proposed solution works (as described), another possible solution is to create a query-definition "on the fly" (as in the described solution), but instead of changing the SQL query, simply fill in the property of the header columns of the query dynamically created row of column headers. OP already mentions the work of column headers, but it isn’t easier to do and does not contain anything but some vba to list the possible values ​​in a row and set the property

• create crosstab querydef
• list column headings in a row
• set property

So I'm going to try this and see if it works / maybe

well .... it turns out that headheadings is not an actual querydef property, but it is automatically placed in SQL.

I thought I had a bright moment;)