こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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の中で完了させたく思っています。
ちなみにACCESS2003/WIN XPの環境です。

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

投稿日時 - 2013-09-04 15:27:19

QNo.8249307

すぐに回答ほしいです

質問者が選んだベストアンサー

#1です

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

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

細工1)クエリを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」だけの列名が存在する様になります。

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


別の方法)

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

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.商品;

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


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

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

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


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

投稿日時 - 2013-09-05 13:39:04

お礼

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

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

投稿日時 - 2013-09-09 14:29:21

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.1

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

以下をクエリの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.売上年月;


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

投稿日時 - 2013-09-04 17:19:46

お礼

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

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

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

投稿日時 - 2013-09-05 09:12:00

あなたにオススメの質問