この記事でやること。
- 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のありがたみを実感した。