列名が変化するクロス集計クエリと連結するフォーム

Access- Form

クロス集計クエリの場合、フィールド名（列名）が固定でない場合があります。そのようなクエリと連結したフォームの場合、コントロールソースとフィールドが合致しなくて #Name? エラーが出てしまいますね。このようなクロス集計クエリをフォームのレコードソースに設定して問題なく表示させる方法の紹介です。

難易度:

仕様

下記のような予約テーブルから、上図のようなフォームで、ヘッダーで指定した開始日から1週間分のデータを表示します。

**T_予約**
ID	名前	予約日	予約時刻
1	A	2009/06/01	13:00:00
2	C	2009/06/01	13:45:00
3	B	2009/06/02	13:00:00
4	A	2009/06/02	13:15:00
5	C	2009/06/03	13:15:00
6	B	2009/06/03	13:30:00
7	C	2009/06/04	13:45:00
8	A	2009/06/06	13:30:00

コントロールソースをVBAで動的に書きかえる方法

フォーム名 F_予約、ヘッダーにテキストボックスを配置して名前を開始日とする。レコードソースは空白にしておきます。また、ヘッダーの日付表示ラベルの名前は、lbl0, lbl1, ・・・lbl6、詳細セクションの値表示用のテキストボックスの名前は、 txt0, txt1, ・・・txt6 とします。

下記のクエリ「Q_予約」を作成 PARAMETERS Forms!F_予約!開始日 DateTime; TRANSFORM First(T_予約.名前) AS 名前の先頭 SELECT T_予約.予約時刻 FROM T_予約 WHERE T_予約.予約日 Between Forms!F_予約!開始日 And Forms!F_予約!開始日+6 GROUP BY T_予約.予約時刻 ORDER BY T_予約.予約時刻, Format([予約日],"mm/dd") PIVOT Format([予約日],"mm/dd");

フォームモジュール Option Compare Database Option Explicit Private Sub SourceReset() Dim i As Integer Dim D As Date Dim Res D = Me.開始日 Me.Painting = False 'ちらつき防止 Me.RecordSource = "" Me.RecordSource = "Q_予約" On Error Resume Next For i = 0 To 6 Me("lbl" & i).Caption = Format(D + i, "m/d(aaa)") Res = Me(Format(D + i, "mm/dd")) 'フィールドを参照して、エラーなら存在しない If Err = 0 Then Me("txt" & i).ControlSource = Format(D + i, "mm/dd") Else Me("txt" & i).ControlSource = "" Err = 0 End If Next Me.Painting = True End Sub Private Sub Form_Load() SourceReset End Sub Private Sub 開始日_AfterUpdate() SourceReset End Sub

クロス集計クエリの列名を固定する方法

フォーム名 F_予約_固定

クエリ名 Q_予約_固定 PARAMETERS Forms!F_予約_固定!開始日 DateTime; TRANSFORM First(T_予約.名前) AS 名前の先頭 SELECT T_予約.予約時刻 FROM T_予約 WHERE 予約日 Between Forms!F_予約_固定!開始日 And Forms!F_予約_固定!開始日 + 6 GROUP BY T_予約.予約時刻 PIVOT [予約日]-Forms!F_予約_固定!開始日 In (0,1,2,3,4,5,6);

開始日と予約日の差を列名にして、In句で列名を0 ～ 6 に固定します。このクエリを、フォームのレコードソースに設定します。

ヘッダに表示する列名のラベルはテキストボックスに変換して、コントロールソースでDateAdd関数で日付に変換します。左から順に、
=DateAdd("d",0,[開始日])
=DateAdd("d",1,[開始日])
=DateAdd("d",2,[開始日])
・・・・

読み込み時と開始日の更新後に再クエリします。

フォームモジュール Option Compare Database Option Explicit Private Sub Form_Load() Me.Requery End Sub Private Sub 開始日_AfterUpdate() Me.Requery End Sub

前者の方法は列名固定でないためウィザードが使えませんが、後者のクエリで列名固定の方法は、ウィザードで簡単にフォームが作成できます。レポートの場合でも同様に使えます。

レポートでVBAでコントロールソースを動的に書きかえる方法は、RURIさんのサイトの下記にサンプルがあります。

クロス集計クエリーからのレポート作成

サンプルファイルが下記からダウンロードできます。
FrmCrossQuerySource_07.zip (Access 2007-2010 形式 - 26kb)
FrmCrossQuerySource.zip (Access 2002-2003 形式 - 26kb)
FrmCrossQuerySource_2k.zip (Access 2000 形式 - 25kb)

追記(2009/7/20)：記事では縦計は表示してませんが、サンプルMDBは、フォームフッターに縦計も表示させているものに差し替えました。必要な場合はダウンロードしてご参考にしてください。