はじめまして。 以下の方法に悩んでおり、ご教示頂きたいです。
製品Xの構成マスタのテーブル が以下のようにあり 構成品がそれぞれ在庫されています。
製品Xの構成
├ A ：在庫30
└ B ：在庫20 ※BはAの次バージョン品のため、在庫がなくなった後に使用する。
製品Xを以下のように計画したとします。
6/1 6/2 6/3 6/4
X 10 10 10 10
このときA,Bの所要計画を立てると並列のためどちらからも
引き算してしまいますが、 Aが在庫切れしたところからBを引くようにしたいです。
◆現状
6/1 6/2 6/3 6/4
A使用 10 10 10 10
A在庫 20 10 10 -10
B使用 10 10 10 10
B在庫 10 0 -10 -20
以下のようにしたい
6/1 6/2 6/3 6/4
A使用 10 10 10 10
A在庫 20 10 0 0
B使用 0 0 0 10
B在庫 20 20 20 10
AとBは違う物として扱っていますが 使用順としてはA→Bとなるため
苦慮しています。
良い方法がありますでしょうか？
現状のテーブル構成はどうなってますか。
各テーブルのテーブル名、フィールド名、主キー設定を提示してください。
お世話になっております。やろうとしていることがAccess向きではないかもしれませんが・・。すみません。
添付画像（簡素化）のような テーブル構成・ 実施しようとしていることになります。
テーブル設計がまずいですね。
画像「計画のテーブル」のD1,D2,D3～というフィールドは日付をあらわしているのですよね。このような日々追加されているようなデータをフィールドにしてはいけません。データベース設計のセオリーから外れます。
「日付」というフィールドに日付を追加してく形にすべきです。
AとBは同じ部品でバージョン違いであるという情報をテーブルに持たせる必要があります。
情報が不足しているで全体像が分かりませんが、とりあえず、下記のような感じのテーブル設計になると思います。
製品マスター
※製品CD
製品名
部品マスター
※部品CD
部品名
部品バージョンマスター
※部品CD
※バージョンCD
バージョン名
製品構成マスター
※製品CD
※部品CD
必要数
生産計画
※日付
※製品CD
生産数
部品在庫履歴
※日付
※部品CD
※部品バージョン
使用数
在庫数
部品在庫履歴 をクエリだけで計算するのは難しいので、
VBAでDAOを使ってレコードセットとして開いて、
レコード移動しながら累計値を入力していくことになると思います。
ありがとうございます。
情報が不足しており申し訳ありません。
D1～ 日付に見立てたフィールドで~31まで用意し、別のフィールドで月、年を用意をしています。
ユーザとしては 入力フォームで
製品ごとに ある年月の 1日～31日 フィールドへ入力する形にしています。
入力したデータは 別のテーブルで日付に変換して 日付順に所要展開できるように考えています。
クロス集計クエリを逆にしている形です。
ご指摘があったように A,Bが同じ部品であることをバージョン情報として持たせるようにしたいと思います。
やはり、クエリでの計算は難しく
バージョン管理品 の場合は 変更順に Aが0となった場合は、Bから引くといった
VBAでの処理が必要と理解しました。
D1～ のテーブルは入力用の一時テーブルで、入力後、縦に展開する正規化されたデータに変換してテーブルに格納するということですね。
レコード間を順に集計していく処理（連番とか累計）はクエリは苦手です。複雑かつ重い処理になりがちです。特に今回の安形だとバージョンがあったりとより複雑になります。
VBAでレコード移動しながら処理していくのが高速かつシンプルにできます。
下記に、連番をクエリまたはVBAでする場合のサンプルがありますので、参考になると思います。
グループ毎連番を自動入力する関数 - hatena chips
お世話になります。 はい、
D1～ のテーブルは入力用の一時テーブルで、入力後、縦に展開する正規化されたデータに変換してテーブルに格納するということですね。
上記の通りになります。
hatena様の 累計値をテーブルに自動入力する関数 を使用させていただき
画像のような 所要量計算テーブルで所要量を計算し、累計残高としています。
製品のマスタとしては、 カレー・肉じゃがに使う じゃがいもは 代替品でOKとした マスタテーブルを持ち、
変更順をデータとして持たせるとします。
このとき、消費計画のテーブルでは
メークイン が0になったら 男爵イモ の在庫を使う ・・ のようにしたいと考えております。
累計処理で計算すると、 まだ 男爵イモを使用しなくても良い 時でも 引いてしまうため
この処理について良い案がないか というご相談になりました。
イメージばかりですみません・・。
これは、下のような感じですか？
日付,子部品あいうA,子部品あいうB,テープ,ネジ,…
6/1,
6/2.
6/3,
お世話になります。
以下のような感じになります。
ユーザが入力するのは 以下のフォーム
D1 D2
X：2021：06: １０ １０
上記データを
↓ 構成マスタと合わせ展開して 消費テーブルに追加
6/1 あいうA 10
6/1 あいうB 10
Excelライクに入力する範囲 を常に一定 にするため上記方法を考えております。
クロス集計テーブルとクロス集計入力フォームは、個人的には、NGです。
クロス集計ありきで、ExcelライクなAccessを目指すと、横にフィールドを伸ばしたくなったり、縦横のイメージが抜けなくなったりしませんか？
縦横のAccessは、たぶん、複雑なSQLを都度都度要求されるので、まず完成しません。神アニメや神ゲームをExcelで再現するくらい大変でしょう。
これも、複雑なSQLが必要になるアンチパターンでしょう。
ある共通点を持つものを、横に並べない、これがAccessの基本だと思います。
こんなのはどうでしょう？
・製造計画フォーム
製造日:6/1、製品名:製品X、製造数:10
材料明細
ロット明細(古いバージョンから使う事)
次のように、ユーザーが選んで打ち込みます。
在庫数がなくなるかマイナスになると、表示されなくなったり、打ち込めなくなったりするのは、どうでしょう？
あとは、在庫数をどうやって処理するかの問題は、残りますが。
クロス集計クエリは、Access完成後、しばらく運用してから、見やすさのために、リリーフ登板する感じになるのではないでしょうか。