入出庫データから在庫数を連続的に算出するクエリです。
ここではエクセルのような連続した日付の在庫数をクエリで計算することが出来ます。
在庫管理の考え方自体は簡単です。前日在庫数に、入庫数を足して、出庫数を引いて、残りの数をまた明日に持ち越せば良いだけです。しかし、これをAccessで作る場合以外と困難な事が分ります。
管理する品目が数千とか数万点以上あり、さらに毎日、数年にわたって継続管理していくとなると非常に大きなデータ量となります。
少量ならばエクセルで十分対応できますが、やはりいずれはAccessを使った在庫管理システムが求められてきます。
という事で、ACCESSを使った在庫管理の核（あくまで基礎）となるクエリ(SQL)をご紹介します。
※ここで使用するACCESS画像のバージョンは2013です
■作成するテーブルとクエリについて
TM_商品 ・・・ 商品マスタテーブルです
TJ_入出庫実績 ・・・ 日々商品の入出庫数を蓄積する実績テーブルです
Q_在庫管理 ・・・日々の在庫数を算出するクエリです
■【TM_商品】マスタテーブルについて
ここでは例として、Aという商品と、Bという商品の2種類を扱います。
【フィールドの補足】
商品ID ＝ 主キー（オートナンバー、長整数型）
商品 ＝ 長いテキスト型
■【TJ_入出庫実績】テーブルについて
いつ、何が、いくつ入庫または出庫されたか、データを蓄積します。
【フィールドの補足】
入出庫ID ＝ 主キー（オートナンバー、長整数型）
日付 ＝ 日付／時刻型（書式＝日付(S)）
商品ID ＝ 商品マスタの商品IDです。数値型（長整数）にする事。商品名の入力は略しています。
入庫 ＝ 入庫数（数値型、長整数型）
出庫 ＝ 出庫数（数値型、長整数型）
■【Q_在庫管理】クエリの結果、表示内容
各商品に対して、日付、入庫計、出庫計、在庫を集計、算出、表示しています。
さて、ここでメインとなるのは、「在庫」項目です。
「前日の在庫数＋入庫計－出庫計＝在庫」
というデータが連続して表示されるような結果が出来ています。
■【Q_在庫管理】クエリのSQL
ACCESSのクエリを新規作成して、SQLビューへ下記を入力してあります。
2つのテーブルがきちんと作成されていれば下記クエリで結果反映されます。
エラーが起きる場合は、フィールド名やデータ型が間違っていないか確認ください。
【在庫計算用SQL】
SELECT Q1.日付, TM_商品.商品, Q1.入庫計, Q1.出庫計, Q1.在庫
FROM (SELECT 日付, 商品ID
, Sum(Nz(入庫)) AS 入庫計
, Sum(Nz(出庫)) AS 出庫計
, Sum( Select Sum(Nz(入庫)) -Sum(Nz(出庫))
From TJ_入出庫実績 As T2
Where T2.商品ID = T1.商品ID And T2.日付 <= T1.日付
Group By 商品ID ) AS 在庫
FROM TJ_入出庫実績 AS T1
GROUP BY 日付, 商品ID) AS Q1
LEFT JOIN TM_商品 ON Q1.商品ID = TM_商品.商品ID
;
■内容を詳しく見てみましょう
【TJ_入出庫実績】
入出庫ID＝１～７は、1行で各商品の日付、入庫数、出庫数の合計数を入力しています。
そして、クエリの結果もそのまま「在庫」フィールドが加えられただけの形となっています。
＜備考.1＞
入出庫ID＝6 入出庫数ともにゼロで入力しました。
そしてこれはクエリの結果も入出庫ゼロで表示され、在庫も前日から変化していません。
あえてこのような表示をしましたが、商品が多くなれば無駄な行が増えるだけなのでおすすめしません。
もし、日々ゼロ数値もほしい場合はこのようなデータが持てるという事で記載しました。
＜備考.2＞
日付＝2018/06/26の商品Aの入出庫数をゼロとして、そもそも実績を入力していません。
ゼロ値を入れるより、動きが無ければ入力しない方が無難です。
クエリにも反映されません。
【実績】
【結果】
■さらにこちらが現実的な実績データと思います
【TJ_入出庫実績】
入出庫ID＝８～１０
日付＝2018/06/27 に対して商品Aのデータが3行入力されています。
これは各データが追加クエリで入力されることを想定しています。
＜例＞
入出庫ID＝８ ・・・商品Aが朝、10個入庫
入出庫ID＝９ ・・・商品Aが昼、5個出庫
入出庫ID＝１０ ・・・商品Aが夕、5個出庫
【実績】
【結果】
在庫管理クエリでは1行にまとめて結果を表示しています。
■最後に
ACCESSで在庫管理を作成するに当たり、様々な方法があり、これはその中の1手法となります。
また、あくまで基礎項目だけですので、実際に作成する場合は、マスタの項目にロット番号を入れたり、実績の項目に作業者IDや登録した時間を追加したりする等が考えられます。
手持ちの情報をもとに、もし、使えそうでしたらチャレンジしてみてください。
いずれ管理フォームを作成して、日付（期間）や商品で抽出するなど、管理が出来るようなデータソースとして使用できるかと思います。
エクセルでは手に負えなくなった時、ACCESS（データベース）化の参考にどうぞ。
お役に立てたら幸いです。
