Microsoft Access 掲示板

実際に出力する店舗数はどのぐらいになりますか。（10000すべて印刷する訳ではないですよね。）
また行数もどのくらいになりますか。

列を動的に作ろうとしましたが空白ページが印刷されます。

これは具体的にどのようにしましたか。

店舗数は最大で200、行数は最大で500ほどあれば十分です。
実際は1店舗1行だけのときもあります。

列を動的に作ろうと試したのは下記のサイトを参考にしました。
http://www.ruriplus.com/msaccess/tch/tch_007.html
空白ページを印刷しないVBAを調べたのですが分かりませんでした。

画像では、1ページ当たり、店舗数11、行数17，ですので、
横に19ページ分(200/11)、縦に30ページ分（500/17）必要ということになります。
19×30で570ページ分、これはちょっと現実的ではないかなと、特に横に19ページ分というのは難しそうです。
レポートの最大幅は55.87cmですので。空白ページの処理も面倒そう。

エクセルを利用するのも可なら、エクセルに出力する方法の方が簡単かも知れません。

CopyFromRecordsetメソッドを使えばクエリデータをエクセルのシートの指定箇所に一気に出力できます。

■T'sWare Access Tips #686 ～レコードセットの内容をそのままExcelのシートに出力する方法～

タイトル行、タイトル列の各ページ固定出力は下記を参考に。

Excelで見出しを固定して全ページに印刷！印刷タイトル

Accessだけでは難しそうですね。
教えていただいたサイトを参考にエクセルも使いながら作ってみます。
また分からないことがあれば質問させていただきます。
ありがとうございました。

書式は固定、各ページ11店舗でいいのなら、1グループ目はA店～K店、2グループ目はL店～V店・・・と11店舗ずつn回印刷処理をすればいいので出力する店舗リストをクエリで作ってVBAでレコードセット取得してループ処理すればできるんじゃないかな？

具体的には出力処理用の1列（店舗名）11行（列数分）のワークテーブルをつくってクロス集計するときに一対多で結合させれば最大11店舗分のデータになります。ループ処理でワークテーブルを1行目から書き換えていって11行目まで書き換えたら印刷実行、ワークテーブルのレコードは先頭にもどし、店舗リストは続けて最後までを繰り返せばうまくいくような気がします（最後のグループだけワークテーブルの直前の出力のゴミ処理も必要です）

現実的に500ページもの印刷に耐えられるかというと何とも言えないのとn回の処理に分けた方法なので、1つのレポートで表示させたいとかなると別な方法が必要ですが

1ページずつ印刷の発想はなかったのでまったく考えてませんでした。
初心者には難易度が高そうな気がしますが少しずつ調べながら試してみます。
通常実際に印刷する枚数は多くても30ページぐらいですので処理のほうは問題ないと思います。
エクセルで加工して印刷も試したのですが思い通りにはいってなかったので助かります。
また何かありましたら質問させていただきます。
ありがとうございます。

私がやるなら、VBAでクロス集計クエリの列名を固定したSQL(店名11列)をVBAで生成して、レポートのレコードソースにして出力を、店名を変更して繰り返すという方法を使います。
列名が変わるので、下記の方法を使用します。（レポートでも同じ方法が使えます。）
列名が変化するクロス集計クエリと連結するフォーム - hatena chips
それなりに難易度が高いのでエクセルへ出力する方法を提案しました。

hirotonさんのワークテーブルを使う方法も、VBAでレコードセットを操作する方法になれていないと難易度は高いです。

エクセルの方法は現状のクロス集計クエリをCopyFromRecordsetでエクセルに出力できたら、あとはエクセルの印刷設定だけなので比較的簡単かなと思い、提案しました。
手順としては、下記になります。

• 事前にExcelで見出しを固定して全ページに印刷！印刷タイトルの方法で見出しを固定したワークブックを作成しておく。標題などが必用であればそれも入力しておく。

• VBAで上記のワークテーブルを開いて CopyFromRecordsetでクロス集計クエリのデータを指定位置に出力する。

