Accessのレポートで列の数が動的に変化するときの作成方法

前提

Microsoft Accessを使ってレポート作成をしています。
クロス集計した結果をレポートに反映させたいのですが、
列の数が動的に変化するため、改ページして2枚目以降のレポート作成をどのようにすればいいのか試行錯誤しています。
クロス集計で表示したい内容を出すところまではできましたが、
レポートの表示列が固定（？）なため、何列ごとに改ページするというような内容をVBでコードを書いていくしかないと思いますが、何列目が終わったら改ページして再度同様の内容で続きの集計結果を描写するという方法が思いつきません。
お知恵をお貸しいただけますと幸いです。

実現したいこと

指定した年月の範囲を抽出条件としてクロス集計する
一番左の列（日）は固定で、1日～31日を表示
次の列から指定した年月日の範囲を表示

　<例> 2017年05月～2019年03月を抽出条件として指定して、その範囲の検索結果を表示

指定した年月の範囲はクエリの列固定とする
1枚の列表示は「日」の項目を合わせて5列表示とする

クロス集計結果

日	2021-01	2022-02	2022-03	2022-04	2022-05	2022-06
	金額	金額	金額	金額	金額	金額
1日				70000	1000000
2日		30000	60000		100000
3日			50000		100000
4日	10000			80000
5日		20000
6日			40000
7日				200000
8日

上記内容を下記のような形にレポート作成することは可能でしょうか？

1枚目

日	2021-01	2022-02	2022-03	2022-04
	金額	金額	金額	金額
1日				70000
2日		30000	60000
3日			50000
4日	10000			80000
5日		20000
6日			40000
7日				200000
8日

2枚目

日	2021-05	2022-06
	金額	金額
1日	1000000
2日	100000	30000
3日	100000
4日
5日		20000
6日
7日
8日

該当のソースコード

日付テーブル（内容は1-31)
販売テーブル

ID	取引先コード	取引先名	商品コード	商品名	単価	数量	日付
1	1	A商店	11111	商品A	100	10	2022/7/1
2	1	A商店	11111	商品A	100	5	2022/7/14
3	1	A商店	11111	商品A	100	6	2022/7/21
4	1	A商店	11111	商品A	100	1	2022/6/16
5	1	A商店	11111	商品A	100	22	2022/6/10
6	1	A商店	11111	商品A	100	3	2022/6/14
7	1	A商店	11111	商品A	100	7	2022/5/18
8	1	A商店	11111	商品A	100	8	2022/5/27
9	1	A商店	11111	商品A	100	9	2022/8/5
10	1	A商店	11111	商品A	100	16	2022/8/9
11	1	A商店	11111	商品A	100	5	2022/8/7

集計クエリ

SELECT Format$(販売クエリ.日付, "yyyy-mm-01") AS [month], 
Day(販売クエリ.日付) AS 日, 
販売クエリ.日付, 
販売クエリ.取引先コード, 
販売クエリ.取引先名, 
販売クエリ.商品コード, 
販売クエリ.商品名, 
[数量]*[単価] AS 値
FROM 販売クエリ;

クロス集計

TRANSFORMMin(Q_1.値) AS 値の最小
SELECT F_日付.日付
FROM F_日付 
LEFT JOIN Q_1 ON F_日付.日付 = Q_1.日
GROUP BY F_日付.日付, Q_1.日付
PIVOT Q_1.Month IN ("2022-01-01","2022-02-01","2022-03-01","2022-04-01","2022-05-01","2022-06-01")

hatena19

2022/08/11 11:05

1ページは4か月分(4列)の表示ですが、列数は最大何列までですか。また、行数は最小は何行、最大は何行でしょう。最大の場合は、1ページに収まりますか。

moge56

2022/08/11 11:34

列数はユーザーが指定する年月の範囲なので1つ以上～で最大何列になるかは不明です。また、行数は1日～31日の31行で固定です。 1ページに31行と4か月分毎に表示したいと考えております。

hatena19

2022/08/11 11:36

