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
SELECT Py, Px, Sum(数量) AS 数量の合計
FROM (テーブルA INNER JOIN Q出力商品ページグループ ON テーブルA.棚番号 = Q出力商品ページグループ.棚番号) INNER JOIN Q出力店舗リスト_連番付き ON テーブルA.店舗名 = Q出力店舗リスト_連番付き.店舗名
GROUP BY Py, Px;
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
;
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]&[店舗名]にすれば順番になるのですが、
見た目が店舗名だけの情報のほうが分かりやすいので。
良い方法があればアドバイスお願いします。
細かなチェックはまだしてませんが、ほぼ理想の形になりました。
ありがとうございます。
あー、hirotonのミスです。直してOKです
処理内容からすれば想定通りです。そのほかの表記したい項目も含めて「ワンクリックぐらいで印刷できる」が達成できていればとりあえず完成かなと思っています。何か問題があれば教えてください
処理速度の問題についてはクエリの見直しで高速化できるか、どうしてもならVBAでどうにかするかとなると思いますが、気になるようなら実際に運用するときのデータ数とその時の処理時間を合わせて出してもらえるといいと思います。
趣味の映画鑑賞データをいれるものを作った程度です
そのときにいろいろなサイトをみたりして、ある程度の機能には触れてみました
ファイルメーカーにはサブフォームに似たポータルというものがありました
一応それらの近いは理解したつもりです
本来は1対多で入力するときに使うものですが、
フォームに検索用の窓をつくり、あるテーブルからその条件で検索した結果をサブフォームに表示させる、ということもできますよね？（ポータル機能もそのようなものでした）
hirotonさんへ
追加情報ありがとうございます。
「全く値が無いページ」は色を染めた部分で2ページになります。この考え方で合っていますか？
はい、問題ありません。
Qレポート出力データまで試してみました。
Qレポート出力データが少し重い感じで、レポート表示したときはかなり重い感じでした。
Q出力商品ページグループとQ出力商品リストに[棚番号]がなかったので追加しました。
間違ってたらすいません。
ファイルメーカーからの移行ということですが、Accessはすこしは使ったことがありますか。それとも、まったくないのでしょうか。
例えば、サブフォームというのは、Accessの場合、メインフォームの中にコントロールとしてフォームを埋めこんであるものさしますが、その認識は持ってますでしょうか。
その辺をはっきりしておかないと、やりとりでお互いの認識に齟齬が生じますに確認させてください。
続き
現在のレポート出力用のクエリにこの情報を追加してやれば不要なページを出力しないようにできます
「Px（各ページの列のまとまり）」はすでに求めていますね。「Q出力店舗のG」です。Q出力店舗の「G」は「Px」に変更します
「Py」も同様に求められます
Q出力商品リスト
Q出力商品ページグループ
これで、商品ほにゃららの店舗foo店はページ（Py,Px）に出力されるという情報ができました。これを組み合わせてページごとの数量の合計を算出します
Q出力ページリスト
サンプルの場合に実際に得られる結果は
です。合計が「0」は実際には「データがない」なので出力されません。「数量」が「0」で実際に登録されているだとか、マイナス数量が許容されてて合計「0」がありうるとかだとまた変わってくるのでこの方法は使えないかもしれません
必要な情報がそろったのでレポート出力用のクエリを作成します
元データにはページの情報が含まれていないのでワンステップ挟んで最終的なクエリを作成します
Q出力全ページデータ
Qレポート出力データ
これで、ページ単位でデータがないデータは取り除いたデータが取得できます。「G」→「Px」等変更になってるのでレポートのデザインも合わせて修正してください
計算がかなり増えているのでレポート表示にもそれなりに時間がかかりそうな気がします。出力時間が気になる場合は、クエリを細かく分けて作成しているので「ここで遅くなる」等チェックしてみてください
例えば次のようなデータ（1ページ=>5行3列）を考えます
「全く値が無いページ」は色を染めた部分で2ページになります。この考え方で合っていますか？
案件では、列も行も固定ではなく出力してみないとわからないという状態で、「ページ単位で見て」合計が0ならページを出力しないということですが、例えば
「商品お」の2ページ目のデータは「D店、E店」でデータはなし→ページ出力あり
「商品か」の2ページ目のデータは「D店、E店」でデータはなし→ページ出力なし
となっていて、レコード単位で出力の有無が決められません。なかなか難しくなってきました
サンプルデータのテーブルにすでに情報を追加していますが、ページ単位で考えるということで、ページの位置情報を追加して出力有無のデータを作ります
「データの合計」が「0」なら出力しないページということですね
長くなるのでいったん切ります
お騒がせしました。
あれから、勝手な再起動は起きていません。
テクニカル分析をしてみようと、
大量のデータをダウンロードしていました。
そして、分析にはエクセル関数が必要だろうと、
参照設定に入れていましたが、どうもこれが原因のようです。
関数は自作すればすみますので困ったことは起こりません。
私にとっては、困ったときのHatenaさんなので、
今後ともよろしくお願いします。