ACCESSクロス集計クエリ

質問

accessクロス集計クエリで質問があります。

このようなテーブルがあります。(テーブル名：売上T)
売上年月,営業所,商品,数量,重量,売上金額
2013/08,東京,AAA，1，5，500
2013/07,名古屋,AAA，2，10，1000
2013/08,東京,BBB，3，30，900
2013/06,東京,CCC，4，50，4000
2013/05,大阪,AAA，5，25，2500
2013/08,東京,CCC，6，50，6000
2013/07,大阪,AAA，7，35，3500
2013/08,東京,AAA，8，40，4000
2013/06,名古屋,BBB，9，90，2700
2013/08,東京,AAA，10，50，5000

これをクロス集計クエリにて
営業所・商品をグループ化し売上年月毎で数量合計・重量合計・売上合計
と3つの表を作っているのですが
数量合計/重量合計/金額合計を同時に表示する事は可能でしょうか？

やりたいこと
営業所・商品／2013/07,2013/07,2013/08
東京AAA／(数・重・売),(数・重・売),(数・重・売)
東京BBB／(数・重・売),(数・重・売),(数・重・売)
東京CCC／(数・重・売),(数・重・売),(数・重・売)
大阪AAA／(数・重・売),(数・重・売),(数・重・売)
大阪BBB／(数・重・売),(数・重・売),(数・重・売)
大阪CCC／(数・重・売),(数・重・売),(数・重・売)
名古屋AAA／(数・重・売),(数・重・売),(数・重・売)
名古屋BBB／(数・重・売),(数・重・売),(数・重・売)
名古屋CCC／(数・重・売),(数・重・売),(数・重・売)

エクセルに吐き出しピボットとかではなく、ACCESSの中で完了させたく思っています。
ちなみにACCESS２００３/WIN XPの環境です。

どなたかアドバイスいただければ幸いです。

回答No.2

30246kiku · ベストアンサー

＃１です

> これを分解して一つずつのセルに表記

ということは、後者での方法という事で良いですか？
細工する部分がチョッとあります。

細工1）クエリを１つ、前準備として作っておきます

SELECT Q1.営業所, Q1.商品 ,Q2.売上年月, Q2.数量, Q2.重量, Q2.売上金額 FROM
(SELECT T1.営業所, T2.商品 FROM
(SELECT DISTINCT 営業所 FROM ★★) AS T1, (SELECT DISTINCT 商品 FROM ★★) AS T2
) AS Q1 LEFT JOIN ★★ AS Q2 ON (Q1.営業所=Q2.営業所) AND (Q1.商品=Q2.商品);

このクエリ名を「■■」とします。

細工2）クロス集計（後者）の「★★」部分を「■■」に変更します。

TRANSFORM Sum(Choose(▲▲.ID,■■.数量,■■.重量,■■.売上金額)) AS 値
SELECT ■■.営業所, ■■.商品
FROM ■■, ▲▲
GROUP BY ■■.営業所, ■■.商品
PIVOT ■■.売上年月 & ▲▲.F1;

上記は変更箇所も多くて、面倒くさいので

TRANSFORM Sum(Choose(Q2.ID,Q1.数量,Q1.重量,Q1.売上金額)) AS 値
SELECT Q1.営業所, Q1.商品
FROM ■■ AS Q1, ▲▲ AS Q2
GROUP BY Q1.営業所, Q1.商品
PIVOT Q1.売上年月 & Q2.F1;

とでもしておいた方が楽かも

細工3）表示結果に余分な列が出来上がります

添付図上段では、テーブル「▲▲」の「F1」を、_1数計 / _2重計 / _3売計　に変更していました。
売上年月が Null の場合、PIVOT ■■.売上年月 & ▲▲.F1;　により、
「F1」だけの列名が存在する様になります。

後は、その部分を非表示にするとか、読み飛ばすとかの細工が必要になります。

別の方法）

＃１での後者クロス集計のクエリ名を「☆☆」と仮定します。

SELECT * FROM
(SELECT T1.営業所, T2.商品 FROM
(SELECT DISTINCT 営業所 FROM ★★) AS T1, (SELECT DISTINCT 商品 FROM ★★) AS T2
) AS Q1 LEFT JOIN ☆☆ AS Q2 ON (Q1.営業所=Q2.営業所) AND (Q1.商品=Q2.商品)
ORDER BY Q1.営業所, Q1.商品;

とすると、添付図下段の表示となります。
後は、３・４列目「Q2.営業所」「Q2.商品」部分を非表示にするとか、読み飛ばすとかの細工が必要になります。
SELECT * FROM
で、全部を表示しているのでそうなりますが、表示するものを選んでおけば・・・・

上記の２例は、どの段階で全部のものと結びつけるか・・・の違いになります。

クロス集計する時に、PIVOT に Null の物を存在させて、それ用の列が出来ても良いか・・・
クロス集計したものと全部を結び付けて・・・・

処理的には、最後に書いた別の方法の方が速そうな雰囲気ですが・・・（裏付けなし）

※ 不都合あれば、ドンドン修正してください。

お礼

すごい！
本当にありがとうございました。

色々アレンジして改良してみます。

回答No.1

30246kiku

テーブル名を「★★」と仮定します。

以下をクエリのSQLビューに記述して表示を見ると添付図上段の様になります。

TRANSFORM Sum(★★.数量) & " " & Sum(★★.重量) & " " & Sum(★★.売上金額) AS 値
SELECT ★★.営業所, ★★.商品
FROM ★★
GROUP BY ★★.営業所, ★★.商品
PIVOT ★★.売上年月;

また、添付図中段のテーブル「▲▲」を追加して、以下に変更すると
表示は添付図下段の様になります。

TRANSFORM Sum(Choose(▲▲.ID,★★.数量,★★.重量,★★.売上金額)) AS 値
SELECT ★★.営業所, ★★.商品
FROM ★★, ▲▲
GROUP BY ★★.営業所, ★★.商品
PIVOT ★★.売上年月 & ▲▲.F1;

※ テーブル「▲▲」の「F1」の内容が _1 / _2 / _3 で始まっているのは、
　数字がなかった場合、数量/重量　の表示順が逆転します。
　逆転させないために、先頭に数字を設定し、そこまでのもので表示順を決めるものになります。

※ もし、営業所 / 商品 を総当たり？　
（提示されたサンプルデータには、大阪・BBB や 大阪・CCC はないけど、やりたい結果にある）
　とすれば、前者のやり方で良ければ以下の記述になるのかも
（ただ、列に見慣れない "<>" が出現しますけど・・・ Q2.売上年月 が Null を示すものになります）

TRANSFORM Sum(Q2.数量) & " " & Sum(Q2.重量) & " " & Sum(Q2.売上金額) AS 値
SELECT Q1.営業所, Q1.商品 FROM
(SELECT T1.営業所, T2.商品 FROM
(SELECT DISTINCT 営業所 FROM ★★) AS T1, (SELECT DISTINCT 商品 FROM ★★) AS T2) AS Q1
LEFT JOIN ★★ AS Q2 ON (Q1.営業所=Q2.営業所) AND (Q1.商品=Q2.商品)
GROUP BY Q1.営業所, Q1.商品
PIVOT Q2.売上年月;

※ 各処理性能はわかりません。

お礼

おお！素晴らしい、
本当にありがとうございました。

求めていた形になりました。

ちなみに、“営業所 / 商品 を総当たり”の場合
集計結果が一つのセル内で表記されていますが、これを分解して一つずつのセルに表記する事は可能でしょうか？