• シートを印刷する。

以上です。

エクセルで加工して印刷も試したのですが思い通りにはいってなかったので助かります。

どの辺で躓いていますか。

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で印刷されなければいいのですが。
あとはマクロで処理しようと考えてます。
空白ページが印刷される場合は失敗です。
説明不足だと思いますがこれでできたらいいのですが。

おそらくこの辺の処理をお二人のやり方でやれば早いのでしょうが文章を読んだ限りかなり難しそうな印象です。
時間はかかると思いますが調べながらお二人のやり方も試してみようとは思ってます。

hirotonがワークテーブルを挙げているのは、処理の流れが分かりやすくなるのとクエリのデザインビューが活用しやすいという点からですね。データベース的な目線からは無駄が多いような気もしますが、理解しやすいんじゃないかと

さて、改めて画像を眺めていたんですが、1ページ目と2ページ目は書式は同じですが合計数量のデータが違っていますね。2ページ目以降は空白になったほうがいいんでしょうか？

それと、hirotonの見落としですが、合計数量はデータ全体の合計になっているようので、11店舗ずつ区切るとちょっと手間が出そうです（合計数量を表示するクエリと11店舗分のクエリを別々に作って後で繋げる）

ACCESSでやる方法ならhatenaさん提示のクロス集計クエリで列見出しを固定する方法のほうがよさそうです

クロス集計クエリの列を固定する方法がよさそうではありますが、せっかくなので代案（訂正案）を出します

表示列数分のワークテーブルを用意します（サンプルのため3店舗に簡略）

T_出力店舗

レポート出力用にクエリを作成します

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
;

上記クエリをレコードソースにしてレポートを次のようにします

ページヘッダー内の「棚番号」と「合計数量」はラベル、その他はテキストボックス（表示内容はコントロールソース）です

このレポートをプレビュー表示すると望みの1ページ目になりますか？
うまくいくようだったらT_出力店舗の各フィールド(f0,f1,f2)を「店舗D店」「店舗E店」「店舗F店」に書き換えて再度レポートをプレビュー表示してください。望みの2ページ目になりますか？

これでうまくいくようであれば続き（VBAで店舗を書き換えてレポートを表示する部分）に進みます

棚番号・商品名だけの情報しかあげてませんでしたが、他に商品CD・JANCD・容量・入数などがあります。
これらの情報をエクセルで作ろうとすると行が横長になってしまい列がほとんど入りません。

ちょっと意味が分かりません。
エクセルは列は最大 16,384列まであります。Accessのクエリは最大255列までですので、列が入らないということはないです。
とりあえず、現状のクロス集計クエリをコピーしてシートに貼り付けてみてください。
そのうえで下記の方法で、行見出し、列見出しを固定すれば簡単に希望の印刷ができるはずです。
Excelで見出しを固定して全ページに印刷！印刷タイトル
それができるのが確認出来たら、VBAでCopyFromRecordsetでエクセルシートに出力するようにして自動化すればいいでしょう。

hirotonさん代案ありがとうございます。

試しに作ってみましたが2ページ目の合計数量は空白でお願いします。
棚番号・商品名の順番はページがまたいでも固定ですが、合計数量は
最初のページのみの表示でお願いします。
他はとりあえず問題なさそうですが
商品CD・JANCD・容量・入数などの行フィールドが他にあります。

あと言い忘れてたことで問題になりそうなことが一つありまして
画像①画像②のあ～ちの商品の数量がない店舗A店と店舗B店の
表示は無しでお願いします。
画像③と画像④は店舗A店と店舗B店の数量がありますので
表示はありでお願いします。
自分で作ってましたらそこでまた引っ掛かりました。
説明が分かりにくかったらすみません。

※印刷は1ページにA3横でお願いします。

hatenaさん

自分の投稿を見直してみて書いてなかったことに今気づいたのですが印刷は1ページにA3横に収めたいです。
この件と関係なかったらすみません。

レイアウト的に厳しそうでしたので、
教えてもらったやり方は実際には試してませんでした。

