動的に見出しが変わるクロス集計クエリの作成

例えばAccessに毎日ストックしている情報を月単位で集計するなどの処理には、クロス集計クエリが便利です。
このとき、報告書などフォーマルな書類に流用するにあたっては、例えば実績のなかった月もあえて表示させるなど、「クエリ列見出し」で表示する列を固定する必要があります。

ときに、例えば年度ごと・月単位の集計を定型業務で行っているなどの場合には、処理対象の年度が変わるとクエリ列見出しも修正が必要です。
中には年度が改まるごとに新しいクエリを定義したり、クエリ列見出しを設定しなおしておられる方も多いと思います。

筆者の職場でも、以前は年度替わりのタイミングでちまちまとクエリ列見出しを修正していたんですが、対象の数が多いのでとにかく大変やし、何より年度始末のバタバタした時期なので、うっかり修正を忘れてしまう・・・などの問題が発生していました。
そこでいろいろ試行錯誤した結果、自動的にクエリ列見出しを更新する仕組みを作れたので、以下にご紹介します。

---

1. 処理の概要

処理手順は、ざっくり次の通りです。
以下、テーブル"tTr_台数集計"に格納されている値「台数」を年月ことに集計して、その結果をワークテーブル"tWk_台数集計"に格納する例です。

1)ベースになるクロス集計クエリを作成
　⇒SQLを抜き出し
2)指定年月をもとに、VBAでクエリ列見出しの文字列を作成
3)1)で抜き出したSQLの、クエリ列見出しの部分に2)の文字列を設定
　⇒クロス集計クエリを一時保存
4)集計結果格納用のテーブルをドロップ
5)3)のクロス集計をもとに、テーブル作成クエリを実行
　⇒集計結果を新規テーブルに格納
6)1)で退避していたSQLをクロス集計クエリに戻し、保存

以下に、具体的な内容を記します。

---

2. 処理の詳細

1)クロス集計クエリのSQL抜き出し

文字通りです。
後で戻すことも考慮して、もともとの値も退避しておきます。

Dim qd1 As DAO.QueryDef
Dim kep_sql As String
Set qd1 = CurrentDb.QueryDefs("qCr_台数集計")        ' ⇒処理対象のクロス集計クエリ
kep_sql = qd1.SQL

このとき、該当のクロス集計クエリのSQLは次のようになっているものとします。

クロス集計クエリ"qCr_台数集計"

TRANSFORM Sum(tTr_台数集計.台数) AS 台数の合計
SELECT tTr_台数集計.大区分, tTr_台数集計.小区分
FROM tTr_台数集計
GROUP BY tTr_台数集計.大区分, tTr_台数集計.小区分
PIVOT Format$([基準年], "0000") & "/" & Format$([基準月], "00");

---

2)指定年月をもとに、VBAでクエリ列見出しの文字列を作成

指定した年月の属する年度の各年月＋前年度の各年月の見出し文字列を作成します。
あちこちで使いまわすので、次のような共通関数にしました。

fPb_固定列見出し作成

Public Function fPb_固定列見出し作成 _
    pVl_基準年 As Variant, _
    pVl_基準月 As Variant _
    ) As Strinig

    ' ***** 指定年月を含む過去２年度分の列見出し文字列を作成 *****
    Dim 年月文字列 As String, 月 As Long
    年月文字列 = ""
    If pVl_基準月 <= 3 Then
        For 月 = 4 To 12
            年月文字列 = 年月文字列 & Chr(34) & Format$(pVl_基準年 - 2, "0000") & "/" & Format$(月, "00") & Chr(34) & ","
        Next 月
        For 月 = 1 To 12
            年月文字列 = 年月文字列 & Chr(34) & Format$(pVl_基準年 - 1, "0000") & "/" & Format$(月, "00") & Chr(34) & ","
        Next 月
        For 月 = 1 To 3
            年月文字列 = 年月文字列 & Chr(34) & Format$(pVl_基準年, "0000") & "/" & Format$(月, "00") & Chr(34) & ","
        Next 月
    Else
        For 月 = 4 To 12
            年月文字列 = 年月文字列 & Chr(34) & Format$(pVl_基準年 - 1, "0000") & "/" & Format$(月, "00") & Chr(34) & ","
        Next 月
        For 月 = 1 To 12
            年月文字列 = 年月文字列 & Chr(34) & Format$(pVl_基準年, "0000") & "/" & Format$(月, "00") & Chr(34) & ","
        Next 月
        For 月 = 1 To 3
            年月文字列 = 年月文字列 & Chr(34) & Format$(pVl_基準年 + 1, "0000") & "/" & Format$(月, "00") & Chr(34) & ","
        Next 月
    End If
    fPb_固定列見出し作成 = Left$(年月文字列, Len(年月文字列) - 1)

End Function

---

3)SQLクエリ列見出しを設定

1)のクエリ"qCr_台数集計"に、2)で作成した列見出しを付加します。

1)のVBAコードの続き

    ' 最後のセミコロンを消してSQL文字列をつなげる
    sqlstr = Replace(qd1.SQL, ";", "") & " In (" & fPb_固定列見出し作成(基準年, 基準月) & ");"
    qd1.SQL = sqlstr

結果的にSQLは次のようになります。

TRANSFORM Sum(tTr_台数集計.台数) AS 台数の合計
SELECT tTr_台数集計.大区分, tTr_台数集計.小区分
FROM tTr_台数集計
GROUP BY tTr_台数集計.大区分, tTr_台数集計.小区分
PIVOT Format$([基準年], "0000") & "/" & Format$([基準月], "00")
In ("2022/04", "2022/05", ・・・・, "2024/03");

---

4)集計結果格納用のテーブルをドロップ

文字通りです。
Accessに「テーブルのドロップクエリー」なるものはないので、空のQueryDefでドロップしてあげます。

    Dim qd2 As QueryDef
    Set qd2 = CurrentDb.CreateQueryDef("", "DROP TABLE tWk_台数集計")
    qd2.Execute

---

5)クロス集計をもとにテーブル作成クエリを実行⇒集計結果をテーブルに格納

あらかじめ、次のようなテーブル作成クエリを定義しておきます。
カラム名を指定せず"*"で済ませているところがミソです。

テーブル作成クエリ"qCt_台数集計"

    SELECT qCr_台数集計.* INTO tWk_台数集計
    FROM qCr_台数集計;

4)までの処理が終わったら、このテーブル作成クエリを実行します。
これで、列見出しが動的に作成された集計結果がテーブル"tWk_台数集計"に格納されます。

    Set qd2 = CurrentDb.QueryDefs("qCt_台数集計")
    qd2.Execute

---

6)退避していたSQLをクロス集計クエリに戻し、保存

もともとのクロス集計クエリも使いまわすので、1)で退避していたオリジナルのSQLを戻してあげて終わります。

    qd1.SQL = kep_sql
    Set qd1 = Nothing