在Access DB中将字段拆分为多个记录 [英] Split Field Into Multiple Records in Access DB

问题描述

我有一个MS Access数据库，其中有一个名为Field1的字段，其中包含多个用逗号分隔的值.即

I have an MS Access Database which has a Field called Field1 that contains multiple values delimited by commas. i.e.,

Value1,Value 2, Value3, Value 4,Value5

我试图通过将记录复制并将每个值存储在另一个字段中，而不是将这些值拆分到单独的字段中.这样一来，包含一个具有三个值的单元格的记录将被重复三次，每个记录的新字段中所包含的值都将发生变化.例如，

I am trying to split the values not into separate fields, but by duplicating the record and storing each value in another field. This will be such that a record containing a cell with three values will be duplicated three times, with each record varying in the value contained in the new field. For example,

在查询/运行模块之前:

Before query/running module:

+-----------+------------------------+ | App Code | Field1 | +-----------+------------------------+ | AB23 | Value1, Value 2,Value3 | +------------------------------------+

+-----------+------------------------+ | App Code | Field1 | +-----------+------------------------+ | AB23 | Value1, Value 2,Value3 | +------------------------------------+

查询/运行模块之后:

+-----------------------------------------------+ | App Code | Field1 | Field2 | +-----------+------------------------+----------+ | AB23 | Value1, Value 2,Value3 | Value1 | +-----------+------------------------|----------+ | AB23 | Value1, Value 2,Value3 | Value 2 | +-----------+------------------------+----------+ | AB23 | Value1, Value 2,Value3 | Value3 | +-----------+------------------------+----------+

+-----------------------------------------------+ | App Code | Field1 | Field2 | +-----------+------------------------+----------+ | AB23 | Value1, Value 2,Value3 | Value1 | +-----------+------------------------|----------+ | AB23 | Value1, Value 2,Value3 | Value 2 | +-----------+------------------------+----------+ | AB23 | Value1, Value 2,Value3 | Value3 | +-----------+------------------------+----------+

到目前为止，我发现了几个有关将字段分成的问题. 两个，甚至是

So far, I have found several questions about splitting a field into two or even several different fields, but I have not found any solution for splitting the record vertically. Of these solutions, some use queries and others use modules but I am also uncertain of which is most efficient, so I decided to go with a VBA module.

因此，这是到目前为止我发现最有用的VBA模块:

And so, here is the VBA module that I have found to be the most useful so far:

Function CountCSWords (ByVal S) As Integer
      ' Counts the words in a string that are separated by commas.

      Dim WC As Integer, Pos As Integer
         If VarType(S) <> 8 Or Len(S) = 0 Then
           CountCSWords = 0
           Exit Function
         End If
         WC = 1
         Pos = InStr(S, ",")
         Do While Pos > 0
           WC = WC + 1
           Pos = InStr(Pos + 1, S, ",")
         Loop
         CountCSWords = WC
      End Function

      Function GetCSWord (ByVal S, Indx As Integer)
      ' Returns the nth word in a specific field.

      Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
         WC = CountCSWords(S)
         If Indx < 1 Or Indx > WC Then
           GetCSWord = Null
           Exit Function
         End If
         Count = 1
         SPos = 1
         For Count = 2 To Indx
           SPos = InStr(SPos, S, ",") + 1
         Next Count
         EPos = InStr(SPos, S, ",") - 1
         If EPos <= 0 Then EPos = Len(S)
         GetCSWord = Trim(Mid(S, SPos, EPos - SPos + 1))
      End Function

但是我如何在访问查询中使用它来实现上述预期结果?否则，除了查询(即仅使用VBA模块)以外，还有更好的方法得出相同的结论吗?

Yet how could I use this in an Access Query to achieve the aforementioned desired results? Otherwise, is there a better way to come to the same conclusion other than a Query (i.e. solely with a VBA module)?

编辑

请注意，表中的主键是Application Code，而不是自动编号.这个主键是文本的，并且是独特的.为了拆分记录，这将需要复制主键，这很好.

