AccessでのNEST⇔UNNEST

' ---------------------------------------------
' [引数]
' nestTable : NESTのテーブル名
' unnestTable : UNNESTのテーブル名
' targetField : UNNESTする対象のフィールド名
'
' [注意]
' ・NESTテーブルのフィールドに存在するフィールドは全て、UNNESTテーブル内に存在すること。
' ---------------------------------------------
Function unnestField(nestTable As String, unnestTable As String, targetField As String)
    ' 変数定義
    Dim db As DAO.Database
    Dim rsNest As DAO.Recordset
    Dim rsUnnest As DAO.Recordset
    Dim splitItem As Variant
    Dim fld As Field

    Set db = CurrentDb

    ' Recordset取得
    Set rsNest = db.OpenRecordset(nestTable) ' 展開前のテーブル取得
    Set rsUnnest = db.OpenRecordset(unnestTable) ' 展開済のテーブル取得

    ' 展開前のRecordsetが空かどうか確認
    If Not (rsNest.BOF And rsNest.EOF) Then
        rsNest.MoveFirst

        ' 展開前のRecordsetを最終レコードまでループする。
        Do Until rsNest.EOF
            For Each splitItem In Split(rsNest.Fields(targetField).Value, ",")
                rsUnnest.AddNew
                For Each fld In rsNest.Fields
                    rsUnnest.Fields(fld.Name) = fld.Value
                Next fld
                rsUnnest.Fields(targetField) = splitItem
                rsUnnest.Update
            Next splitItem
            rsNest.MoveNext
        Loop
    End If

    ' 終了手続き
    rsUnnest.Close
    Set rsUnnest = Nothing
    rsNest.Close
    Set rsNest = Nothing
    Set db = Nothing
End Function

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)

    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long

    On Error GoTo ErrHandler

    ' Initialize to Null
    DConcat = Null

    ' Build up a query to grab the information needed for the concatenation
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")

    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value

    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF

            ' Initialize variable for this row
            ThisItem = ""

            ' Concatenate columns on this row
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next

            ' Trim leading delimiter
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)

            ' Concatenate row result to function return value
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With

    ' Trim leading delimiter
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)

    GoTo Cleanup

ErrHandler:

    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    DConcat = CVErr(Err.Number)

Cleanup:
    Set rs = Nothing

End Function