Accessでの引き算

質問

アクセス初心者です。どなたか教えてください。
Windows XP Access 2003の環境です。

このようなデータがあります。

データ
商品ＩＤ　　商品名　　入庫　　出庫　　
００１　　　ＡＡＡ　　　　３　　　　１　　
ｘｘｘ
ｘｘｘ

これをクエリーで在庫数を出したいのですが、私のやり方ではこのようになってしまいます。

商品ＩＤ　　商品名　　入庫　　出庫　　在庫
００１　　　ＡＡＡ　　　　　３　　　　　　　３
００１　　　ＡＡＡ　　　　　　　　　　１　　１

どこを直せばちゃんと一行で在庫が２と出てくれますか？
すみません。教えてください。

宜しくお願い申し上げます。

回答No.5

noname#79209

本当は「入出庫情報」でなく、「入庫情報」と「出庫情報」とテーブルを分けたほうが無駄が少なくなます。
あと、多分「入出庫情報」には日付のフィールドもあり、これも加えてグループ化して、月別、商品別の集計に関する質問に移行して行くのでしょうね。
さらには「出力する期間や商品を『フォーム』で指定できないか」とかするのでしょう。

また、商品マスターをジョインするのは単に商品名を表示したいためだけのようですので、
集計用クエリは商品IDだけにしておき、別途出力用クエリで商品名等をくっつけてやるのも一つの方法です・

回答No.4

kurodai2

No3さんの回答で気が付きました。
Accessには、Nz関数がありますね。

SQLビュー
SELECT 入出庫情報.商品ID, First(商品マスター.商品名) AS 商品名, Sum(入出庫情報.入庫数) AS 入庫数計, Sum(入出庫情報.出庫数) AS 出庫数計, Sum(Nz([入庫数],0)-Nz([出庫数],0)) AS 在庫数
FROM 入出庫情報 LEFT JOIN 商品マスター ON 入出庫情報.商品ID = 商品マスター.商品ID
GROUP BY 入出庫情報.商品ID;

回答No.3

CHRONOS_0

商品別の入出庫を記録し、在庫を求めたいということのようですね
だったらアップされたテーブルじゃ無理じゃないの

まず、入出庫を記録するには、1日1回記録するとしても
[入出庫情報]（入出庫ＩＤ、年月日、商品ＩＤ、入庫数、出庫数）
で、このテーブルから商品別の在庫を求めるには

select 商品ID,商品名,Sum(Nz(入庫数,0)-Nz(出庫数,0))
from 入出庫情報 inner join 商品マスター on 入出庫情報.商品ID=商品マスタ.商品ID
group by 商品ID

回答No.2

kurodai2

そうでしたか、どうもおかしな内容だとは思いました。
更に、追加の情報から考えると　入出庫情報は発生単位でしょうから
例えば、同じ001の商品IDをもつデータが複数あると思います。

SQLビューの内容です。
SELECT 商品ID, 商品名, 入庫数合計, 出庫数合計, [入庫数合計]-[出庫数合計] AS 差引在庫数
FROM (
SELECT 入出庫情報.商品ID, First(商品マスター.商品名) AS 商品名, Sum(入出庫情報.入庫数) AS 入庫数合計, Sum(入出庫情報.出庫数) AS 出庫数合計
FROM 入出庫情報 LEFT JOIN 商品マスター ON 入出庫情報.商品ID = 商品マスター.商品ID
GROUP BY 入出庫情報.商品ID);

一旦、入出庫情報を商品IDで入庫・出庫の合計を取り、
その結果で、引き算をしています。

結果です。

商品ID　商品名　入庫数合計　出庫数合計　差引在庫数
001　　　AAA　　　3　　　　　1　　　　　2

希望の内容でなければ、さらに補足してください。

回答No.1

kurodai2

クエリをSQLビューで表示したものを記載します。

SELECT テーブルX.商品ID, テーブルX.商品名, テーブルX.入庫, テーブルX.出庫, [テーブルX]![入庫]-[テーブルX]![出庫] AS 在庫
FROM テーブルX;
＊テーブル名は、実際のものに読み替えてください。

結果は、
商品ID　商品名　　入庫　　　出庫　　在庫
001　　　 AAA　　　 3　　　　 1　　　　 2
です。

お礼

早速のお答えをお知らせくださりありがとうございました。

実は、テーブルが２つに分かれております：

商品マスター
商品ＩＤ　商品名　

入出庫情報
入出庫ＩＤ　商品ＩＤ　入庫数　　出庫数

SELECT 商品マスター.商品ID, 商品マスター.商品名, 入出庫情報.入庫数, 入出庫情報.出庫数, 入出庫情報!入庫数-入出庫情報!出庫数 AS 在庫
FROM 入出庫情報;

このようにしてＳＱＬにペイストしてクエリーを実行すると商品ＩＤと商品名のパラメータを聞いてきます。

多分私がはじめに１つのテーブルにあるような質問の仕方をしたのが
悪かったのだと思います。すみません。もう一度式を教えてください。

宜しくお願い申し上げます。