Microsoft Access 掲示板

クロス集計クエリからレポート印刷

50 コメント

views

4 フォロー

最新

hirotonさん新しい案ありがとうございます。
まだ試せてませんので少しお時間をください。

「ページごとに不要な列は出力しない」部分には対応してません。
とのことですがわがままを言って申し訳ありませんが、この部分はぜひよろしくお願いします。

hatenaさんとhirotonさんお二人ともアイデアを出していただき感謝してます。
現状私のスキルでは何がいいかもよくわからずにいます。

情報後出しで申し訳ありませんが、理想のレイアウトの画像を貼り付けます(上の画像と同じです)。

hirotonさん

「ページごとに不要な列は出力しない」部分には対応してません。

私の勘違いでした。
出力して大丈夫です、申し訳ありません。

ただ全く値が無いページは無しでお願いします。

情報がごちゃごちゃしてしまいましたので画像貼ります。

hirotonさん

「Q出力店舗」のレポート表示まで試してみました。
試したパソコンと実際に使うパソコンは違うのですが試したパソコンではまったく問題ありませんでした。

例えば次のようなデータ（1ページ=>5行3列）を考えます

		Px	0			1
Py	商品名	合計	A店	B店	C店	D店	E店
0	あ	5	1	1	1	1	1
	い	1	1
	う	2	1				1
	え	3	1		1	1
	お	2	1		1
1	か	1		1
	き	1		1
	く	2		1	1
	け	2		1	1
	こ	2		1	1
2	さ	1					1
2	し	1					1

「全く値が無いページ」は色を染めた部分で2ページになります。この考え方で合っていますか？

案件では、列も行も固定ではなく出力してみないとわからないという状態で、「ページ単位で見て」合計が0ならページを出力しないということですが、例えば

「商品お」の2ページ目のデータは「D店、E店」でデータはなし→ページ出力あり
「商品か」の2ページ目のデータは「D店、E店」でデータはなし→ページ出力なし

となっていて、レコード単位で出力の有無が決められません。なかなか難しくなってきました

サンプルデータのテーブルにすでに情報を追加していますが、ページ単位で考えるということで、ページの位置情報を追加して出力有無のデータを作ります

Py	Px	データの合計
0	0	9
0	1	4
1	0	8
1	1	0
2	0	0
2	1	2

「データの合計」が「0」なら出力しないページということですね

長くなるのでいったん切ります

続き

現在のレポート出力用のクエリにこの情報を追加してやれば不要なページを出力しないようにできます

商品名	f0	f1	f2	n0	n1	n2	G	→	商品名	f0	f1	f2	n0	n1	n2	Py	Px	P合計
あ	A店	B店	C店	1	1	1	0		あ	A店	B店	C店	1	1	1	0	0	9
い	A店	B店	C店	1			0		い	A店	B店	C店	1			0	0	9
う	A店	B店	C店	1			0		う	A店	B店	C店	1			0	0	9
え	A店	B店	C店	1		1	0		え	A店	B店	C店	1		1	0	0	9
お	A店	B店	C店	1		1	0		お	A店	B店	C店	1		1	0	0	9
か	A店	B店	C店		1		0		か	A店	B店	C店		1		1	0	8
き	A店	B店	C店		1		0		き	A店	B店	C店		1		1	0	8
く	A店	B店	C店		1	1	0		く	A店	B店	C店		1	1	1	0	8
け	A店	B店	C店		1	1	0		け	A店	B店	C店		1	1	1	0	8
こ	A店	B店	C店		1	1	0		こ	A店	B店	C店		1	1	1	0	8
さ	A店	B店	C店				0		さ	A店	B店	C店				2	0	0
し	A店	B店	C店				0		し	A店	B店	C店				2	0	0
あ	D店	E店		1	1		1		あ	D店	E店		1	1		0	1	4
い	D店	E店					1		い	D店	E店					0	1	4
う	D店	E店			1		1		う	D店	E店			1		0	1	4
え	D店	E店		1			1		え	D店	E店		1			0	1	4
お	D店	E店					1		お	D店	E店					0	1	4
か	D店	E店					1		か	D店	E店					1	1	0
き	D店	E店					1		き	D店	E店					1	1	0
く	D店	E店					1		く	D店	E店					1	1	0
け	D店	E店					1		け	D店	E店					1	1	0
こ	D店	E店					1		こ	D店	E店					1	1	0
さ	D店	E店			1		1		さ	D店	E店			1		2	1	2
し	D店	E店			1		1		し	D店	E店			1		2	1	2

