当列数据是动态的时，如何在交叉表查询中对列进行排序? [英] 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.

如果您曾经在Access中创建过交叉表查询，那么您会意识到，默认情况下，Access会按字母顺序对列进行排序.您可以通过转到属性对话框并按您喜欢的顺序输入列标题来更改此顺序.这确实是一种痛苦，但是，正如一个回答者在另一个网站上提到的那样，这只是一次一次的痛苦！"

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!"

好吧……如果您的栏是动态的，那不是真的.就我而言，我在表上还有第二列，其中包含我想使用该字段进行排序的列标题.我想我可以将我的sort列的详细信息附加到description列的前面(在其他地方已建议)，但是我不认为这是解决问题的最优雅的方法.这尤其是一个问题，因为排序信息是系统数据，对交叉表的最终用户无用.

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?

我认为该问题在所有常用的Access版本(Access 2003+)中仍然存在，但以防万一，我正在使用Access 2010.

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.

更新

这里有一些非常简单的样本数据，可以帮助表达问题.我的实际场景中还存在其他一些复杂问题，但是该数据集无疑可以说明问题.

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.

表#1 这就是标题的来源. Key是列顺序的排序，而Descriptions是交叉表中输出的标题.

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

表#2 :这是数据存储 P_Key + F_Key是唯一的，并且两者都是表上的主键.

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

交叉表结果 这些已导出到Excel以供用户更新.

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

这是Access对这些列进行排序的方式.但是，我需要它看起来像是下表，该表是根据Table #1而不是Description中的键排序的.

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

推荐答案

我多次遇到相同的情况，因此我准备了一种可重复的方法，将In列表添加到PIVOT子句的末尾.这样做将按照 pivotfield 列表中元素的顺序对交叉表查询中的列进行排序. 可从MSDN获得该结构的文档.解决方案是需要由表单上的命令按钮或其他事件触发的过程.请查看Sub下方的屏幕截图.

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

在下面的屏幕截图中，请注意tblKeyDescriptions和tblPFValues.这些来自问题. qryCrosstab_Initial与以上问题中提供的查询类似.该表单用于运行过程并打开之前和之后的查询.

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.

将一个整数字段(NumericIndexForSorting)添加到tblKeyDescriptions中，因为该子项需要一个数字索引来对列名称进行排序.

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

现在，检查在初始查询和排序查询的SQL视图中突出显示的"In"列表.

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

这是对交叉表查询中的列进行排序所需要的全部.动态生成In列表是该子程序的目的.

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.

注意:每次运行查询时都需要运行该子项，因此使用命令按钮On Click事件之类的事件将序列绑定在一起是很有帮助的.

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.

这篇关于当列数据是动态的时，如何在交叉表查询中对列进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！