Accessのクロス集計で「列が消える」現象を撲滅せよ！QueryDefでSQLを操る極意
こんにちは。システム開発の現場で日々「自動化」という名の魔法をかけているエンジニアです。
Accessを使っていて、こんな経験はありませんか？
「クロス集計クエリを作ったけど、データがない月があると列ごと消えてしまい、レポートのレイアウトが崩壊する……」
これ、Access開発者が一度は必ずぶつかる「通過儀礼」のような壁です。この問題を「見なかったことにする」のではなく、「プログラムでSQLを書き換えて制御する」というエンジニアの視点を手に入れましょう。
今回は、QueryDef（クエリ定義オブジェクト）を駆使して、クロス集計の列見出しを強固に固定するテクニックを伝授します。
—
なぜ「列」が消えてしまうのか？
クロス集計クエリの仕組みを理解しましょう。Accessのクロス集計は、実行されるたびに「今あるデータ」を見て列を再生成します。「3月」のデータが一つもなければ、SQLは3月という概念を無視して結果を返します。
レポート側は「3月」というテキストボックスを探しているのに、クエリ側に存在しない。結果、エラーや空白が発生します。
これを解決するには、SQLの`PIVOT`句に、「データがなくても必ず表示する列名」を明示的に指定してあげる必要があります。
—
攻略の鍵：QueryDefオブジェクト
Accessのクエリは、単なる保存されたファイルではありません。VBAから見れば、`QueryDef`という「操作可能なオブジェクト」です。この `.SQL` プロパティを直接書き換えることで、クエリを動的に変身させることができます。
実装コード：SQLを動的に書き換える魔法
以下のコードは、指定したクエリのSQLを、あらかじめ定義した列見出しで上書きする関数です。
Public Sub FixCrosstabColumns()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
‘ 固定したい列見出し（カンマ区切り）
Dim strHeaders As String
strHeaders = “‘4月’, ‘5月’, ‘6月’, ‘7月’, ‘8月’, ‘9月’, ’10月’, ’11月’, ’12月’, ‘1月’, ‘2月’, ‘3月'”
Set db = CurrentDb
‘ 対象のクエリ名を取得
Set qdf = db.QueryDefs(“qry_売上クロス集計”)
‘ SQLの骨格を作成（基本の集計ロジック）
‘ PIVOT句の末尾に IN (…) を加えるのがポイントです
strSQL = “TRANSFORM Sum(売上) AS 売上の合計 ” & _
“SELECT 商品名 FROM T_売上 ” & _
“GROUP BY 商品名 ” & _
“PIVOT Format([日付],’m月’) IN (” & strHeaders & “);”
‘ QueryDefのSQLを書き換える
qdf.SQL = strSQL
‘ 後片付け
qdf.Close
Set qdf = Nothing
Set db = Nothing
MsgBox “クエリの列見出しを固定しました！”, vbInformation
End Sub
コードの解説：ここがプロの視点
1. `IN (“4月”, “5月”, …)` の威力:
これが今回の主役です。`PIVOT`句の最後に`IN`句を添えることで、「データがあろうとなかろうと、この順番でこの列を作れ」という強力な命令になります。
2. QueryDefへのアクセス:
`CurrentDb.QueryDefs(“名前”)` を使うことで、保存されているクエリの「設計図」そのものを書き換えています。これにより、レポート側は常に同じ列名を受け取れるため、レイアウトが安定します。
—
陥りやすい罠と解決策
初学者がよくやるミスを先回りして解説します。
- 「クエリが開かれています」エラー:
クエリやレポートを開いたままこのコードを実行すると、Accessは書き込みを拒絶します。処理の冒頭で `DoCmd.Close acQuery, “qry_売上クロス集計”` を入れるなどして、対象を閉じてから書き換えるのが鉄則です。
- 型の不一致:
`PIVOT`句で使うデータ型（日付や数値）と、`IN`句で指定する値の型がズレているとエラーになります。`Format()`関数などで、型をしっかり揃えておくのが安全です。
—
まとめ：自動化エンジニアへの第一歩
今回学んだ「QueryDefのSQLを書き換える」という手法は、クロス集計だけでなく、「ユーザーの入力値に応じて抽出条件をリアルタイムに変える」といった高度な検索フォームを作る際にも応用できる、汎用的なスキルです。
「クエリは固定されているもの」という思い込みを捨て、「クエリすらもプログラムの一部」と捉えられるようになった時、あなたのAccess開発レベルは一段階上のステージへ到達します。
まずはこのコードをコピーして、ご自身の環境で試してみてください。データが消えても動じない、堅牢なレポートシステムがあなたの手の中に生まれるはずです。
応援していますよ。困ったことがあれば、またいつでも聞きに来てくださいね。
