Accessにてテーブルが異なるデータの引き算

質問

Access に異なるテーブルに収められているデータの「引き算」ほ方法をお教えください。

只今作成しているシステムは、簡単な在庫管理システムです。
今後、拡張していくこともあり得るという前提で各テーブルを作成しました。

作成したテーブルは以下のとおりです。

仕入先テーブル「仕入先ID・仕入先名・フリガナ・〒・住所1・住所2・TEL・FAX」
材料テーブル「材料ID・材料名」（単価はその時々で変わってくるのであえてここでは設定していません）
仕入テーブル「仕入ID・日付・仕入先ID・材料ID・仕入数量・単価」
払出テーブル「払出ID・日付・材料ID・払出数量」

在庫確認クエリにて以下の設定を行いました。
材料テーブルより「材料ID・材料名」
仕入テーブルより「仕入数量」
払出テーブルより「払出数量」
を設定し演算にて「在庫: Sum(Nz([仕入数量],0)-Nz([払出数量],0))」を入力しました。

データシートビューにて確認したところ、お互い発生した回数分だけ足されてしまい正確な在庫数量が表示されません。

例
====================
9/1　仕入数量　100
9/2　払出数量　50
9/3　仕入数量　100
9/4　払出数量　50
9/5　仕入数量　100
====================

とした場合に、仕入数量600　払出数量300 となってしまいます。

以下は「SQLレビュー」の内容です。

SELECT 材料.材料ID, 材料.材料名, Sum(仕入.仕入数量) AS 仕入数量の合計, Sum(払出.払出数量) AS 払出数量の合計, Sum(Nz([仕入数量],0)-Nz([払出数量],0)) AS 在庫
FROM (材料 INNER JOIN 仕入 ON 材料.材料ID = 仕入.材料ID) INNER JOIN 払出 ON 材料.材料ID = 払出.材料ID
GROUP BY 材料.材料ID, 材料.材料名;

解決方法をご享受くださいますようよろしくお願いいたします。

回答No.3

CHRONOS_0 · ベストアンサー

1段目で集計はできているのですから2段目のクエリでは単に引き算をするだけです

在庫クエリ
SELECT
材料.材料ID,
材料.材料名,
仕入合計.仕入数量の合計,
払出合計.払出数量の合計,
[仕入数量の合計]-[払出数量の合計] AS 在庫
FROM 仕入合計 INNER JOIN 払出合計 ON 仕入合計.材料ID = 払出合計.材料ID

お礼

ありがとうございます。

ただ、単純に引き算をするだけだったのですね・・・　お手数をおかけいたしました。

SELECT 材料.材料ID, 材料.材料名, 仕入合計.仕入数量の合計, 払出合計.払出数量の合計, [仕入数量の合計]-[払出数量の合計] AS 在庫
FROM (材料 INNER JOIN 仕入合計 ON 材料.材料ID = 仕入合計.材料ID) INNER JOIN 払出合計 ON 材料.材料ID = 払出合計.材料ID;

ばっちり、表示されました。

あとは、月締め、年締めの作業です。

お忙しい中本当にありがとうございました。

回答No.2

CHRONOS_0

仕入と払い出しを材料IDだけで結合したのではそうなりますね
仕入の集計、払い出しの集計を別個に行ってから
その集計クエリ同士を材料IDで結合したクエリを作ります

お礼

お忙しい中ありがとうございます。

仕入、払出をそれぞれ合計値を求め、合計値から在庫数を求めるクエリを作成してみましたが・・・　うまくいきませんでした。
まったくもって、理解度がなく大変申し訳ございません。

仕入合計
SELECT 仕入.材料ID, 材料.材料名, Sum(仕入.仕入数量) AS 仕入数量の合計
FROM 材料 INNER JOIN 仕入 ON 材料.材料ID = 仕入.材料ID
GROUP BY 仕入.材料ID, 材料.材料名;

払出合計
SELECT 払出.材料ID, 材料.材料名, Sum(払出.払出数量) AS 払出数量の合計
FROM 材料 INNER JOIN 払出 ON 材料.材料ID=払出.材料ID
GROUP BY 払出.材料ID, 材料.材料名;

在庫クエリ
SELECT 材料.材料ID, 材料.材料名, 仕入合計.仕入数量の合計, 払出合計.払出数量の合計, Sum([仕入数量の合計]-[払出数量の合計]) AS 在庫
FROM 払出合計, 仕入合計 INNER JOIN 材料 ON 仕入合計.材料ID = 材料.材料ID
GROUP BY 材料.材料ID, 材料.材料名, 仕入合計.仕入数量の合計, 払出合計.払出数量の合計;

結果
仕入300　払出50　在庫750
仕入300　払出100　在庫200

と、2段の結果になります。
ただし、以前のクエリとは違い、2段目は正しい結果となりました。

回答No.1

Dxak

SELECT 材料ID, 材料名, (SELECT Sum(仕入.仕入数量) FROM 仕入 WHERE 仕入.材料ID=材料.材料ID) AS 仕入数量計, (SELECT Sum(払出.払出数量) FROM 払出 WHERE 払出.材料ID=材料.材料ID) AS 払出数量計, Nz([仕入数量計],0)-Nz([払出数量計],0) AS 在庫
FROM 材料;

みたいに、サブクエリで逃げるか？DSum関数で回避するか？かな？・・・
原因は、集計のチェックを外せば判ると思うけど、「仕入れ」３パターン×「払出し」２パターンの計６パターンが、すべて出てくる、それを足し算すると・・・結果、重複した加算がされると言う話
ですので、集計した結果を「材料」テーブルの材料ＩＤに連結すれば問題は無いはずだけど・・・サブクエリをFrom句に作るように変わるだけなので、結果的には、変わらないかな？

お礼

ありがとうございます。

仕入数量・払出数量それぞれに材料IDを与えるという仕組みですね！