「Px（各ページの列のまとまり）」はすでに求めていますね。「Q出力店舗のG」です。Q出力店舗の「G」は「Px」に変更します
「Py」も同様に求められます

Q出力商品リスト

SELECT 商品名
FROM テーブルA
GROUP BY 商品名;

Q出力商品ページグループ

SELECT 商品名, DCount("*","Q出力商品リスト","商品名<'" & [商品名] & "'") AS [No], Int([No]/5) AS Py
FROM Q出力商品リスト;

これで、商品ほにゃららの店舗foo店はページ（Py,Px）に出力されるという情報ができました。これを組み合わせてページごとの数量の合計を算出します
Q出力ページリスト

SELECT Py, Px, Sum(数量) AS 数量の合計
FROM (テーブルA INNER JOIN Q出力商品ページグループ ON テーブルA.棚番号 = Q出力商品ページグループ.棚番号) INNER JOIN Q出力店舗リスト_連番付き ON テーブルA.店舗名 = Q出力店舗リスト_連番付き.店舗名
GROUP BY Py, Px;

サンプルの場合に実際に得られる結果は

Py	Px	数量の合計
0	0	9
0	1	4
1	0	8
2	1	2

です。合計が「0」は実際には「データがない」なので出力されません。「数量」が「0」で実際に登録されているだとか、マイナス数量が許容されてて合計「0」がありうるとかだとまた変わってくるのでこの方法は使えないかもしれません

必要な情報がそろったのでレポート出力用のクエリを作成します
元データにはページの情報が含まれていないのでワンステップ挟んで最終的なクエリを作成します

Q出力全ページデータ

SELECT Py, テーブルA.*, Q出力店舗.*
FROM Q出力店舗, テーブルA INNER JOIN Q出力商品ページグループ ON テーブルA.棚番号 = Q出力商品ページグループ.棚番号;

Qレポート出力データ

SELECT
  棚番号
  , Sum(数量) AS 合計数量
  , Q出力全ページデータ.Py
  , Q出力全ページデータ.Px
  , f0
  , f1
  , f2
  , Sum(IIf([店舗名]=[f0],[数量])) AS n0
  , Sum(IIf([店舗名]=[f1],[数量])) AS n1
  , Sum(IIf([店舗名]=[f2],[数量])) AS n2
FROM
  Q出力ページリスト
    INNER JOIN
      Q出力全ページデータ
    ON
      Q出力ページリスト.Px = Q出力全ページデータ.Px
    AND
      Q出力ページリスト.Py = Q出力全ページデータ.Py
GROUP BY
  棚番号
  , Q出力全ページデータ.Py
  , Q出力全ページデータ.Px
  , f0
  , f1
  , f2
;

これで、ページ単位でデータがないデータは取り除いたデータが取得できます。「G」→「Px」等変更になってるのでレポートのデザインも合わせて修正してください

計算がかなり増えているのでレポート表示にもそれなりに時間がかかりそうな気がします。出力時間が気になる場合は、クエリを細かく分けて作成しているので「ここで遅くなる」等チェックしてみてください

hirotonさんへ

追加情報ありがとうございます。

「全く値が無いページ」は色を染めた部分で2ページになります。この考え方で合っていますか？
はい、問題ありません。

Qレポート出力データまで試してみました。
Qレポート出力データが少し重い感じで、レポート表示したときはかなり重い感じでした。

Q出力商品ページグループとQ出力商品リストに[棚番号]がなかったので追加しました。
間違ってたらすいません。

Q出力商品ページグループとQ出力商品リストに[棚番号]がなかったので追加しました。

あー、hirotonのミスです。直してOKです

Qレポート出力データまで試してみました。
Qレポート出力データが少し重い感じで、レポート表示したときはかなり重い感じでした。

処理内容からすれば想定通りです。そのほかの表記したい項目も含めて「ワンクリックぐらいで印刷できる」が達成できていればとりあえず完成かなと思っています。何か問題があれば教えてください

