アクセスのクエリ

質問

アクセスのクエリで、
ある会社に"課"という組織があり、1つの課に1係、2係、3係とあります。
1-1,1-2,1-3,2-1,2-2,2-3...とします。
アクセスのテーブルが2つあり、1つは、消費テーブル、もう1つは、注文テーブルです。
消費テーブルのフィールドは、"年月日"、"課と係（1-2など）"消費量"
注文テーブルのフィールドは、"年月日"、"課（1など係は考えず課だけで考える）"、"注文"となっています。
この消費テーブルと注文テーブルの"年月日"は同じ日ではありません。
これを月ごとに、例えば2009年10月分の1課の消費量の合計（1,2,3係合計）、注文数の合計、その差をクエリを使って表示させる様にするにはどうしたらいいですか？
エクセルにてイメージを作ってみました。
誰か教えて下さい。よろしくお願いします。

回答No.1

noname#245936 · ベストアンサー

えー。
すいません、当方はSQL Server使いなのでSQLの方言があれば
ごめんなさい。

まず、月次集計がほしい時、年月日、DateTime型フィールドは
ちょっと使いにくいので、Accessで中間処理の「ビュー」を作る
べきだと思います。
ビューのテーブル型としては、消費テーブルも、注文テーブルも
整形してこんな感じかな。
　年（文字列）　…2009
　月（文字列）　…10
　部署（文字列）…1
　課　（文字列）…1
　量　（10進）　…5000など

雰囲気では、
SELECT
　文字変換(YEAR(年月日)) AS 年,
右２文字（"00" & 文字変換(MONTH(年月日))) AS 月,
右２文字（"00" & 文字変換(DAY(年月日))) AS 日,
　左１文字（部署） AS 部署,
消費 AS 数量
FROM 消費テーブル
注文テーブルも同様にこさえます。
これを、それぞれ、消費中間ビュー、注文中間ビューとします。

ついで、これの集計を出すのは。
月次でよければ、
SELECT 年,月,部署,SUM(数量) FROM 消費中間ビュー
(WHERE 年 = ほしい年 AND 月 = ほしい月 : なくてもよい）
GROUP BY 年,月,部署
ORDER BY 年,月,部署
これで、グループ集計結果がだらだらーと表示されます。
注文も同様。

で、これを一撃で出したいのだから…
長いですよ…
SELECT
年,月,部署,SUM(消費),SUM(注文) FROM
(
(SELECT
　 年,月,部署,SUM(数量) AS 消費,0 AS 注文 FROM 消費中間ビュー
　 (WHERE 年 = ほしい年 AND 月 = ほしい月 : なくてもよい）
GROUP BY 年,月,部署) AS A
UNION ALL
(SELECT 年,月,部署,0 AS 消費,SUM(数量)AS 注文 FROM 注文中間
ビュー
(WHERE 年 = ほしい年 AND 月 = ほしい月 : なくてもよい）
GROUP BY 年,月,部署) AS B
) AS X
GROUP BY 年,月,部署
ORDER BY 年,月,部署

こんな雰囲気になるでしょう。

お礼

丁寧にありがとうございます。
やってみます。

回答No.4

noname#245936

No.1の者です。
おおー、No.2様のSQLのほうがいいかも。
僕のやり方は、結合忘れちゃって、バグってますね。

すいませんでした。

回答No.3

30246kiku

＃２です

ごめんなさい。

一応動くには動くのですが、中途半端なものを書いてしまいました。

指定した期間で出したいですよね。

文字通り参考ということで。

回答No.2

30246kiku

一例となります。

SELECT
Format(T1.年月,"@@@@年@@月") AS 年月
, T1.部 AS 部署
, T1.消費
, T2.注文
, T2.注文 - T1.消費 AS 差
FROM
(SELECT
Format(消費テーブル.年月日,"yyyymm") AS 年月
, Left(消費テーブル.部署,InStr(消費テーブル.部署,"-")-1) AS 部
, SUM(消費テーブル.消費) AS 消費
FROM 消費テーブル
GROUP BY Format(消費テーブル.年月日,"yyyymm")
, Left(消費テーブル.部署,InStr(消費テーブル.部署,"-")-1)) AS T1
LEFT JOIN
(SELECT
Format(注文テーブル.年月日,"yyyymm") AS 年月
, 注文テーブル.部署 AS 部
, SUM(注文テーブル.注文) AS 注文
FROM 注文テーブル
GROUP BY Format(注文テーブル.年月日,"yyyymm")
, 注文テーブル.部署) AS T2
ON (T1.年月 = T2.年月 AND T1.部 = T2.部);

※　消費テーブルを主に考えているので、
例に於いて　注文テーブルに１１月のデータがあっても表示されません。

逆に、消費テーブルに９月のデータがあると表示されますが、
注文、差　部分は NULL （カラ）で表示されます。