时间戳

首页 > 解决方案 > MS-Access 使用 VBA 将可变行值动态转换为可变列值

vba - MS-Access 使用 VBA 将可变行值动态转换为可变列值

问题描述

原始代码链接：MS Access - 将行值转换为列值

我对一个问题的答案没有完全解决，但非常接近。这是在上面的原始代码链接中询问的。它是网络上的单个页面，它实际上解决了将一对多关系列中的多个值以动态方式转置为每个相关值的单行的问题，特别是使用 VBA。这个问题的变体已经在这个网站上被问了十几次，实际上没有一个答案能像 Vlado 所做的那样（回答的用户），这是解决这个问题所必需的。

我采用了 Vlado 在该链接中发布的内容，根据我的需要对其进行了调整，进行了一些基本的清理，解决了所有故障排除和语法问题（甚至删除了一个未使用的声明变量：f As Variant），并发现它几乎一直有效。它正确地生成具有前两列值的表，正确迭代具有标题的变量计数列的正确数量，但无法为每个相关的“多值”填充单元格内的值。很近！

为了达到这一点，我必须注释掉转置函数的db.Execute updateSql部分；倒数第 3 行，倒数第 3 行。如果我不注释掉它，它仍然会生成表，但它会引发运行时错误 3144（UPDATE 语句中的语法错误）并且只创建第一行和所有具有正确标题的正确列（但仍然无效单元格内的值）。下面是来自上面链接的 Vlado 的代码，但根据我的字段名称需要进行了调整，并在定义的两个函数的开头设置变量。第二个功能肯定可以正常工作。

Public Function Transpose()

    Dim DestinationCount As Integer, i As Integer
    Dim sql As String, insSql As String, fieldsSql As String, updateSql As String, updateSql2 As String
    Dim db As DAO.Database, rs As DAO.Recordset, grp As DAO.Recordset
    Dim tempTable As String, myTable As String
    Dim Var1 As String, Var2 As String, Var3 As String, Var4 As String

    tempTable = "Transposed"        'Value for Table to be created with results
    myTable = "ConvergeCombined"    'Value for Table or Query Source with Rows and Columns to Transpose
    Var1 = "Source"                 'Value for Main Rows
    Var2 = "Thru"                   'Value for Additional Rows
    Var3 = "Destination"            'Value for Columns (Convert from Rows to Columns)
    Var4 = "Dest"                   'Value for Column Name Prefixes

    DestinationCount = GetMaxDestination
    Set db = CurrentDb()
    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tempTable & "'")) Then
        DoCmd.DeleteObject acTable, tempTable
    End If

    fieldsSql = ""
    sql = "CREATE TABLE " & tempTable & " (" & Var1 & " CHAR," & Var2 & " CHAR "
    For i = 1 To DestinationCount
        fieldsSql = fieldsSql & ", " & Var4 & "" & i & " INTEGER"
    Next i
    sql = sql & fieldsSql & ")"
    db.Execute (sql)

    insSql = "INSERT INTO " & tempTable & " (" & Var1 & ", " & Var2 & ") VALUES ("
    Set grp = db.OpenRecordset("SELECT DISTINCT " & Var1 & ", " & Var2 & " FROM " & myTable & " GROUP BY " & Var1 & ", " & Var2 & "")
    grp.MoveFirst

    Do While Not grp.EOF
        sql = "'" & grp(0) & "','" & grp(1) & "')"
        db.Execute insSql & sql
        
        Set rs = db.OpenRecordset("SELECT * FROM " & myTable & " WHERE " & Var1 & " = '" & grp(0) & "' AND " & Var2 & " = '" & grp(1) & "'")
        updateSql = "UPDATE " & tempTable & " SET "
        updateSql2 = ""
        i = 0
        rs.MoveFirst

        Do While Not rs.EOF
            i = i + 1
            updateSql2 = updateSql2 & "" & Var3 & "" & i & " = " & rs(2) & ", " ' <------- MADE CHANGE FROM (3) to (2)
            rs.MoveNext
        Loop

        updateSql = updateSql & Left(updateSql2, Len(updateSql2) - 1) & " WHERE " & Var1 & " = '" & grp(0) & "' AND " & Var2 & " = '" & grp(1) & "'"
        db.Execute updateSql ' <-- This is the point of failure
        grp.MoveNext
    Loop
End Function

Public Function GetMaxDestination()

    Dim rst As DAO.Recordset, strSQL As String
    myTable = "ConvergeCombined"    'Value for Table or Query Source with Rows and Columns to Transpose
    Var1 = "Source"                 'Value for Main Rows
    Var2 = "Thru"                   'Value for Additional Rows
    Var3 = "Destination"            'Value for Columns (Convert from Rows to Columns)

    strSQL = "SELECT MAX(CountOfDestination) FROM (SELECT Count(" & Var3 & ") AS CountOfDestination FROM " & myTable & " GROUP BY " & Var1 & ", " & Var2 & ")"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    GetMaxDestination = rst(0)
    rst.Close
    Set rst = Nothing
End Function

样品表：

样品表

样本数据：

样本数据

标签： vbams-accessdynamic-programmingone-to-manytranspose

解决方案

在该 Execute 行之前添加一个Debug.Print updateSql，将在 SQL 语句中看到不正确的语法。需要从 updateSql2 字符串中修剪尾随逗号。代码附加了一个逗号和空格，但只修剪了 1 个字符。从连接中消除空格或修剪 2 个字符。
Left(updateSql2, Len(updateSql2) - 2)

updateSql2 的连接使用 Var3 而不是 Var4。

源字段是 ConvergeCombined 中的数字类型，这会在 SELECT 语句中触发“类型不匹配”错误以打开记录集，因为撇号分隔符Var1 & " = '" & grp(0) & "'- 从两个 SQL 语句中删除它们。

此外，源值被保存到 Transposed 中的文本字段，在 CREATE TABLE 操作中将其设为 INTEGER 而不是 CHAR。

推荐阅读