処理速度の問題についてはクエリの見直しで高速化できるか、どうしてもならVBAでどうにかするかとなると思いますが、気になるようなら実際に運用するときのデータ数とその時の処理時間を合わせて出してもらえるといいと思います。

hirotonさんへ

フィールドを追加して再度試しました。
データ量が増えたせいかQレポート出力データ→印刷がより重たくなったので,一度テーブル作成クエリをはさんでテーブル→印刷にしました。
現状この方法で問題なさそうなのですが、このやり方で大丈夫でしょうか？

すごく簡単なことなのかも知れませんが、
いくつか困ったことがあるのでアドバイスお願いします。

お願い①

印刷ページの順番の変更をお願いします。

現在
1ページ目　1店～11店　商品1～商品17
2ページ目　1店～11店　商品18～商品34
3ページ目　1店～11店　商品35～商品51

理想
1ページ目　1店～11店　商品1～商品17
2ページ目　12店～22店　商品1～商品17
3ページ目　23店～33店　商品1～商品17

お願い②

商品の合計数量欄が全ページ数字が入ってますので
2回目以降の同じ商品の合計数量は空白でお願いします。

合計数量あり　1店～11店　商品1～商品17　
合計数量なし　12店～22店　商品1～商品17
合計数量なし　23店～33店　商品1～商品17

お願い③
店舗名の順番を固定の方法以外で指定したいです。
具体的には店舗CDのフィールドがあるのでその順番にしたいです。
[店舗CD]&[店舗名]にすれば順番になるのですが、
見た目が店舗名だけの情報のほうが分かりやすいので。
良い方法があればアドバイスお願いします。

細かなチェックはまだしてませんが、ほぼ理想の形になりました。
ありがとうございます。

データ量が増えたせいかQレポート出力データ→印刷がより重たくなったので,一度テーブル作成クエリをはさんでテーブル→印刷にしました。
現状この方法で問題なさそうなのですが、このやり方で大丈夫でしょうか？

欲しい結果が得られているのなら問題ないと思いますが、テーブル作成クエリですか？
ことあるごとにテーブルそのものを作ったり消したりするのは負荷がかかるので削除クエリ（全データ削除）と追加クエリを使う形でデータだけを任意に変えられる作りのほうがいいでしょう

高速化自体はいろいろ方法があります。特に、クエリはかなり初心者向けの手順で作成しているので見直ししたいところではあります。SQLの知識が必要になるにも関わらず効果は薄そうなので微妙ですが

VBAを使えば劇的な効果もありそうですが、これはこれでやはりそれなりの知識が必要になります。
ワークテーブルへの書き込みを自動化する（簡単・効果小）
連番作成ルーチンを組む（検索すれば見つかるそれなりの方法・効果大）
レポートに出力した後印刷しない処理を組む（※）
（※）できるかどうかわかりません。できればワークテーブルもPy関係のクエリも不要で夢のようです

ただ、後で見直して「これ何やってるんだっけ？」となるようなものは非常にコストが高いので「運用上十分」で一番「楽な方法」ならそれで完成でいいんじゃないかという感じです

hirotonとしては技術的な話題も嫌いではないので、現状問題はないんだけどどうにかならないか？というのもそれはそれでアリですけどね

お願い④

[数量]のフィールドを値にしてるのですが、
これを[ケース]＆[バラ]の値にしようと思ってます。
この場合、[ケース]もしくは[バラ]の値がゼロの場合
どちらか一方を空白にすることは可能でしょうか？

普通にやれば
[ケース]0　[バラ]1の場合
0　　1
[ケース]1　[バラ]0の場合
1　　0
[ケース]0　[バラ]0の場合
空白　空白

理想
[ケース]0　[バラ]1の場合
空白　　1
[ケース]1　[バラ]0の場合
1　　空白
[ケース]0　[バラ]0の場合
空白　空白

もし理想の形になるのであればの話ですが、
値の下にテキストボックスでケース、バラ表記を付けた場合

[ケース]1　[バラ]1の場合
1　　　 1
ケース　バラ
[ケース]0　[バラ]1の場合
空白　　1
空白　　バラ
[ケース]1　[バラ]0の場合
1　　空白
ケース空白

値があるときだけケース、バラ表記をつけて
空白のときは空白にできますでしょうか？

お願い⑤

