この記事でやること。

Access2016をつかって以下を実現する。
1つのフィールドレコード内に区切り文字で複数情報入ってるものを展開する。
その逆のキーとなる値を元に、特定のフィールドを区切り文字で1つにまとめる。

◆NESTのテーブル（１つのセルにカンマで複数情報が含まれている状態）

↑ ↓

◆UNNESTのテーブル（１つのセルに1つの情報で複数行で表現されている状態）

背景

MySQLだとUNNESTやGROUP_CONCATとか使えば簡単にできるけど、Accessにはそんな便利機能がない。
Accessを使う人口少なくてナレッジも落ちてない。
事情によりAccessを使うしかなく、上記のような変換をやる必要がある。
とりあえず今後の自分の備忘録と、同じような事情で悩まされている方の助けにでもなればということで記事を書く。
~~あわよくばもっと効率よくやれるよという熟練のAccessマスターからの救済コメントが欲しい。~~

展開編（NEST⇒UNNEST）

やってることの概要
- 展開前テーブルと展開済みテーブルのRecordsetを取得する。
- 展開前テーブルのRecordsetをループする。
- ループの中で展開対象のフィールドの値をSplitして、要素分展開済みテーブルにレコードを追加する。

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

集約編（UNNEST⇒NEST）

[2020/11/25 New]

もろもろ探していたら神がいた。（パトリックマシューズ氏に多大なる感謝）
- リンク
- ※後日使い方とコードの中身でどんなことをしているか勉強した結果を掲載できたらと思う。

[2021/01/16 New]

マシューズ氏のコード使った方が処理効率がいいので、自分のコードを削除しました。
以下、マシューズ氏のコードより引用と、コード見て学ばせてもらった気づき
- 作成した関数をクエリで呼ぶことできたのね。これは便利。
- 引数のCriteria周りの活用の仕方が参考になった。
- MSならもっと良い感じのライブラリ用意してるだろとおもってたけど、マシューズさんの記事見る感じだとRsで地道に処理するしかなさそうね。

unnest⇒nest

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

最後に

Accessはなかなか癖強いので悩まされる。。。
MySQLのありがたみを実感した。