日付テーブル（内容は1-31) とのことですので、行は31行固定ということですね。この31行は1ページに収まりますか。

hatena19

2022/08/11 11:37

あっ、かぶりました。状況は把握できました。

moge56

2022/08/11 11:39

最初は、1日～31日の行を列にして（31行と固定なため）、何か月分のデータを行にして・・と考えていましたが、 1日～31日を縦表示にしたいためぐるぐる悩み中です。。。

moge56

2022/08/15 04:54 編集

hatena19様もし可能であれば、追加で質問させていただきたいのですが、 Accessのクエリは、１００個UNION ALLするのと、数千回ループして一時テーブルにインサートするのはどちらが好ましいかご存じでしょうか？UNION ALLは重い処理で有名なのであまりするべきではないと思うのですが、かといってループを何千回するのもどうかと思いお尋ねしました。（年月と日の組み合わせを作成するときにふと思いました）

行動規範の内容に同意します

回答2件

とりあえずアイデアだけいつくか提案しておきます。
自身でやりやすそうなものにチャレンジしてみてください。(人により得意、不得意があるで)
そこで行き詰ったら、どこまでできて、どこが分からないか、再質問してください。

案１

(日、列1、列2、列3、列4) というフィールドを持つ一時テーブルを作成しておいて、
そこにVBAでクロス集計クエリをレコードセットとして開いて、
最初の４か月分の値を３１日分追加する、
次の４か月分を３１日分追加する、・・・・
というのを最後の年月まで繰り返す。
この一時テーブルをレポートのレコードソースにする。

案２

集計クエリで(列、ページ)の演算フィールドを追加して、下記のような出力になるようにする。

日付	値	年月	列	ページ
2022/1/1	10	2022-01	1	1
・・・	・・・	・・・	・・・	・・・
2022/1/31	10	2022-01	1	1
2022/2/1	10	2022-02	2	1
・・・	・・・	・・・	・・・	・・・
2022/2/28	10	2022-02	2	1
2022/3/1	10	2022-03	3	1
・・・	・・・	・・・	・・・	・・・
2022/3/31	10	2022-03	3	1
2022/4/1	10	2022-04	4	1
・・・	・・・	・・・	・・・	・・・
2022/4/30	10	2022-04	4	1
2022/5/1	10	2022-05	1	2
・・・	・・・	・・・	・・・	・・・
2022/5/31	10	2022-05	1	2

これから、列見出しに「列」を、行見出しに「Day(日付)」でクロス集計クエリを作成。
これをレポートのレコードソースにして、ページでグループ化して、グループフッターで改ページ。

案３

日フィールド(1日～31日)を列見出しにして、年月を行見出しにする。

各テキストボックスのプロパティを下記のように設定する。

縦書き　→　はい
フォント　→　@マーク付きの等幅フォントを選択
縦、横の数値を入れ替えて縦長の長方形にする。

これで、通常の表示から右に90度回転させた状態にできます。

ページレイアウトで横を選択します。（もともと横だった場合は縦に）
これでプレビューでは通常の状態から９０度回転した状態で表示されますが、
これを印刷すれば通常のものと同じレイアウトになります。

投稿2022/08/12 08:23

編集2022/08/12 08:24

hatena19

総合スコア33699

moge56

2022/08/15 04:50

３案も出していただきありがとうございます！レポートの列は固定で、VBAでゴリゴリ横に追加するということができなさそうなため、「案２」に近いことを考えて試していました。なんとか形になりそうなのでまた出来上がりましたらご報告させていただきます。

行動規範の内容に同意します

列の数が動的に変化するため、改ページして2枚目以降のレポート作成をどのようにすればいいのか試行錯誤しています。

とのことですが、提示のクロス集計クエリは、
`PIVOT Q_1.Month IN ("2022-01-01","2022-02-01","2022-03-01","2022-04-01","2022-05-01","2022-06-01")‘で列を固定していますが、この部分をVBAで動的に書き換えているということでしょうか。
抽出条件によっては、列名も変化しますよね。

投稿2022/08/11 08:57

hatena19

総合スコア33699