时间戳

首页 > 解决方案 > 将包含多个值的访问字段分隔到具有正确对应数据的唯一行

vba - 将包含多个值的访问字段分隔到具有正确对应数据的唯一行

问题描述

我在 Access 中有一个字段，其中有多个逗号分隔值（字段一的值"1, 2, 3, 4"在第二行1, "2, 2, 3"的行中）。

第一个字段中有1-7（但可以大到1,101）值。

第二个字段将具有与 Field1 的行对应的相同数量的值，

然而，逗号的位置可能很奇怪（我假设这可以在分析此字段时使用修剪或左偏移函数来解决“，” 1, 2, 3）。

我需要的是让成千上万的这些行不再有重复的条目，并且它们每一个都是自己的行。可扩展的解决方案很重要，并且允许使用整个 Microsoft Office 套件。

从一行：

 Field1           Field2

 1, 2, 3, 4      , 1, 1, 4, 4
 2, 2, 3         , 2, 3, 3

输出如下所示：

 Field1  Field2
 1       1       
 2       1
 3       4
 4       4
 2       2
 2       3
 3       3

标签： vbams-access

解决方案

这是你想要的：

Sub GetNumbers()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select Field1, Field2 From Table1")
    Do While Not rs.EOF
        'Get the fields values and 'trim away' any blanks.
        Dim field1 As String
        field1 = Trim(rs("Field1").Value)
        Dim field2 As String
        field2 = Trim(rs("Field2").Value)

        'If the strings now begin with a ',', remove it.
        If Left(field1, 1) = "," Then field1 = Mid(field1, 2)
        If Left(field2, 1) = "," Then field2 = Mid(field2, 2)

        'Split the strings to an array by ','
        Dim arrField1() As String
        arrField1 = Split(field1, ",")
        Dim arrField2() As String
        arrField2 = Split(field2, ",")

        'Loop the arrays (important is that the number of elements is equal in both arrays. This depends on your data!)
        Dim index As Long
        For index = LBound(arrField1) To UBound(arrField1)
            'Output the values and again 'trim away' any blanks
            Debug.Print Trim(arrField1(index)), Trim(arrField2(index))
            'Here you also can store the values to another table if you want/need.
            'If the new field is a text field:
            'DoCmd.RunSQL "Insert Into Table2 (Field1, Field2) Values ('" & Trim(arrField1(index)) & "', '" & Trim(arrField2(index)) & "')"
            'If the new field is a number field:
            'DoCmd.RunSQL "Insert Into Table2 (Field1, Field2) Values (" & Trim(arrField1(index)) & ", " & Trim(arrField2(index)) & ")"
        Next index
        rs.MoveNext
    Loop
End Sub

推荐阅读