レポートのテキストボックス1に商品名を登録します。
テキストボックス2～4は商品情報を登録します。
例えば、商品名が15文字あったとして、
テキストボックス1(上)に10文字、テキストボックス1(下)に5文字
のように分けて登録することは可能でしょうか？

フィールドを[商品名前半10文字][商品名後半5文字]と分けて
テキストボックスにそれぞれ登録すればできるとおもいますが、
[商品名]のみのフィールドでできますでしょうか？

連続投稿申し訳ありません。
回答できる方よろしくお願いします。

お願い①

印刷ページの順番の変更をお願いします。

レポートのデザインでグループの追加で「Py」を追加して「Px」の上にします

お願い②

商品の合計数量欄が全ページ数字が入ってますので
2回目以降の同じ商品の合計数量は空白でお願いします。

レポートのデザインで合計数量のコントロールソースを=IIf([Px]=0,[合計数量])にします。ただし、「1店～11店」の出力がない商品グループの場合、合計数量が表示されなくなります。これに対応する場合は=IIf([Px]=DMin("Px","Qレポート出力データ","Py=" & [Py]),[合計数量])とします

お願い③
店舗名の順番を固定の方法以外で指定したいです。
具体的には店舗CDのフィールドがあるのでその順番にしたいです。

連番付けのルールを「店舗CD」に対して行えばいいですね
Q出力店舗リスト

SELECT 店舗名, 店舗CD
FROM テーブルA
GROUP BY 店舗名, 店舗CD;

Q出力店舗リスト_連番付き

SELECT 店舗名, DCount("*","Q出力店舗リスト","店舗CD<'" & [店舗CD] & "'") AS [No], Int([No]/11) AS Px, [No] Mod 11 AS f
FROM Q出力店舗リスト;

お願い④

[数量]のフィールドを値にしてるのですが、
これを[ケース]＆[バラ]の値にしようと思ってます。
この場合、[ケース]もしくは[バラ]の値がゼロの場合
どちらか一方を空白にすることは可能でしょうか？

数量のフィールドを表示するために

  , Sum(IIf([店舗名]=[f0],[数量])) AS n0

を作りました。ケースの値も表示したいなら

  , Sum(IIf([店舗名]=[f0],[ケース])) AS nC0

とかでフィールドを増やせばいいかな？（数量用=>n0～n10、ケース用nC0～nC10）
「ケース」「バラ」の表示はそれぞれ

=IIf(IsNull(nC0),,"ケース")
=IIf(IsNull(n0),,"バラ")

お願い⑤

レポートのテキストボックス1に商品名を登録します。
テキストボックス2～4は商品情報を登録します。
例えば、商品名が15文字あったとして、
テキストボックス1(上)に10文字、テキストボックス1(下)に5文字
のように分けて登録することは可能でしょうか？

テキストボックス1(上)
=Left([商品名],10)
テキストボックス1(下)
=Mid([商品名],11)

hirotonさん

全ての質問に回答ありがとうございます。

お願い①
印刷ページの順番の変更をお願いします。
レポートのデザインでグループの追加で「Py」を追加して「Px」の上にします

一つ目でつまずいてしましました。
自分なりに試行錯誤しましたができませんでした。

こんな感じです

Pyヘッダーで表示するものもないのでグループの追加じゃなくて並べ替えの追加で良かったです

hirotonさんへ

お願い①の件
素早いアドバイスありがとうございます。
画像付きでとても分かりやすく助かります。

アドバイス通りにしてるつもりなんですが(余計なことをしていたらすみません)
行の表示数が減ってしまいページ数が増えてしまいます。
(例)
変更前　商品17行
変更後　商品5行
のようになってしまします。
お手数おかけします。

お願い②の件
=IIf([Px]=0,[合計数量])
問題なく実行できました。
例えば1店舗のみのときも空白になるってことでしょうか？

=IIf([Px]=DMin("Px","Qレポート出力データ","Py=" & [Py]),[合計数量])
とても重いです。
印刷した場合、1ページごとに15秒ぐらいかかったりします。
(例)
1ページ目印刷→15秒経過→2ページ目印刷→15秒経過→3ページ目印刷・・・

この件で少ない店舗のときを調べようと気づいたのですが、
店舗名、商品名などが少ない時にQレポート出力データを実行したら
'Q出力全ページデータ.f8を有効なフィールド名、または式として認識できません。
というエラーメッセージが出てきます。

