如果列数据是动态的，如何对交叉表查询中的列进行排序？
如果列数据是动态的，如何对交叉表查询中的列进行排序？
提问于 2012-12-13 14:50:34
我一直在对这个主题做一些研究，我似乎既找不到一个可行的解决方案，也找不到一个足以让我实现的解决方案。
如果您曾经在Access中创建过交叉表查询，那么您可以知道，在默认情况下，Access按字母顺序对列进行排序。您可以通过转到Properties对话框并按您喜欢的顺序输入列标题来更改此顺序。这是一个真正的痛苦，但正如一个回答者在另一个网站上提到的，“这只是一次痛苦！”
好吧..。如果您的列是动态的，则不是这样。在我的例子中，我在表中有第二列，其中包含我想要使用该字段进行排序的列标题。我想我可以将排序列的细节附加到description列的前面(它被建议在其他地方)，但我不认为这是解决问题的最优雅的方法。这尤其是一个问题，因为排序信息是系统数据，对交叉表的最终用户是无用的。
有人知道这个问题的解决办法吗？如果是的话，您能详细说明对交叉表查询的动态列进行排序的步骤吗？
我认为这个问题在所有常用的Access (Access 2003+)版本中都是持续存在的，但是为了防止发生变化，我正在使用Access 2010。
更新
下面是一些非常简单的样本数据，可以帮助表达问题。在我的现场场景中还有一些其他的复杂性，但是这个数据集肯定能让人明白这一点。
表#1这是标题的来源。Key是列顺序的排序，Descriptions是交叉表中输出的标题。
+---------+---------------------------------------+
| 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是唯一的，这两个是表上的主键。
+---------+---------+-------+
| 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 |
+---------+---------+-------+交叉表结果这些输出到中供用户更新。
+---------+---------------------------------+--------------------------------------+-----------------------------+
| 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。
+---------+--------------------------------------+-----------------------------+---------------------------------+
| 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 |
+---------+--------------------------------------+-----------------------------+---------------------------------+回答 2
Stack Overflow用户
回答已采纳
发布于 2012-12-24 19:48:33
在多次遇到相同的场景后，我准备了一种可重复的方法，将In添加到透视子句的末尾。这样做将根据列表中支点字段中元素的顺序对交叉表查询中的列进行排序。有关此构造的文档可从MSDN获得。。解决方案是需要由窗体或其他事件上的命令按钮触发的过程。请看下面的屏幕截图。
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类似于上述问题中提供的查询。该表单用于运行过程并打开“前”和“后”查询。
一个整数字段(NumericIndexForSorting)被添加到tblKeyDescriptions中，因为子字段需要一个数字索引来排序列名。
现在，检查初始查询和排序查询的SQL视图中突出显示的In列表。
这是为交叉表查询中的列排序所需的全部内容。动态生成is是子程序的目的。
注意:每次运行查询时都需要运行子程序，因此使用一个事件(如Click上的命令按钮)将序列绑定在一起是有帮助的。
Stack Overflow用户
发布于 2018-12-28 15:53:10
如果您知道查询的预期结果并能够预测列数，则排序交叉表查询结果的最简单方法是在属性表中的列标题字段中指定正确的顺序。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13862344
复制相关文章
相似问题
腾讯云开发者
