アクセスのクエリについて（前当期比較させたい）

質問

現在、アクセス2010を勉強中の超初心者です。
アクセスを用いて、前期と当期の商品売上数量比較表（得意先ごとの）を作成できないか考えております。

　■さっそくですが、以下の４つのテーブルがあったと仮定します。

テーブル(1)（商品マスター）　　　　　　　　　　　テーブル(2)（得意先マスター）
品コード　|　品名　　　　　　　　　　　　　　　　　　店コード　|　 店名
　　1　　　 |　○○　　　　　　　　　　　　　　　　　　 　　　　A　 |　　あか
　　2　　　 |　××　　　　　　　　　　　　　　　　　　 　　　　B　 |　　いき
　　3　　　 |　△△　　　　　　　　　　　　　　　　　　 　　　　C　 |　　うす

テーブル(3)（前期売上データ）　　　　　　　　　　テーブル(4)（当期売上データ）
店コード　|　品コード　|　売上数量　　　　　　　　　店コード　|　品コード　|　売上数量
　A 　　　 | 　　1　　 　|　 10　　　　　　　　 　　 　　 A 　　　　 | 　　3 　　 　|　 20　　　
　A　 　　 | 　　 3 　　 　|　 15　　　　　　　　　 　　 A　　 　　 | 　　 4 　　 　|　 25　　　
　B　 　　 | 　　 1 　　 　|　 17　　　　　 　　　　 　　 B　　　　 | 　　 2　　 　|　 27　　　
　：　 　　 | 　　 ：　　 　|　 ：　　　　　 　　　　 　　　 ：　　　　 | 　　 ： 　　 　|　 ：　　　

　■以下のようなデータ抽出できないかを検討しています。

店コード　|　品コード　|　前期売上数量　|　当期売上数量
　　A　　　 | 　　 1　　 　 | 　　　 10 　　　 　 | 　　　 0
　　A　　　 | 　　 3 　　 　 | 　　　 15 　　　 　 | 　　　 20
　　A　　　 | 　　4 　　 　 | 　　　 0 　　　 　 | 　　　 25

　◆自身で検討してみましたが、「前期に売上実績があるが、当期に売上実績がないケース」、及び「その逆のケース」についてうまく抽出できません。（上記例で言いますと、A店に対する売上で品コード1　と　品コード３　の商品売上数量比較ができませんでした。）

　解決方法わかる方、ご教授のほどよろしくお願い致します。

回答No.4

hatena1989

> ただ、この方法を用いた場合ですと、前期に売上実績があり、後期に売上実績がない場合、前期の分しか表示されない状態になりました。（109期の列自体が表示されませんでした）

後期の売上実績が0件、言い換えると、「後期売上データ.」のレコード件数が 0 の場合は、後期の列は表示されません。
そういうことでしょうか。

もし、そうなら、クロス集計クエリをデザインビューで開き、ビューの上部のテーブルの背景部分をクリックして、
クエリプロパティを表示させます。そこの、「クエリ列見出し」を
"前期","後期"
と設定すれば、「後期売上データ.」のレコード件数が 0 でも、後期の列は表示されます。

回答No.3

nda23

SQLを考える時は理論の組み立てが肝要で、闇雲に作ったり、
人の作ったものをマネしても永久にウマくなれません。
先ず、前期の数量を考えます。次に今期の数量を考えます。
(1)店別、品別、前期数量
(2)店別、品別、今期数量
これを店コード、品コードで結合すれば9割は完成です。
ここまでは質問者さんにもできていると思います。
問題は「前期だけにある」と「今期だけにある」ものの
取り扱いです。始めに前期を中心に今期を見ます。
(3)店別、品別、前期数量 ⇒ 店別、品別、今期数
つまり、(1)⇒(2)です。
こうすると、前期と、今期(前期にあったものだけ)の抽出が
できます。残りは「今期だけ」のものです。
(4)店別、品別、今期数量(但し、前期にないもののみ)
ここまでくれば、(3)+(4)が求めるものであることが理解
できると思います。

(1)店別、品別、前期数量
SELECT 店コード,品コード,SUM(売上数量) AS 前期数量
FROM 前期売上データ GROUP BY 店コード,品コード
(2)店別、品別、今期数量
SELECT 店コード,品コード,SUM(売上数量) AS 今期数量x
FROM 今期売上データ GROUP BY 店コード,品コード

(3)店別、品別、前期数量 ⇒ 店別、品別、今期数
SELECT A.店コード,A.品コード,A.前期数量,NZ(B.今期数量x,0) AS 今期数量
FROM (SELECT 店コード,品コード,SUM(売上数量) AS 前期数量
FROM 前期売上データ GROUP BY 店コード,品コード) AS A
LEFT JOIN
(SELECT 店コード,品コード,SUM(売上数量) AS 今期数量x
FROM 今期売上データ GROUP BY 店コード,品コード) AS B
ON A.店コード=B.店コード AND A.品コード=B.品コード

※前期にあって、今期にないと「今期数量」はNULLになる。
　表示上は空欄になるので、NZ関数で「NULLなら0」とした。

(4)店別、品別、今期数量(但し、前期にないもののみ)
SELECT A.店コード,A.品コード,0 AS 前期数量,SUM(売上数量) AS 今期数量
FROM 今期売上データ AS A LEFT JOIN 前期売上データ AS B
ON A.店コード=B.店コード AND A.品コード=B.品コード
WHERE B.店コード IS NULL GROUP BY A.店コード,A.品コード