早い段階で確認しとくべきこと遅くなってすみません。
よろしくお願いします。

お願い③の件
理想の形になりました。
ありがとうございます。

お願い④の件
現状どこにどう追加すればいいのかよくわかっておりません。
申し訳ありません。

お願い⑤
理想の形になりました。
ありがとうございます。

テーブル作成クエリの件
削除クエリ（全データ削除）と追加クエリを今まで使ったことがありませんでした。
クエリの使い分けがまったく分かってないので勉強します。
アドバイスありがとうございます。

お願い①の件

Pyも考え方はPxと同じなので細かいこと省略しましたが、>> 23の「Q出力商品ページグループ」は実際の出力に合わせて調整が必要です

SELECT 商品名, DCount("*","Q出力商品リスト","商品名<'" & [商品名] & "'") AS [No], Int([No]/5) AS Py
FROM Q出力商品リスト;

↓

SELECT 商品名, DCount("*","Q出力商品リスト","商品名<'" & [商品名] & "'") AS [No], Int([No]/17) AS Py
FROM Q出力商品リスト;

お願い②の件
=IIf([Px]=0,[合計数量])

例えば1店舗のみのときも空白になるってことでしょうか？

計算式を理解してもらいたいのですが、「Px」が「0」の時だけ[合計数量]を表示する（それ以外の時は何もしない＝空白になる）という処理をしています。
『「Px」が「0」』とは列データが「A店～K店」のデータですね。クロス集計クエリで全データ出力したときの左端のページということです。その上で「データがないときはページを印刷しない」という処理を追加しているので『ページのグループ（Px）が「0」の時』と固定すると「Px=0（A店～K店）」のデータがないときは[合計数量]を表示するページが印刷されないということです。

=IIf([Px]=DMin("Px","Qレポート出力データ","Py=" & [Py]),[合計数量])
とても重いです。

そうなるだろうなぁと思っていました。とりあえず、計算式でとんでもない無駄をしていました

=IIf([Px]=DMin("Px","Q出力ページリスト","Py=" & [Py]),[合計数量])

少しは速くなると思います。いずれにせよ、これが必要ならデータベース的な計算をさせるととても苦しいモノなのでVBAで対応する案件かと思っています

'Q出力全ページデータ.f8を有効なフィールド名、または式として認識できません。

クエリの整理が必要かなぁと思っていたので触れていなかったんですが、「Q出力店舗」の問題ですね。これがクロス集計クエリなので、データ数が少ないと列数が足りなくなります。データを変えながら「Q出力店舗」を開いてみてください。このクエリでフィールドが固定であればいいのでクエリ列見出しに"f0","f1","f2","f3","f4","f5","f6","f7","f8","f9","f10"と設定すれば良いでしょう

お願い④の件

クエリの問題ですか？レポートデザインの問題ですか？

元データのフィールドはどうなっていますか？（「数量」自体も実際に存在するフィールドなんでしょうか？）

レポートのデザインであれば、「ケースの数量」「バラの数量」「ケースの文字」「バラの文字」の4つのテキストボックスがあることを想定しています

合計数量の表示をVBAで対応する場合
レポートデザインで「Py」にヘッダーセクションを付けます
改ページを「カレントセクションの前」に設定します

'Pyヘッダーのフォーマット時
Private Sub グループヘッダー1_Format(Cancel As Integer, FormatCount As Integer)
    Me!合計数量.Format = ""
End Sub

'ページヘッダーのフォーマット時
Private Sub ページヘッダーセクション_Format(Cancel As Integer, FormatCount As Integer)
    Me!合計数量.Format = " "
End Sub

※グループヘッダー1は「Py」のヘッダーの名前です。実際のデータに合わせて変更してください

あれこれ試していたら思っていたよりも簡素になりました。ただ、これが何で正しく動くのかというのは結構難解な気がします。解説は必要であれば、ということで

hirotonさんへ

お願い①の件
無事できました。
何も分かっておらずお手数おかけしました。
順番がおかしかったので[棚番号]を追加しました。
「Q出力商品ページグループ」

SELECT Q出力商品リスト.棚番号, Q出力商品リスト.商品名, DCount("*","Q出力商品リスト","棚番号<'" & [棚番号] & "'") AS [No], Int([No]/17) AS Py
FROM Q出力商品リスト;

