MS Access批量查询多列最大值对应的Position位置实现方案

方案1：VBA自动生成结果表（推荐，适配40+列场景，无需手动维护列名） #

无需逐个写列查询逻辑，自动遍历所有数据列计算对应结果，新增数据后重新运行即可更新结果，不会出现旧方案的结果错误问题。
操作步骤：

1. 打开Access文件后按Alt+F11唤起VBA编辑器，右键点击当前数据库名称-插入-模块，粘贴如下代码：

Sub GetMaxPosForAllCols()
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim resultRs As DAO.Recordset
    Dim maxVal As Variant
    Dim posVal As Long
    
    ' 删已存在的结果表避免重复创建报错
    On Error Resume Next
    CurrentDb.Execute "DROP TABLE ColMaxPositionResult", dbFailOnError
    On Error GoTo 0
    
    ' 新建结果表，存储列名、对应最大值、对应Position
    CurrentDb.Execute "CREATE TABLE ColMaxPositionResult (ColumnName TEXT(255), MaxPosition LONG, MaxValue DOUBLE)", dbFailOnError
    Set resultRs = CurrentDb.OpenRecordset("ColMaxPositionResult")
    
    ' 打开原数据表
    Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
    
    ' 遍历所有字段
    For Each fld In rs.Fields
        ' 跳过Position字段，只处理数据列
        If fld.Name <> "Position" Then
            ' 动态获取当前列的最新最大值
            maxVal = DMax(fld.Name, "Table1")
            ' 查找最大值对应的Position，有多个相同最大值时默认返回第一个匹配项
            posVal = DLookup("Position", "Table1", fld.Name & " = " & Nz(maxVal, 0))
            
            ' 写入结果表
            resultRs.AddNew
            resultRs!ColumnName = fld.Name
            resultRs!MaxPosition = posVal
            resultRs!MaxValue = maxVal
            resultRs.Update
        End If
    Next
    
    ' 释放资源
    rs.Close
    resultRs.Close
    Set rs = Nothing
    Set resultRs = Nothing
    Set fld = Nothing
    
    MsgBox "计算完成，结果已存入ColMaxPositionResult表"
End Sub

2. 按F5运行代码即可得到结果表。

• 提示：如果列名包含空格或特殊字符，把代码里的fld.Name替换为"[" & fld.Name & "]"即可；如果列是文本/日期类型，对应修改结果表的MaxValue字段类型、调整DLookup的条件引号格式即可。

方案2：动态生成联合查询SQL（无需长期依赖VBA） #

如果希望用查询而非VBA代码存结果，可以运行下面的VBA生成一次性SQL，之后直接刷新查询即可更新结果：

Sub GenerateUnionSQL()
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim sqlStr As String
    
    Set rs = CurrentDb.OpenRecordset("Table1")
    sqlStr = ""
    
    For Each fld In rs.Fields
        If fld.Name <> "Position" Then
            If sqlStr <> "" Then sqlStr = sqlStr & " UNION ALL "
            sqlStr = sqlStr & "SELECT '" & fld.Name & "' AS ColumnName, Position AS MaxPosition, " & fld.Name & " AS MaxValue FROM Table1 WHERE " & fld.Name & " = DMax('" & fld.Name & "', 'Table1')"
        End If
    Next
    
    ' 按Ctrl+G打开立即窗口即可复制生成的SQL
    Debug.Print sqlStr
    rs.Close
    Set rs = Nothing
    Set fld = Nothing
End Sub

把生成的SQL复制后，新建查询粘贴SQL语句运行即可得到结果，新增数据后直接刷新查询就能拿到最新值。

内容的提问来源于stack exchange，提问作者Rod Parker