, 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バラ文字
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");
'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件のみで、検索終了後はデータはクリアすればいいでしょう。
過去の抽出条件を再利用する可能性があるなら、レコードを追加して利用するのもありです。
開いたときは新規レコードにしておいて、コンボボックスで過去の条件データを選択することも可能にするという設計にしてもいいでしょう。
この計算自体はレポート出力の直前でやればいいですね
レポート出力用のクエリは次のような形になっていると思います
[合計数量]は各商品の全店舗の合計ですね。店舗ごとの数量としては、「(Px,Py)ページの1列目の店舗はf0フィールドの値、その時の数量はn0」のように作っているので、この「n0」に対して計算式を当ててあげればいいです
数値をチェックして必要な時（0より大きいとき）だけ出力すればいいですね
または、レポートに配置したあと書式を設定するでもいいかもしれません
数値フィールドまたは通貨フィールドの書式を設定する
表示できないですが、「
;;」の後に「半角スペース」が入っています。レポートのデザインビューで入力するときは自動整形が働くので、例えば、「
0;;" "」と入力する必要があります。または「0;;」（最後の文字は全角スペース）でもいいでしょうありがとうございます
検索条件用入力用のテーブルのレコードは常に一つということですか？検索のたびに増えていきませんか？
それらのフィールドを元に検索をかけるのですよね？
VBAを使えば可能ですよ。
ラベルとチェックボックスを並べて作成すればできます。
ただ、面倒ですよね。
検索条件用入力用のテーブルを作成して、各フィールドは「複数値フィールド」にすれば、リストからチェックボックスで選択できるUIになります。これを使えば楽できそうですね。
複数値を持つフィールドの作成または削除 - Access
質問が曖昧で申し訳ないのですが
厳密にいうとフィルタではなく、検索ということになります
画像のようなイメージです
伝え方があいまいで申し訳ないです
個人的なこだわり（UIデザイン的な）でデータシートビューは使いたくなったので、サンプルをみて勉強させてもらいます
データシートビューのように複数の項目にチェックをいれていき、すべてに適合するものを検索することはむずかしいですか？
hirotonさんへ
お願い④の件は1店舗分だけ作りましたがうまくいきました。
「テーブルA」に
ケース入数
ケース数:Int([数量] / [ケース入数] )
バラ数: [数量] Mod [ケース入数]
の3つを追加してます。
合計数量も[合計ケース数][合計バラ数]に分けて表示したいと思っております。
合計ケース数:Int([合計数量] / [ケース入数] )
合計バラ数: [合計数量] Mod [ケース入数]
のような感じで作ろうとしましたがうまくいきませんでした。
0の場合、レポートに表示するときは空白でお願いします。
hirotonさんへ
※hirotonの場合、レコードソースに存在するフィールドを表示するテキストボックスは、その名前をフィールド名と同じにする（レポートでもフォームでも）
※合計数量を表示するテキストボックスの名前を正しく指定していますか？
まったくこの通りのミスでした。
お手数おかけして申し訳ありませんでした。
処理の重さもまったく感じません。
ありがとうございます。
コードはレポートのモジュールに記述していますか？
合計数量を表示するテキストボックスの名前を正しく指定していますか？
データシートビューにすれば、ヘッダーをクリックするとエクセル的なフィルターが使えます。
ヘッダーに条件を入力するボックスを配置してVBAでフィルターをかける方法なら、下記にサンプルがあります。
複数条件の抽出フォームの設計 その1 - hatena chips
表計算ソフトとデータベースソフトでは、データの扱いがまったく異なるのでエクセル的な発想から離れてデータベース的な設計を意識するようにしたほうがいいと思います。
VBAで疑似的にオートフィル的な動作をさせることは可能ですが、あまりいい設計ではないと個人的には思います。
オートフィル的な動作が欲しいときの例として、例えば、一対多の関係のデータ（具体的には、請求書ヘッダーと請求明細など）なら、Accessならメインサブフォーム形式あるいはデータシート/サブデータシート形式の入力フォームを使います。
メインフォームで、顧客データ、請求日付を入力。サブフォームでは明細データを入力。という設計にすればオートフィルは必要ないです。
あるいは、連続してデータを入力するとき、前の同じデータを入力する場合が多いというときには、テキストボックスの既定値を設定をVBAで設定すれば、新規レコードに移動したときに既定値として表示させておくことが可能です。
連続値も同様に規定値で可能です。
ユーザーがエクセルでの入力に慣れているということなら、エクセルで入力させてインポートするという方法もあるでしょうが、私はしたことはありません。
Accessのフォームで入力しやすいUIを工夫するようにしてます。
hirotonさんへ
説明不足申し訳ございません。
「エラーの発生するタイミング」
レポートを開くまでは問題ないのですが、印刷プレビューを開いたときです。
「その時の挙動（どのような結果をエラーと言っているのか）」
「エラーメッセージが出るならそのメッセージを添えてください」
画像をはりつけますのでよろしくお願いします。
エラーの発生するタイミング
その時の挙動（どのような結果をエラーと言っているのか）
エラーメッセージが出るならそのメッセージを添えてください
想定しているレポートは>> 15から進んだものです
クエリにフィールドとして「合計数量」を持ち、それをレポート上に配置している状態です。エラーが発生するとなると、「フィールドが存在しません」エラーですかね？
[合計数量]は合計数量を表示するテキストボックス（上記色付き部分のテキストボックス）の名前に変更してください
hirotonの場合、レコードソースに存在するフィールドを表示するテキストボックスは、その名前をフィールド名と同じにする（レポートでもフォームでも）ので、特に問題なく動くんですが、『[数量]フィールドのデータを表示するテキストボックスは[txt数量]とする』みたいなルールでやってるところもあるようなのでうまく読み取ってもらうしかないですね
hirotonさんへ
お願い②の件
計算式理解しました。
「A店～K店」のページが無い場合、合計数量が出ないということですね。
=IIf([Px]=DMin("Px","Q出力ページリスト","Py=" & [Py]),[合計数量])
前よりは軽いと思いますが、これも少し重いです。
「合計数量の表示をVBAで対応する場合」
Me![合計数量].Format = " "
の部分だと思うのですがエラーが出ます。
よろしくお願いします。
リンク先の方法とか、他にはページフォーマットでLineメソッドで直線を描画するとかの方法はありますよ。
Lineメソッドの使い方は下記が参考になるかと。
レコードがない場合も用紙の最後まで罫線を出力する関数 - hatena chips
まあ、上記のコードを書いている時間より、目分量でやる方が速いですけどね。
目分量の調整しかできないということですね
目分量で合わせて微妙な隙間は一枚目の画像のようになるものとして割り切るしかなさそうですね
私の場合は、ページ設定の「下余白」の値をプレビューしながら調整してます。
それが面倒ということなら、下記で紹介した方法を使うかですかね。
ページの最後の罫線を太線にする - hatena chips
hirotonさんへ
お願い①の件
無事できました。
何も分かっておらずお手数おかけしました。
順番がおかしかったので[棚番号]を追加しました。
「Q出力商品ページグループ」
お願い②の件
少しお時間をください
'Q出力全ページデータ.f8を有効なフィールド名、または式として認識できません。の件
無事できましたが下記のような感じで大丈夫でしょうか？
「Q出力店舗」
お願い④の件
少しお時間をください。
追加アドバイスありがとうございます。
まだ確認できてない部分もありますのでまた後日書き込みます。
現在、ウィザードの設定で、テーブルAとテーブルBのリンクが出来た、
リンクが無いデータも登録出来る(＝参照整合性OFF)状態と想像します。
リレーションシップの設定で、結合線を引いてダブルクリックすると、参照整合性をONに出来ます。
活用する事で、リンク切れを予防出来る意味があるのでしょう。
ありがとうございます解説を読んでみます
>りんごさん
そうです。わざわざリレーションシップの設定でやる意味がわからずに質問しました
できないんですね
フォームの印刷プレビューボタンを押すたびに、レポートを閉じて新しく開くような仕組みにするしかなさそうですね
テーブルBのフォームにテーブルA(のフォーム)をサブフォームとして配置すると、ウィザードのようなものが表示され、自動的にリレーション出来るじゃないか！？という質問でよろしいでしょうか？
下記で解説してますのでご参照ください。
リレーションシップを設定した場合の利点 - hatena chips
印刷プレビューは更新や再クエリ出来ないので、いったん閉じて再度開くしかないと思います。
合計数量の表示をVBAで対応する場合
レポートデザインで「Py」にヘッダーセクションを付けます
改ページを「カレント セクションの前」に設定します
※グループヘッダー1は「Py」のヘッダーの名前です。実際のデータに合わせて変更してください
あれこれ試していたら思っていたよりも簡素になりました。ただ、これが何で正しく動くのかというのは結構難解な気がします。解説は必要であれば、ということで
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出力商品リスト;
計算式を理解してもらいたいのですが、「Px」が「0」の時だけ[合計数量]を表示する（それ以外の時は何もしない＝空白になる）という処理をしています。
『「Px」が「0」』とは列データが「A店～K店」のデータですね。クロス集計クエリで全データ出力したときの左端のページということです。その上で「データがないときはページを印刷しない」という処理を追加しているので『ページのグループ（Px）が「0」の時』と固定すると「Px=0（A店～K店）」のデータがないときは[合計数量]を表示するページが印刷されないということです。
そうなるだろうなぁと思っていました。とりあえず、計算式でとんでもない無駄をしていました
少しは速くなると思います。いずれにせよ、これが必要ならデータベース的な計算をさせるととても苦しいモノなのでVBAで対応する案件かと思っています
クエリの整理が必要かなぁと思っていたので触れていなかったんですが、「Q出力店舗」の問題ですね。これがクロス集計クエリなので、データ数が少ないと列数が足りなくなります。データを変えながら「Q出力店舗」を開いてみてください。このクエリでフィールドが固定であればいいのでクエリ列見出しに
"f0","f1","f2","f3","f4","f5","f6","f7","f8","f9","f10"と設定すれば良いでしょうクエリの問題ですか？レポートデザインの問題ですか？
元データのフィールドはどうなっていますか？（「数量」自体も実際に存在するフィールドなんでしょうか？）
レポートのデザインであれば、「ケースの数量」「バラの数量」「ケースの文字」「バラの文字」の4つのテキストボックスがあることを想定しています
現状の運用実態は下記のようでしょうか。
売上データには日々に売り上げが入力されていく。
請求書は前月の売上データをまとめて請求する。
上記であっているとして、一例をあげておきます。
売上テーブル
売上ID
取引先ID
売上日
商品コード
金額
請求ID
請求書テーブル
請求ID
請求日
請求年月
取引先ID
上記以外に 取引先マスター、商品マスター がある。
請求書テーブルをレコードソースにした単票フォーム「請求書フォーム」を作成する。
売上テーブルをレコードソースにした帳票フォーム「請求明細フォーム」を作成する。
「フィルター」プロパティを「False」、「読み込み時にフィルター適用」プロパティを「はい」に設定しておく。
「請求書フォーム」に「請求明細フォーム」をサブフォームとして埋め込む。
メインフォーム「請求書フォーム」でデータを入力する。
「請求年月」を入力したときに、VBAで請求年月を元にサブフォーム「請求明細フォーム」のフィルターに該当年月のデータが表示されるように抽出条件を設定する。
その後、抽出されたレコードの「請求ID」フィールドに、「請求書フォーム」の「請求ID」を入力する。
請求書レポートは、請求書テーブルと売上テーブルを「請求ID」で結合したクエリをレコードソースとする。
これを 請求ID でフィルターをかけて印刷する。
上記のような流れになります。
これは一例ですので、要件によっては別のほうほうの方がいい場合もあります。
サブフォームの認識間違っていましたか？
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ヘッダーで表示するものもないのでグループの追加じゃなくて並べ替えの追加で良かったです
一つのテキストボックスでするのは難しいでしょうね。
VBAで空白を挿入して調整するとかすれば可能でしょうが、かなり面倒そうです。
単価 と 数量 をそれぞれテキストボックスとして配置して、その間に"✕"をラベルとして配置すればどうでしょう。
単価✕数量 が必要ない場合は、セクションのフォーマット時で非表示にすればいいでしょう。
レポートの詳細セクションにラベルやテキストボックスをそのようなレイアウトになるように配置すればいいでしょう。
分かりやすいようにラベルは薄いピンク色にしています。
あるいは、ユニオンクエリで縦並びに変換する方法もあります。
hirotonさん
全ての質問に回答ありがとうございます。
お願い①
印刷ページの順番の変更をお願いします。
レポートのデザインでグループの追加で「Py」を追加して「Px」の上にします
一つ目でつまずいてしましました。
自分なりに試行錯誤しましたができませんでした。
[訪問実績]テーブルをもとにクエリを作成して、必要なフィールドを表示させて、
フィールド蘭に下記の式を入力してください。
アクセスログ日時: (SELECT Max(日時) FROM アクセスログ AS T1 WHERE 訪問実績.機器番号=T1.機器番号 AND 訪問実績.日時>=T1.日時 )
レポートのデザインでグループの追加で「Py」を追加して「Px」の上にします
レポートのデザインで合計数量のコントロールソースを
=IIf([Px]=0,[合計数量])にします。ただし、「1店～11店」の出力がない商品グループの場合、合計数量が表示されなくなります。これに対応する場合は=IIf([Px]=DMin("Px","Qレポート出力データ","Py=" & [Py]),[合計数量])とします連番付けのルールを「店舗CD」に対して行えばいいですね
Q出力店舗リスト
Q出力店舗リスト_連番付き
数量のフィールドを表示するために
を作りました。ケースの値も表示したいなら
とかでフィールドを増やせばいいかな？（数量用=>n0～n10、ケース用nC0～nC10）
「ケース」「バラ」の表示はそれぞれ
欲しい結果が得られているのなら問題ないと思いますが、テーブル作成クエリですか？
ことあるごとにテーブルそのものを作ったり消したりするのは負荷がかかるので削除クエリ（全データ削除）と追加クエリを使う形でデータだけを任意に変えられる作りのほうがいいでしょう
高速化自体はいろいろ方法があります。特に、クエリはかなり初心者向けの手順で作成しているので見直ししたいところではあります。SQLの知識が必要になるにも関わらず効果は薄そうなので微妙ですが
VBAを使えば劇的な効果もありそうですが、これはこれでやはりそれなりの知識が必要になります。
ワークテーブルへの書き込みを自動化する（簡単・効果小）
連番作成ルーチンを組む（検索すれば見つかるそれなりの方法・効果大）
レポートに出力した後印刷しない処理を組む（※）
（※）できるかどうかわかりません。できればワークテーブルもPy関係のクエリも不要で夢のようです
ただ、後で見直して「これ何やってるんだっけ？」となるようなものは非常にコストが高いので「運用上十分」で一番「楽な方法」ならそれで完成でいいんじゃないかという感じです
hirotonとしては技術的な話題も嫌いではないので、現状問題はないんだけどどうにかならないか？というのもそれはそれでアリですけどね
お願い⑤
レポートのテキストボックス1に商品名を登録します。
テキストボックス2～4は商品情報を登録します。
例えば、商品名が15文字あったとして、
テキストボックス1(上)に10文字、テキストボックス1(下)に5文字
のように分けて登録することは可能でしょうか？
フィールドを[商品名前半10文字][商品名後半5文字]と分けて
テキストボックスにそれぞれ登録すればできるとおもいますが、
[商品名]のみのフィールドでできますでしょうか？
連続投稿申し訳ありません。
回答できる方よろしくお願いします。
お願い④
[数量]のフィールドを値にしてるのですが、
これを[ケース]＆[バラ]の値にしようと思ってます。
この場合、[ケース]もしくは[バラ]の値がゼロの場合
どちらか一方を空白にすることは可能でしょうか？
普通にやれば
[ケース]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 空白
ケース 空白
値があるときだけケース、バラ表記をつけて
空白のときは空白にできますでしょうか？
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]&[店舗名]にすれば順番になるのですが、
見た目が店舗名だけの情報のほうが分かりやすいので。
良い方法があればアドバイスお願いします。
細かなチェックはまだしてませんが、ほぼ理想の形になりました。
ありがとうございます。