お願い②の件
少しお時間をください

'Q出力全ページデータ.f8を有効なフィールド名、または式として認識できません。の件
無事できましたが下記のような感じで大丈夫でしょうか？
「Q出力店舗」

TRANSFORM Max(Q出力店舗リスト_連番付き.店舗名) AS 店舗名の最大
SELECT Q出力店舗リスト_連番付き.Px
FROM Q出力店舗リスト_連番付き
GROUP BY Q出力店舗リスト_連番付き.Px
PIVOT "f" & [f] In ("f0","f1","f2","f3","f4","f5","f6","f7","f8","f9","f10");

お願い④の件
少しお時間をください。

追加アドバイスありがとうございます。
まだ確認できてない部分もありますのでまた後日書き込みます。

hirotonさんへ

お願い②の件

計算式理解しました。
「A店～K店」のページが無い場合、合計数量が出ないということですね。

=IIf([Px]=DMin("Px","Q出力ページリスト","Py=" & [Py]),[合計数量])

前よりは軽いと思いますが、これも少し重いです。

「合計数量の表示をVBAで対応する場合」

Me![合計数量].Format = " "
の部分だと思うのですがエラーが出ます。

よろしくお願いします。

エラーの発生するタイミング
その時の挙動（どのような結果をエラーと言っているのか）
エラーメッセージが出るならそのメッセージを添えてください

想定しているレポートは>> 15から進んだものです

  , Sum(数量) AS 合計数量

ページヘッダー
Pyヘッダー
Pxヘッダー
棚番号	合計数量	f0	f1	f2
詳細
棚番号	合計数量	n0	n1	n2

クエリにフィールドとして「合計数量」を持ち、それをレポート上に配置している状態です。エラーが発生するとなると、「フィールドが存在しません」エラーですかね？

[合計数量]は合計数量を表示するテキストボックス（上記色付き部分のテキストボックス）の名前に変更してください

hirotonの場合、レコードソースに存在するフィールドを表示するテキストボックスは、その名前をフィールド名と同じにする（レポートでもフォームでも）ので、特に問題なく動くんですが、『[数量]フィールドのデータを表示するテキストボックスは[txt数量]とする』みたいなルールでやってるところもあるようなのでうまく読み取ってもらうしかないですね

hirotonさんへ

説明不足申し訳ございません。
「エラーの発生するタイミング」
レポートを開くまでは問題ないのですが、印刷プレビューを開いたときです。

「その時の挙動（どのような結果をエラーと言っているのか）」
「エラーメッセージが出るならそのメッセージを添えてください」
画像をはりつけますのでよろしくお願いします。

コードはレポートのモジュールに記述していますか？
合計数量を表示するテキストボックスの名前を正しく指定していますか？

hirotonさんへ

※hirotonの場合、レコードソースに存在するフィールドを表示するテキストボックスは、その名前をフィールド名と同じにする（レポートでもフォームでも）
※合計数量を表示するテキストボックスの名前を正しく指定していますか？

まったくこの通りのミスでした。
お手数おかけして申し訳ありませんでした。
処理の重さもまったく感じません。
ありがとうございます。

hirotonさんへ

お願い④の件は1店舗分だけ作りましたがうまくいきました。

「テーブルA」に
ケース入数
ケース数:Int([数量] / [ケース入数] )
バラ数: [数量] Mod [ケース入数]
の3つを追加してます。

合計数量も[合計ケース数][合計バラ数]に分けて表示したいと思っております。
合計ケース数:Int([合計数量] / [ケース入数] )
合計バラ数: [合計数量] Mod [ケース入数]
のような感じで作ろうとしましたがうまくいきませんでした。
0の場合、レポートに表示するときは空白でお願いします。

ケース数:Int([数量] / [ケース入数] )
バラ数: [数量] Mod [ケース入数]

この計算自体はレポート出力の直前でやればいいですね

合計ケース数:Int([合計数量] / [ケース入数] )
合計バラ数: [合計数量] Mod [ケース入数]

レポート出力用のクエリは次のような形になっていると思います

  , Sum(数量) AS 合計数量

  , f0

  , Sum(IIf([店舗名]=[f0],[数量])) AS n0