Note that the primary key in the Table is Application Code and not autonumber. This primary key is textual and distinct. In order for a record to be split, this will require the primary key to be duplicated, which is fine.

推荐答案

下面是在Table1中使用Field1和Field2的示例代码

Here's a sample piece of code using Field1, Field2 in your a Table1

Option Explicit

Public Sub ReformatTable()

    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim rsADD       As DAO.Recordset

    Dim strSQL      As String
    Dim strField1   As String
    Dim strField2   As String
    Dim varData     As Variant
    Dim i           As Integer

    Set db = CurrentDb

    ' Select all eligible fields (have a comma) and unprocessed (Field2 is Null)
    strSQL = "SELECT Field1, Field2 FROM Table1 WHERE ([Field1] Like ""*,*"") AND ([Field2] Is Null)"

    Set rsADD = db.OpenRecordset("Table1", dbOpenDynaset, dbAppendOnly)

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    With rs
        While Not .EOF
            strField1 = !Field1
            varData = Split(strField1, ",") ' Get all comma delimited fields

            ' Update First Record
            .Edit
            !Field2 = Trim(varData(0)) ' remove spaces before writing new fields
            .Update

            ' Add records with same first field 
            ' and new fields for remaining data at end of string
            For i = 1 To UBound(varData)
                With rsADD
                    .AddNew
                    !Field1 = strField1
                    !Field2 = Trim(varData(i)) ' remove spaces before writing new fields
                    .Update
                End With
            Next
            .MoveNext
        Wend

        .Close
        rsADD.Close

    End With

    Set rsADD = Nothing
    Set rs = Nothing
    db.Close
    Set db = Nothing

End Sub

编辑

更新示例以生成新的主键

如果必须基于先前的Appcode生成新的AppCode(并且将AppCode假定为文本字段)，则可以使用此示例基于最后的Appcode生成唯一的主键.

If you have to generate a new AppCode based on previous Appcode (AND assumming AppCode is a text field), you can use this example to generate a unique primary key based on last appcode.

Option Explicit

Public Sub ReformatTable()

    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim rsADD       As DAO.Recordset

    Dim strSQL      As String
    Dim strField1   As String
    Dim strField2   As String
    Dim varData     As Variant
    Dim strAppCode  As String
    Dim i           As Integer

    Set db = CurrentDb

    ' Select all eligible fields (have a comma) and unprocessed (Field2 is Null)
    strSQL = "SELECT AppCode, Field1, Field2 FROM Table1 WHERE ([Field1] Like ""*,*"") AND ([Field2] Is Null)"

    ' This recordset is only used to Append New Records
    Set rsADD = db.OpenRecordset("Table1", dbOpenDynaset, dbAppendOnly)

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    With rs
        While Not .EOF

            ' Do we need this for newly appended records?
            strAppCode = !AppCode

            strField1 = !Field1
            varData = Split(strField1, ",") ' Get all comma delimited fields

            ' Update First Field
            .Edit
            !Field2 = Trim(varData(0)) ' remove spaces before writing new fields
            .Update

            ' Add new fields for remaining data at end of string
            For i = 1 To UBound(varData)
                With rsADD

                    .AddNew

                    ' ***If you need a NEW Primary Key based on current AppCode
                    !AppCode = strAppCode & "-" & i

                    ' ***If you remove the Unique/PrimaryKey and just want the same code copied
                    !AppCode = strAppCode

                    ' Copy previous Field 1
                    !Field1 = strField1

                    ' Insert Field 2 based on extracted data from Field 1
                    !Field2 = Trim(varData(i)) ' remove spaces before writing new fields
                    .Update
                End With
            Next
            .MoveNext
        Wend

        .Close
        rsADD.Close

    End With

    Set rsADD = Nothing
    Set rs = Nothing
    db.Close
    Set db = Nothing

End Sub

这篇关于在Access DB中将字段拆分为多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！