※B(つまり前期)が結合しないとB側の項目はNULLになる。

(3)と(4)を結合して、店コードと品コードで並べ替える

SELECT A.店コード,A.品コード,A.前期数量,NZ(B.今期数量x,0) AS 今期数量
FROM (SELECT 店コード,品コード,SUM(売上数量) AS 前期数量
FROM 前期売上データ GROUP BY 店コード,品コード) AS A
LEFT JOIN
(SELECT 店コード,品コード,SUM(売上数量) AS 今期数量x
FROM 今期売上データ GROUP BY 店コード,品コード) AS B
ON A.店コード=B.店コード AND A.品コード=B.品コード

UNION ALL

SELECT A.店コード,A.品コード,0 AS 前期数量,SUM(売上数量) AS 今期数量
FROM 今期売上データ AS A LEFT JOIN 前期売上データ AS B
ON A.店コード=B.店コード AND A.品コード=B.品コード
WHERE B.店コード IS NULL GROUP BY A.店コード,A.品コード

ORDER BY 1,2

※結合はUNIONで、この場合はフィールド名ではなく列位置でORDER句を
　指定します。

左が主体で右を見る方法(前述の⇒)がまさしくLEFT JOINです。

お礼

ご回答ありがとうございます。

あなた様のおっしゃる通り、真似しただけではスキルは身に付きませんね。
それぞれの方から教えて戴いた方法を理解しながら、少しずつ身につけたいと思います。

あなた様の指導していただいた方法もなんとか理解できました。全くの初心者だったので非常に長い構文に抵抗ありつつも、一つ一つ解読していけました。

As , Nz , NULLあたりが何を示しているのか最初全然わからず、ググっていたためお礼遅くなりました。
ありがとうございました。

回答No.2

30246kiku

違う方法のものを

前期売上データ / 当期売上データ にある 店コード, 品コード を一塊りにし、
別名「Q0」を付けておきます

(SELECT 店コード, 品コード FROM 前期売上データ
UNION
SELECT 店コード, 品コード FROM 当期売上データ) AS Q0

これを元に、前期売上データ / 当期売上データ と LEFT JOIN で
店コード, 品コード　を結び付け

SELECT Q0.店コード, Q0.品コード,
Q1.売上数量 AS 前期売上数量,
Q2.売上数量 AS 当期売上数量
FROM
((SELECT 店コード, 品コード FROM 前期売上データ
UNION
SELECT 店コード, 品コード FROM 当期売上データ) AS Q0
LEFT JOIN 前期売上データ AS Q1
ON Q0.店コード=Q1.店コード AND Q0.品コード=Q1.品コード)
LEFT JOIN 当期売上データ AS Q2
ON Q0.店コード=Q2.店コード AND Q0.品コード=Q2.品コード;

もし、各テーブルの中で、店コード, 品コード　が重複しているのなら
上記を Q0.店コード, Q0.品コード でグループ化するようにします。

SELECT Q0.店コード, Q0.品コード,
Sum(Q1.売上数量) AS 前期売上数量,
Sum(Q2.売上数量) AS 当期売上数量
FROM
((SELECT 店コード, 品コード FROM 前期売上データ
UNION
SELECT 店コード, 品コード FROM 当期売上データ) AS Q0
LEFT JOIN 前期売上データ AS Q1
ON Q0.店コード=Q1.店コード AND Q0.品コード=Q1.品コード)
LEFT JOIN 当期売上データ AS Q2
ON Q0.店コード=Q2.店コード AND Q0.品コード=Q2.品コード
GROUP BY Q0.店コード, Q0.品コード;

お礼

回答ありがとうございます。
突然、長いSQLが出てきたので、解読に時間を要してしまいましたが、理解できました。

こちらの方法を用いた場合、前期に売上実績あり、当期に売上実績なしの商品等に対して、空白で表示されるので、回答者No,3の方がおっしゃるようにNz関数を用いて対処すれば希望通りに表示できました。

ありがとうございました。

回答No.1

hatena1989

ユニオンクエリで前期データと当期データを連結します。
クエリのSQLビューで下記のSQLを記述してください。

SELECT "前期" AS 期, 前期売上データ.*
FROM 前期売上データ
UNION ALL
SELECT "後期" AS 期, 後期売上データ.*
FROM 後期売上データ;

このクエリを保存します。
このクエリよりクロス集計クエリを作成して、下記のように設定します。

フィールド / 集計 / 行列の入れ替え
店コード / グループ化 / 行見出し
品コード / グループ化 / 行見出し
期 / グループ化 / 列見出し
売上数量 / 合計 / 値

以上です。

ちなみに、データベースでは、ユニオンクエリの結果のようなテーブルにするのが、正しい設計といえます。

お礼

ご回答ありがとうございます。

SQLを直接記述したことがなく、UNION　ALLについてネットで色々検索したところ、内容理解できました。
ただ、この方法を用いた場合ですと、前期に売上実績があり、後期に売上実績がない場合、前期の分しか表示されない状態になりました。（109期の列自体が表示されませんでした）
私のやり方が間違っていたのかもしれませんが、大変勉強になりました。
ありがとうございます。