AccessVBA 元データにグループ毎の集計結果（演算結果）を挿入する（配列変数）
スポンサーリンク
元データのグループごとの合計値を元データに挿入する。
大量レコードを処理する場合は動作が遅くなるようだが（1000〜2000レコードくらいまで？）、配列変数を使って作成してみる。
＜元データ＞
値が全て0になっている総数のフィールドに商品ごとの合計値を挿入する
＜完成後のイメージ＞
商品ごとの合計値が総数フィールドに追加された。
以下、VBAの記述。
'----------------------------------------------------------------------------------------
’下段のFunction createTotalCount プロシージャから呼ばれる
’配列変数とレコード数を返す
'配列数は未定のため、()で記載
Private Sub createArray(varArray() As Variant, cnt As Integer)
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim i As Integer
Set db = CurrentDb
’グループごとの合計値をSQLで算出する
strSQL = "SELECT originTbl.商品, Sum(originTbl.数量) AS 総数 FROM originTbl "
strSQL = strSQL & "GROUP BY originTbl.商品;"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.EOF Then
cnt = -1
Exit Sub
End If
rst.MoveLast
'一次元の配列数を抽出
cnt = rst.RecordCount
rst.MoveFirst
'配列の次元ごとの配列数は設定する必要がある
ReDim varArray(cnt, 1) As Variant
i = 0
Do Until rst.EOF
varArray(i, 0) = rst!商品
varArray(i, 1) = rst!総数
i = i + 1
rst.MoveNext
Loop
rst.Close
db.Close
Set db = Nothing
End Sub
'合計値を挿入するメインのプロシージャ
Public Function createTotalcnt()
Dim db As Database
Dim rst As Recordset
Dim varArray() As Variant
Dim cnt As Integer
Dim strSQL As String
Dim i As Integer
'グループごとの合計値を格納する配列変数作成へ
’戻り値は配列と一次元の配列数（レコード数）
Call createArray(varArray(), cnt)
If cnt = -1 Then
Exit Function
End If
Set db = CurrentDb
strSQL = "SELECT originTbl.* FROM originTbl;"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rst.EOF
rst.Edit
’配列変数のKeyCodeと一致する場合、総数フィールドに値を挿入
’配列数までFor〜Nextで繰り返し処理
For i = 0 To cnt
If rst!商品 = varArray(i, 0) Then
rst!総数 = varArray(i, 1)
Exit For
End If
Next i
rst.Update
rst.MoveNext
Loop
rst.Close
db.Close
Set db = Nothing
End Function
'-------------------------------------------------------------------------------------
以上、VBA終わり。
