SELECT
棚番号
, Sum(数量) AS 合計数量
, G
, f0
, f1
, f2
, Sum(IIf([店舗名]=[f0],[数量])) AS n0
, Sum(IIf([店舗名]=[f1],[数量])) AS n1
, Sum(IIf([店舗名]=[f2],[数量])) AS n2
FROM
テーブルA
, T_出力店舗
GROUP BY
棚番号
, G
, f0
, f1
, f2
;
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "クロス集計クエリ", cnn, adOpenKeyset, adLockOptimistic
'Excelオブジェクトを生成
Dim xls As Object
Set xls = CreateObject("Excel.Application")
With xls
'テンプレートブックを開く
Dim wb As Object
Set wb = .objExcel.Workbooks.Open("C:\TEST\テンプレート.xlsx")
'最初のシートにデータ出力
wb.WorkSheets(1).Cells(1, 1).CopyFromRecordset rst
'Excelを可視状態にする
.Visible = True
’印刷する。
wb.WorkSheets(1).PrintOut
End With
Set xls = Nothing
rst.Close: Set rst = Nothing: Set cnn = Nothing
SELECT
棚番号
, Sum(数量) AS 合計数量
, f0
, f1
, f2
, Sum(IIf([店舗名]=[f0],[数量])) AS n0
, Sum(IIf([店舗名]=[f1],[数量])) AS n1
, Sum(IIf([店舗名]=[f2],[数量])) AS n2
FROM
テーブルA
, T_出力店舗
GROUP BY
棚番号
, f0
, f1
, f2
;
hirotonさん
「Q出力店舗」のレポート表示まで試してみました。
試したパソコンと実際に使うパソコンは違うのですが試したパソコンではまったく問題ありませんでした。
情報がごちゃごちゃしてしまいましたので画像貼ります。
hirotonさん
「ページごとに不要な列は出力しない」部分には対応してません。
私の勘違いでした。
出力して大丈夫です、申し訳ありません。
ただ全く値が無いページは無しでお願いします。
hirotonさん新しい案ありがとうございます。
まだ試せてませんので少しお時間をください。
「ページごとに不要な列は出力しない」部分には対応してません。
とのことですがわがままを言って申し訳ありませんが、この部分はぜひよろしくお願いします。
hatenaさんとhirotonさんお二人ともアイデアを出していただき感謝してます。
現状私のスキルでは何がいいかもよくわからずにいます。
情報後出しで申し訳ありませんが、理想のレイアウトの画像を貼り付けます(上の画像と同じです)。
hatenaさん、手順までありがとうございます。
わかりやすくすぐできました。
実際にしてみてやはりレイアウト的に厳しい感じです。
情報後出しで申し訳ありませんが、
エクセルでの現状のレイアウトと理想のレイアウトの画像貼り付けます。
やり方がよくわかってなく、私の勘違いでしたらすみません。
ワークテーブル（T_出力店舗）を入力すればレポート表示できることは見てもらえたと思います。これを「VBAで」と言っていたんですが、実はクエリで作成できます
元になる店舗名のリストクエリ
Q出力店舗リスト
これで
のデータが取れます。これを、
のように連番をつけて計算フィールドを増やしてやれば、Gを行見出し、fを列見出し、店舗名を値にしてクロス集計クエリをつくることで、ワークテーブルの形のデータを得ることができます
Q出力店舗リスト_連番付き
Q出力店舗
この「Q出力店舗」をレポート出力用クエリで指定している「T_出力店舗」と入れ替えれば完成です
クエリで連番を振る方法は遅い処理です。実用に耐えられるか試してみてください。「Q出力店舗」の表示までなら耐えられるけど、レポート表示の段階だと遅すぎるなんて場合もあるかもしれません。そもそも「Q出力店舗」の表示すら遅いということもあるかもしれません。状況に応じて「じゃあその部分はVBAで」となると思います
順序だてて説明するために複数のクエリを作ることにしました。この辺もクエリの作り方によっては整理できるので、「考え方」としてうまくいくようであれば整理したクエリに変更したいと思います
>> 11の、特に「ページごとに不要な列は出力しない」部分には対応してません。>> 14で回答した通りVBAで商品グループごとn回処理するか、何か別案を思いつくか、hatenaさんがExcel出力の方法を提示していますし、もんぞうさんが進められる方法で、として進むようであればまた続きを考えたいと思います
追加の仕様については>> 11で返信しました。続きはVBA・・・なんて言っておきながらですがちょっと進んだ案を思いついたので半歩だけ進めます
ワークテーブルを次のように変更します
T_出力店舗
Gのデータは0から始まる連番です
（名前に特に意味はありません。列Groupの番号くらいの意味です）
出力する店舗数で行も増減します
レポート出力用にクエリを作成します
上記クエリをレコードソースにしてレポートを次のようにします
グループの追加：G（改ページ：カレントセクションの前）
並べ替えの追加：棚番号
これで全データの出力をレポート出力1回でできます。データ量（処理時間）的に問題なければこの方法で進めたいと思います
把握しました。
表示の仕方自体は商品名と同じような感じでしょうか？場合によっては1ページ名用レポートと2ページ目以降用レポートで2つ作ってしまったほうがいいかもしれませんね。
サンプルで言うと
商品あ～商品ち=>店舗C店～店舗M店、店舗N店
商品つ～商品と=>店舗A店～店舗K店、店舗L店～店舗N店
という形で、一ページに含まれる商品のパターンで列が変わるということですね
データベース的な考え方をすると、列と言ったらその対象は常に同じでないといけない（「商品あ」の1列目が「A店」なら「商品い」の1列目も「A店」、「商品と」の1列目も「A店」）のですが、求めるレポートはこれではないということですね
「一ページに含まれる商品の組み合わせ」が決まって初めて出力すべき列（店舗）が決まるということになるので、さらに処理を追加してあげないといけませんね
今度こそ、大元のデータを商品のグループで17行項目（出力する行数）ずつ区切って処理するような方法が必要になりそうです
手順
クロス集計クエリをデータシートビュー表示して全選択してコピー、エクセルのシートのA1に貼り付け
[ページレイアウト]-[ページ設定]-[サイズ]で「A3」 を選択、[印刷の向き]で「横」を選択。[印刷タイトル]をクリック。
[ページ設定]ダイアログが開くので、[タイトル行]、[タイトル列]を指定する。（マウスドラッグで指定できます。）
1ページ分の範囲が点線で表示されているので、希望の行数、列数になるように行幅、列幅を変更する。
[ファイル]-[印刷]でプレビューを確認。
1ページ目
2ページ目
3ページ目
4ページ目
印刷設定が済んだブックをデータを削除してからテンプレートとして保存する。
■T'sWare Access Tips #686 ～レコードセットの内容をそのままExcelのシートに出力する方法～の方法で、上記のテンプレートブックにクロス集計クエリのデータを出力する。印刷する。（「Microsoft ActiveX Data Objects * Library」に参照設定必要）
2ページ目、3ページ目に空欄の合計列が必要な場合は、対応がちょっと難しそう。
空欄の合計列は必要ないとは個人的には思います。
hatenaさん
自分の投稿を見直してみて書いてなかったことに今気づいたのですが印刷は1ページにA3横に収めたいです。
この件と関係なかったらすみません。
レイアウト的に厳しそうでしたので、
教えてもらったやり方は実際には試してませんでした。
明日時間があれば試したいと思います。
いろいろありがとうございます。
hirotonさん代案ありがとうございます。
試しに作ってみましたが2ページ目の合計数量は空白でお願いします。
棚番号・商品名の順番はページがまたいでも固定ですが、合計数量は
最初のページのみの表示でお願いします。
他はとりあえず問題なさそうですが
商品CD・JANCD・容量・入数などの行フィールドが他にあります。
あと言い忘れてたことで問題になりそうなことが一つありまして
画像①画像②のあ～ちの商品の数量がない店舗A店と店舗B店の
表示は無しでお願いします。
画像③と画像④は店舗A店と店舗B店の数量がありますので
表示はありでお願いします。
自分で作ってましたらそこでまた引っ掛かりました。
説明が分かりにくかったらすみません。
※印刷は1ページにA3横でお願いします。
ちょっと意味が分かりません。
エクセルは列は最大 16,384列まであります。Accessのクエリは最大255列までですので、列が入らないということはないです。
とりあえず、現状のクロス集計クエリをコピーしてシートに貼り付けてみてください。
そのうえで下記の方法で、行見出し、列見出しを固定すれば簡単に希望の印刷ができるはずです。
Excelで見出しを固定して全ページに印刷！印刷タイトル
それができるのが確認出来たら、VBAでCopyFromRecordsetでエクセルシートに出力するようにして自動化すればいいでしょう。
hirotonがワークテーブルを挙げているのは、処理の流れが分かりやすくなるのとクエリのデザインビューが活用しやすいという点からですね。データベース的な目線からは無駄が多いような気もしますが、理解しやすいんじゃないかと
さて、改めて画像を眺めていたんですが、1ページ目と2ページ目は書式は同じですが合計数量のデータが違っていますね。2ページ目以降は空白になったほうがいいんでしょうか？
それと、hirotonの見落としですが、合計数量はデータ全体の合計になっているようので、11店舗ずつ区切るとちょっと手間が出そうです（合計数量を表示するクエリと11店舗分のクエリを別々に作って後で繋げる）
ACCESSでやる方法ならhatenaさん提示のクロス集計クエリで列見出しを固定する方法のほうがよさそうです
クロス集計クエリの列を固定する方法がよさそうではありますが、せっかくなので代案（訂正案）を出します
表示列数分のワークテーブルを用意します（サンプルのため3店舗に簡略）
T_出力店舗
レポート出力用にクエリを作成します
上記クエリをレコードソースにしてレポートを次のようにします
ページヘッダー内の「棚番号」と「合計数量」はラベル、その他はテキストボックス（表示内容はコントロールソース）です
このレポートをプレビュー表示すると望みの1ページ目になりますか？
うまくいくようだったらT_出力店舗の各フィールド(f0,f1,f2)を「店舗D店」「店舗E店」「店舗F店」に書き換えて再度レポートをプレビュー表示してください。望みの2ページ目になりますか？
これでうまくいくようであれば続き（VBAで店舗を書き換えてレポートを表示する部分）に進みます
はい、そうです。
方法はいろいろありますが、DAOのRecordsetを使う方法がいいでしょう。
新規レコードの追加なら AddNewメソッド、
既存レコードの更新なら、主キーで該当レコードを探して、Editメソッドで、
ということになります。
レコードの更新・追加・削除（DAO編)
従業員は固定です
たとえば0なら転記しないように記述して、転記ボタンで別テーブルに、書き出すということでしょうか？
別テーブルには
aaa りんご 10
aaa みかん 5
となるように転記するんですよね？
要件次第です。
従業員がその時、その時で異なるなら、全レコード削除。
入力するときに必要な従業員を追加するということになります。
従業員が固定なら、従業員以外のフィールドの値を0にする、ということになります。
転記するのはVBAでかき、上記のテーブルは毎回クリアされ0レコードにするということですか？
エクセル関数を利用しようと参照設定に入れていましたが
どうも、これがいけなかったようです。
いまのところ、勝手な再起動は起きていません。
もし、また勝手な再起動が起きたら報告します。
エラーがでるフォームは、自動的にインポート対象から外れてインポートされていませんでした。
現在のところ、勝手な再起動は起きていません。
もしかしたら、エクセルの参照設定が干渉していたのかも？
引き続き、報告します。
そのエラーがでるフォームを使用していないなら、インポート対象から外して（チェックを外す）インポートしたらどうでしょうか。
やはり、勝手に再起動が起きます。
参照設定でまったく使用していない
Microsoft Excel 16.0 Object Library
をはずしてみます。
今、リフレッシュを実行しました。
すると、５、６個のフォームで
「レコードがありません」や「検索キーが・・・」
などのエラーメッセージがでて取り込めませんでした。
これらは、使用していないフォームです。
たぶん、これが原因だったのではないかと思います。
このまま使用して、意味不明の症状がでるようなら再度質問します。
返信ありがとうございます。
今、データ型の集計をすべて排除して、作業していたのですが、
中断中に変数の値を確認するためにマウスポイントを合わせている
最中に突然で再起動が起こりました。
チェック作業する前に必ず、上書保存をするようにしていますので
実害はありませんが、いったいどうなっているのか分かりません。
私には、大量のデータを扱っている以外に、思い当たる原因がありません。
何かアドバイスをお願いしたいです。
今作業中なので、後で、以前教えて頂いたリフレッシュをやってみようと思います。
https://hatenachips.blog.fc2.com/blog-entry-117.html
このページの記事以前見たことがありました。
レポートには使えないと思ってましたが使えるのですね。
新しいアドバイスありがとうございます。
エクセルでの印刷の件です。
せっかくアドバイス頂き誠に申し訳ないのですが、行のフィールドが実はまだあります。
棚番号・商品名だけの情報しかあげてませんでしたが、他に商品CD・JANCD・容量・入数などがあります。
これらの情報をエクセルで作ろうとすると行が横長になってしまい列がほとんど入りません。
エクセルでもできるのかも知れませんが現状まったくわからない状況でした。
アクセスなら自在に編集できるので問題ないのですが。
必要ないと思い情報を省略してました、すいませんでした。
お二人のアドバイスは正直難易度が高いです。
ただアイデアとしてはなんとなく分かるような気がします。
現状ですが、1～11・12～22・23～33・・・の店舗名のクエリをそれぞれ作ってクロス集計しようと思ってます。
レポートは以前試した下記のページのやり方でやってみようと思ってます。
http://www.ruriplus.com/msaccess/tch/tch_007.html
印刷して空白ページを印刷しないVBAで印刷されなければいいのですが。
あとはマクロで処理しようと考えてます。
空白ページが印刷される場合は失敗です。
説明不足だと思いますがこれでできたらいいのですが。
おそらくこの辺の処理をお二人のやり方でやれば早いのでしょうが文章を読んだ限りかなり難しそうな印象です。
時間はかかると思いますが調べながらお二人のやり方も試してみようとは思ってます。
データ型の集計は比較的最近追加されたものなので、安定しないのかもしれませんね。
同様のことはクエリの演算フィールドで可能ですので、私は使ったことがありません。
Access独自の機能なので、外のデータベースに移管するときの障害にもなりますので。
今、気が付いたのですが
当初のデータベースが破損しています。というエラーメッセージは
データ型の集計を排除したら起きなくなりました。
それで、これを使用すると不安定になるんだなと考え使用しないようにしていましたが
今見ると、簡単な引き算の集計がまだ残っていました。
これを排除して使用してみようと思います。
私がやるなら、VBAでクロス集計クエリの列名を固定したSQL(店名11列)をVBAで生成して、レポートのレコードソースにして出力を、店名を変更して繰り返すという方法を使います。
列名が変わるので、下記の方法を使用します。（レポートでも同じ方法が使えます。）
列名が変化するクロス集計クエリと連結するフォーム - hatena chips
それなりに難易度が高いのでエクセルへ出力する方法を提案しました。
hirotonさんのワークテーブルを使う方法も、VBAでレコードセットを操作する方法になれていないと難易度は高いです。
エクセルの方法は現状のクロス集計クエリをCopyFromRecordsetでエクセルに出力できたら、あとはエクセルの印刷設定だけなので比較的簡単かなと思い、提案しました。
手順としては、下記になります。
事前にExcelで見出しを固定して全ページに印刷！印刷タイトルの方法で見出しを固定したワークブックを作成しておく。標題などが必用であればそれも入力しておく。
VBAで上記のワークテーブルを開いて CopyFromRecordsetでクロス集計クエリのデータを指定位置に出力する。
シートを印刷する。
以上です。
どの辺で躓いていますか。
1ページずつ印刷の発想はなかったのでまったく考えてませんでした。
初心者には難易度が高そうな気がしますが少しずつ調べながら試してみます。
通常実際に印刷する枚数は多くても30ページぐらいですので処理のほうは問題ないと思います。
エクセルで加工して印刷も試したのですが思い通りにはいってなかったので助かります。
また何かありましたら質問させていただきます。
ありがとうございます。
書式は固定、各ページ11店舗でいいのなら、1グループ目はA店～K店、2グループ目はL店～V店・・・と11店舗ずつn回印刷処理をすればいいので出力する店舗リストをクエリで作ってVBAでレコードセット取得してループ処理すればできるんじゃないかな？
具体的には出力処理用の1列（店舗名）11行（列数分）のワークテーブルをつくってクロス集計するときに一対多で結合させれば最大11店舗分のデータになります。ループ処理でワークテーブルを1行目から書き換えていって11行目まで書き換えたら印刷実行、ワークテーブルのレコードは先頭にもどし、店舗リストは続けて最後までを繰り返せばうまくいくような気がします（最後のグループだけワークテーブルの直前の出力のゴミ処理も必要です）
現実的に500ページもの印刷に耐えられるかというと何とも言えないのとn回の処理に分けた方法なので、1つのレポートで表示させたいとかなると別な方法が必要ですが
Accessのテーブルです。
下記のようなテーブルを作成しておきます。
入力用に使うテーブルで、入力後、本テーブルに転記した後は、入力データは削除して空欄にしておきます。
別案についてですが、ワークテーブルとは作業するシートということで、EXCELで入力して
ACCESSにインポートするということですか？
サブフォームコントロールを複数配置してレイアウトを工夫するという方法があります。
下記にサンプルがあるので参考にしてください。
連結入力フォームのレイアウトを自由に設計したい - hatena chips
別案
最初の画像のような形のワークテーブルを作成しておいて、入力フォームはこのテーブルをレコードソースとして作成。
入力後、VBAで本来のテーブルの形に変換して出力する。
Accessだけでは難しそうですね。
教えていただいたサイトを参考にエクセルも使いながら作ってみます。
また分からないことがあれば質問させていただきます。
ありがとうございました。
画像では、1ページ当たり、店舗数11、行数17，ですので、
横に19ページ分(200/11)、縦に30ページ分（500/17）必要ということになります。
19×30で570ページ分、これはちょっと現実的ではないかなと、特に横に19ページ分というのは難しそうです。
レポートの最大幅は55.87cmですので。空白ページの処理も面倒そう。
エクセルを利用するのも可なら、エクセルに出力する方法の方が簡単かも知れません。
CopyFromRecordsetメソッドを使えばクエリデータをエクセルのシートの指定箇所に一気に出力できます。
■T'sWare Access Tips #686 ～レコードセットの内容をそのままExcelのシートに出力する方法～
タイトル行、タイトル列の各ページ固定出力は下記を参考に。
Excelで見出しを固定して全ページに印刷！印刷タイトル
店舗数は最大で200、行数は最大で500ほどあれば十分です。
実際は1店舗1行だけのときもあります。
列を動的に作ろうと試したのは下記のサイトを参考にしました。
http://www.ruriplus.com/msaccess/tch/tch_007.html
空白ページを印刷しないVBAを調べたのですが分かりませんでした。
実際に出力する店舗数はどのぐらいになりますか。（10000すべて印刷する訳ではないですよね。）
また行数もどのくらいになりますか。
これは具体的にどのようにしましたか。
hirotonさん、情報ありがとうございます。
勉強になりました。
Accessの場合、作りこめばできないこともないですが、かなりの工数が必要ですね。
また、ファイル破損の可能性もつきまといます。定期的なバックアップをとっていても、直近のバックアップ以降に発行した請求データは復元できません。
その辺を考慮すると印刷(送付用)とPDF出力(控え)を同時にするという運用がいいのかなと思っています。手軽な割に有用性が高いと思います。自分の作成したシステムはそのようにしています。
懇意の会計士さんとしっかり相談されるのが一番でしょう
とはいえせっかくなので適当にWeb検索してみました
インボイス制度に関するＱ＆Ａ目次一覧（国税庁）
問51～に電子データに関する内容がありますね（更新で変わってるかもしれません）
決定的なこととして「所轄税務署長の承認」を受けること
ざっくりとしたことで「わかりやすいマニュアルがあること」
ずばり記述されてるシステム要件であれば
等記述がありますね。続くQAにもシステムの要件に関わりそうな記述があります。データの保存部分で言うとデータベース的な保存でもいいけど、発行したものは「修正できない」か「修正した記録がすべて残る」システムである必要があるようです
できることなら両方でやって損はないということですね
少しACCESSからはずれてしまいますが、今からシステムをつくるにあたり、対応していたほうがよいと思うので質問させてください
インボイス制度が開始されると請求書を発行した側にも保存義務があるそうです
ご指摘の通りデータベースの利点として再発行が容易、というのはありますが
そのような運用ですと、請求書を保存していることにはならなくなり、義務違反となってしまうのでしょうか？
何を持って請求書の控えとするか、という問題になりそうですが、データベースから持ってきてレポートを作成して同じものを出力できても
データベースから作成したものは控えではない、とかいわれそうな気もします
業務の要件次第かと。
請求書だと、客先に送るものと、手元に控え(コピー)を保存しておく必要がある場合は、2枚印刷するのは用紙の無駄なので、
印刷するときに、プリンター出力とPDF出力を同時にすることがあります。
出力先は、下記のリンク先の方法で変更できます。
■T'sWare Access Tips #287 ～特定のプリンタでレポートを印刷する～
レポート用にプリンターを選択するプロセスを自動化する | Microsoft Docs
データベースにデータがあるので再発行は簡単にできますが、
トラブルがあったときにデータベースのデータだと簡単に編集ができるので、
控えとして有効性が低いです。