Accessシステムを構築するのに、データの正規化は重要ですが、どうやって蓄積されたデータを活用したらいいか分からない人もいるかもしれません。
今回は、このような悩みを抱えている方に向けて、問題解決につながるような提案を行います。
正規化されたデータベースを構築し、データを読み取りやすく整理することは当然大切ですが、それだけではデータを有効活用できません。
データを有効活用する方法を解説した情報(書籍やサイト)はほとんど見かけませんが、本記事ではそんな情報を提供していきます。
最後まで目を通すと「こんな簡単な方法だったのか」と感じられるかもしれません。
しかし、Access初心者の方にとっては、きっと大きな一歩を踏み出せるきっかけになるでしょう。
このシリーズは2回分かれますが、前編にあたる今回はより難易度が低い方法を解説していきます。
Accessでバッチ処理を行うための基本的な手順
まず、正規化された入力テーブルだけでは、蓄積されたデータに価値を見出すことはできません。
そのために、出力用のテーブルを作成し、いろいろな方法でデータを貼り付けることで、データに付加価値を持たせることができます。
データを出力テーブルに貼り付ける方法は、VBAコードで複数のアクションクエリを連鎖させるだけです。
アクションクエリをオブジェクトに残したままにしておくと、メンテナンス性が低下するので、VBAコードやコードマスタに記録するなどの工夫を行っています。
過去記事では、よりかみ砕いて解説していますので、そちらもあわせてごらんください。
では、実際にサンプルデータベースを使って実演していきます。
サンプルデータベースは下のリンクからダウンロードしてください。
≫ サンプルデータベース(Batch_Processing.accdb)
サンプルデータベースの動作
サンプルデータベースのフォーム「F04月別カテゴリ集計M」を起動します。
「バッチ集計1」ボタンをクリックします。
バッチ処理で作成された出力データが表示されました。
「金額_前月」と「前月対比」はバッチ処理で貼り付けられたデータです。
確認のため、小分類「クロス」でフィルタをかけます。
フィールド「金額_前月」が正しいのを確認できます。
「集計データ削除」をクリックすると、データがクリアされます。
「バッチ集計2」ボタンをクリックしても同処理動作になるのですが、コードマスタを活用した難易度高めの方法なので次回で触れていきます。
RunSQLメソッドでバッチ処理を行うVBAコード
RunSQLメソッドをそのまま使ったバッチ処理のコードは以下の通りです。
Private Sub バッチ集計1_Click()
DoCmd.SetWarnings False
'集計テーブルのデータクリア
DoCmd.RunSQL ("DELETE * FROM T04月別小分類集計;")
'月別集計データを追加
DoCmd.RunSQL ("INSERT INTO T04月別小分類集計 ( 年月, 小分類名, 中分類名, 大分類名, 金額 )" & _
" SELECT Format([発注日],'yyyymm') AS 年月, T2.小分類名, T2.中分類名, T2.大分類名, Sum([数量]*[単価]) AS 金額" & _
" FROM T02発注伝票 AS T1 INNER JOIN T02発注伝票明細 As T2 ON T1.発注登録No = T2.発注登録No" & _
" GROUP BY Format([発注日],'yyyymm'), T2.小分類名, T2.中分類名, T2.大分類名;")
'前月換算のワークテーブル作成
DoCmd.RunSQL ("SELECT Format(DateSerial(Year([発注日]),Month([発注日])+1,1),'yyyymm') AS 次月, T2.小分類名, T2.中分類名, T2.大分類名, Sum([数量]*[単価]) AS 金額" & _
" INTO WT04前月小分類集計 FROM T02発注伝票 AS T1 INNER JOIN T02発注伝票明細 AS T2 ON T1.発注登録No = T2.発注登録No" & _
" GROUP BY Format(DateSerial(Year([発注日]),Month([発注日])+1,1),'yyyymm'), T2.小分類名, T2.中分類名, T2.大分類名;")
'前月金額を更新
DoCmd.RunSQL ("UPDATE T04月別小分類集計 AS T1 INNER JOIN WT04前月小分類集計 AS WT2 ON (T1.小分類名 = WT2.小分類名) AND (T1.年月 = WT2.次月) SET T1.金額_前月 = [WT2].[金額];")
'前月対比の計算
DoCmd.RunSQL ("UPDATE T04月別小分類集計 SET T04月別小分類集計.前月対比 = IIf([金額_前月]=0,0,[金額]/[金額_前月]);")
'前月換算のワークテーブル削除
CurrentDb.TableDefs.Delete "WT04前月小分類集計"
DoCmd.SetWarnings True
Me.F04月別カテゴリ集計DS.SourceObject = "F04月別カテゴリ集計DS"
End Sub
ごらんのとおり、アクションクエリをRun SQLメソッドで順番に実行しているだけです。
また、バッチ処理を組むときは、以下の2点を忘れないよう気を付けています。
- 最初に出力データを全クリア
- 中間テーブルや中間クエリを全削除
まとめ
今回は、Accessテーブルのデータを思い通りに加工する方法として、バッチ処理の手順を解説しました。
アクションクエリの順番を決め、VBAのRunSQLメソッドを実行するだけでバッチ処理が完了し、希望した並びのデータを手に入れることができます。
RunSQLメソッドを使った方法は、構築手順がシンプルで導入しやすいですが、改修にやや手間がかかります。
少し難易度が高いですが、メンテナンス性をさらに上げるため、コードマスタを使うという手段もあります。
次回の後編は、コードマスタでバッチ処理する方法を解説します。