GROUP BY

  , Q出力全ページデータ.Py
  , Q出力全ページデータ.Px

[合計数量]は各商品の全店舗の合計ですね。店舗ごとの数量としては、「(Px,Py)ページの1列目の店舗はf0フィールドの値、その時の数量はn0」のように作っているので、この「n0」に対して計算式を当ててあげればいいです

  , Sum(数量) AS 合計数量

  , f0

  , Sum(IIf([店舗名]=[f0],[数量])) AS n0
  , Int([n0] / [ケース入数] ) AS n0ケース数
  , [n0] Mod [ケース入数] AS n0バラ数

GROUP BY

  , Q出力全ページデータ.Py
  , Q出力全ページデータ.Px

0の場合、レポートに表示するときは空白でお願いします。

数値をチェックして必要な時（0より大きいとき）だけ出力すればいいですね

  , IIf([n0ケース数]>0,[n0ケース数]) AS n0ケース数表示
  , IIf([n0ケース数]>0,"ケース") AS n0ケース文字
  , IIf([n0バラ数]>0,[n0バラ数]) AS n0バラ数表示
  , IIf([n0バラ数]>0,"バラ") AS n0バラ文字

または、レポートに配置したあと書式を設定するでもいいかもしれません
数値フィールドまたは通貨フィールドの書式を設定する

n0ケース数
コントロールソース｜n0ケース数
書式 　　　　　　　｜0;; 

n0バラ数
コントロールソース｜n0バラ数
書式 　　　　　　　｜0;; 

n0ケース文字
コントロールソース｜n0ケース数
書式 　　　　　　　｜ケース;; 

n0バラ文字
コントロールソース｜n0バラ数
書式 　　　　　　　｜バラ;;

表示できないですが、「;;」の後に「半角スペース」が入っています。
レポートのデザインビューで入力するときは自動整形が働くので、例えば、「0;;" "」と入力する必要があります。または「0;;　」（最後の文字は全角スペース）でもいいでしょう

hirotonさんへ

素早いアドバイスいつもありがとうございます。
まだ試せていませんので、できましたらまた書き込みさせていただきます。

あ、見落としてました。[合計数量]もケース、バラに分解するんですね。やろうとした感じで出来るはずですがうまくいかないですか？

  , ケース入数

  , Sum(数量) AS 合計数量
  , Int([合計数量]/[ケース入数]) AS 合計ケース数
  , [合計数量] Mod [ケース入数] AS 合計バラ数

  , f0

  , Sum(IIf([店舗名]=[f0],[数量])) AS n0
  , Int([n0]/[ケース入数] ) AS n0ケース数
  , [n0] Mod [ケース入数] AS n0バラ数

それと、上記の方法はケース、バラの算出は[数量]を合計してから機械的にという方法です。そうではなく、
例えば

商品	店舗	数量	ケース入数
あ	A	15	10
あ	A	16	10

なんてデータの場合、現実に即して数字を出すと

商品	店舗	数量合計	ケース入数	ケース合計	バラ合計
あ	A	31	10	2	11

となることが考えられます（バラ数合計がケース入数を超える）。こういう計算をしたいのであれば合計する前の[数量]からケース数、バラ数を算出する必要があります

  , ケース入数

  , Sum(数量) AS 合計数量
  , Sum(Int([数量]/[ケース入数])) AS 合計ケース数
  , Sum([数量] Mod [ケース入数]) AS 合計バラ数

  , f0

  , Sum(IIf([店舗名]=[f0],Int([数量]/[ケース入数]))) AS n0ケース数
  , Sum(IIf([店舗名]=[f0],[数量] Mod [ケース入数])) AS n0バラ数

Int([数量]/[ケース入数]と[数量] Mod [ケース入数])が複数回でてきますが、レポート出力用クエリ「Qレポート出力データ」は集計を使ったクエリなので、これを個別のフィールドに置くことはできません。わかりやすく処理するなら事前にフィールドを作っておく必要があります。ちょうど前段階として「Q出力全ページデータ」があるので

Q出力全ページデータ

SELECT Py, テーブルA.*, Q出力店舗.*, Int([数量]/[ケース入数]) AS ケース数, [数量] Mod [ケース入数] AS バラ数
FROM Q出力店舗, テーブルA INNER JOIN Q出力商品ページグループ ON テーブルA.棚番号 = Q出力商品ページグループ.棚番号;

