access两字段同时升序排序_ms-access – 当列数据是动态的时,如何对交叉表查询中的列进行排序？...

在不同时间遇到相同的场景后,我准备了一种可重复的方法,将In列表添加到PIVOT子句的末尾.这样做将按照pivotfield In列表中元素的顺序对交叉表查询中的列进行排序.

Documentation for this construct is available from MSDN.解决方案是需要由表单或其他事件上的命令按钮触发的过程.请参阅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类似于上述问题中提供的查询.该表单用于运行该过程并打开前后查询.

整数字段(NumericIndexForSorting)被添加到tblKeyDescriptions,因为sub需要一个数字索引来对列名进行排序.

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

这就是在交叉表查询中对列进行排序所需的全部内容.动态生成In列表是sub的目的.

注意：每次运行查询时都需要运行sub,因此使用诸如命令按钮On Click事件之类的事件将序列绑定在一起是有帮助的.