明日時間があれば試したいと思います。
いろいろありがとうございます。

手順

1. クロス集計クエリをデータシートビュー表示して全選択してコピー、エクセルのシートのA1に貼り付け
   

2. [ページレイアウト]-[ページ設定]-[サイズ]で「A3」 を選択、[印刷の向き]で「横」を選択。[印刷タイトル]をクリック。
   

3. [ページ設定]ダイアログが開くので、[タイトル行]、[タイトル列]を指定する。（マウスドラッグで指定できます。）
   

4. 1ページ分の範囲が点線で表示されているので、希望の行数、列数になるように行幅、列幅を変更する。

5. [ファイル]-[印刷]でプレビューを確認。
   1ページ目
   
   2ページ目
   
   3ページ目
   
   4ページ目
   

6. 印刷設定が済んだブックをデータを削除してからテンプレートとして保存する。

7. ■T'sWare Access Tips #686 ～レコードセットの内容をそのままExcelのシートに出力する方法～の方法で、上記のテンプレートブックにクロス集計クエリのデータを出力する。印刷する。（「Microsoft ActiveX Data Objects * Library」に参照設定必要）

  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

2ページ目、3ページ目に空欄の合計列が必要な場合は、対応がちょっと難しそう。
空欄の合計列は必要ないとは個人的には思います。

追加の仕様については>> 11で返信しました。続きはVBA・・・なんて言っておきながらですがちょっと進んだ案を思いついたので半歩だけ進めます

ワークテーブルを次のように変更します
T_出力店舗

Gのデータは0から始まる連番です
（名前に特に意味はありません。列Groupの番号くらいの意味です）
出力する店舗数で行も増減します

レポート出力用にクエリを作成します

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
;

上記クエリをレコードソースにしてレポートを次のようにします

グループの追加：G（改ページ：カレントセクションの前）
並べ替えの追加：棚番号

これで全データの出力をレポート出力1回でできます。データ量（処理時間）的に問題なければこの方法で進めたいと思います

ワークテーブル（T_出力店舗）を入力すればレポート表示できることは見てもらえたと思います。これを「VBAで」と言っていたんですが、実はクエリで作成できます

元になる店舗名のリストクエリ
Q出力店舗リスト

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

これで

のデータが取れます。これを、

のように連番をつけて計算フィールドを増やしてやれば、Gを行見出し、fを列見出し、店舗名を値にしてクロス集計クエリをつくることで、ワークテーブルの形のデータを得ることができます

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

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

Q出力店舗

TRANSFORM Max(店舗名) AS 店舗名の最大
SELECT G
FROM Q出力店舗リスト_連番付き
GROUP BY G
PIVOT "f" & [f];

この「Q出力店舗」をレポート出力用クエリで指定している「T_出力店舗」と入れ替えれば完成です

クエリで連番を振る方法は遅い処理です。実用に耐えられるか試してみてください。「Q出力店舗」の表示までなら耐えられるけど、レポート表示の段階だと遅すぎるなんて場合もあるかもしれません。そもそも「Q出力店舗」の表示すら遅いということもあるかもしれません。状況に応じて「じゃあその部分はVBAで」となると思います

順序だてて説明するために複数のクエリを作ることにしました。この辺もクエリの作り方によっては整理できるので、「考え方」としてうまくいくようであれば整理したクエリに変更したいと思います

>> 11の、特に「ページごとに不要な列は出力しない」部分には対応してません。>> 14で回答した通りVBAで商品グループごとn回処理するか、何か別案を思いつくか、hatenaさんがExcel出力の方法を提示していますし、もんぞうさんが進められる方法で、として進むようであればまた続きを考えたいと思います

hatenaさん、手順までありがとうございます。
わかりやすくすぐできました。

実際にしてみてやはりレイアウト的に厳しい感じです。

情報後出しで申し訳ありませんが、
エクセルでの現状のレイアウトと理想のレイアウトの画像貼り付けます。

やり方がよくわかってなく、私の勘違いでしたらすみません。