とすれば
Qレポート出力データ


  , Sum([数量]) AS 合計数量
  , Sum([ケース数]) AS 合計ケース数
  , Sum([バラ数]) AS 合計バラ数

  , f0

  , Sum(IIf([店舗名]=[f0],[ケース数])) AS n0ケース数
  , Sum(IIf([店舗名]=[f0],[バラ数])) AS n0バラ数

と作ることができます

「テーブルA」に
ケース入数
ケース数:Int([数量] / [ケース入数] )
バラ数: [数量] Mod [ケース入数]
の3つを追加してます。

なので、こっちのほうがやりたいことだったかな？

hirotonさんへ

追加アドバイスありがとうございます。

「ケース、バラの算出は[数量]を合計してから機械的にという方法です。」
こちらのやり方で大丈夫です。
無事できましたありがとうございます。

「合計数量の表示をVBAで対応する場合」がおかしくなってしまいました。
2ページ目以降に数量は空白なのですが単位が表示されてしまいます。

　, IIf([n0ケース数]>0,[n0ケース数]) AS n0ケース数表示
  , IIf([n0ケース数]>0,"ケース") AS n0ケース文字
  , IIf([n0バラ数]>0,[n0バラ数]) AS n0バラ数表示
  , IIf([n0バラ数]>0,"バラ") AS n0バラ文字

レポートは上記のやり方でしてます。
画像貼り付けますのでよろしくお願いします。
情報不足でしたら申し訳ありません。

[テキスト122]～[テキスト125]は適切な名前で運用するか、

[テキスト122]→n0ケース数表示

等、注釈を追記するようにしてください（質問者以外中身のわからないモノでは回答しにくいです）

それと、コードはなるべくコピペで提示するようにしましょう。いろいろ捗ります
コードブロック

hirotonさんへ

上の書き込みの追加です。

テーブルAにテキスト型の[入荷引当]というフィールドがあります。
0か1の判定があり、0のときは空白で1のときは「予」という文字を合計数量と各店舗の数量の左にいれたいと思っております。
1店舗でも「予」があれば合計数量にも「予」を入れて、1店舗も「予」がなければ合計数量も無しでお願いします。

よろしくお願いします。

区切りをどこで付けるかというのは悩みどころですが、そろそろ質問を分けたほうがよさそうです

当初の質問として

ワンクリックぐらいで印刷できるように

とあり、それに対してデータベース的なアプローチ（クエリ・SQLを駆使してデータ整形）で回答を付けました。ACCESS的なアプローチで連番を振ったり、処理速度的に厳しいところをVBAで対応したりもしましたが、元データに依存するような部分はなかったと思います。
言葉にすると「大きなデータをページ毎のデータに整形する方法」ですね

追加の質問は元データから項目が追加されてきているし、「各ページのデータができた後のレポート表示のしかた」といった感じで最初の質問からは内容が離れてきているので、改めて必要な内容（データ構造、レポートの表示イメージ等）を整理した上で新たな質問にしてください

hirotonさんへ

「クロス集計クエリからレポート印刷」に関して最初の設計図と現状は大幅に変わってしまいました。
アクセスでクロス集計表を作る場合は列と値は1つしか設定できないという認識がありました。
現状は最終的にクロス集計表としての複数の列と値を設定できるようになり求める設計図が大幅に広がってしまいました。
「クロス集計クエリからレポート印刷」というトピックへの書き込みはこれで最後にします。
一か月近い期間のアドバイスありがとうございました。
また機会がありましたらよろしくお願いします。

タグリスト

VBA (363) Office2016 (289) フォーム (226) Office2021 or 2019 (220) クエリ SQL (211) テーブル (134) レポート (122) Office2013 (56) Office2010 (46) Microsoft365 (40)

Markdown テーブル生成

Accessのテーブルやクエリのデータを貼り付ける場合は下記で、Markdown書式のテーブルに変換して貼り付けてください。

Markdown Tables generator

ファイル送信フォーム

掲示板で必要なサンプルファイルは下記のフォームから送信してください。

Microsoft Access 掲示板用ファイル送信

Microsoft Access 掲示板

クロス集計クエリからレポート印刷

最新トピック

タグリスト

Markdown テーブル生成

ファイル送信フォーム