ACCESSで累計数量管理 仕入時と販売時の部品番号が異なる商品を累計管理する方法２

私はACCESS初心者です。
会社用に製品の仕入・販売数量累計数量を閲覧出来る仕組みを作成しております。
以前こちらのサイトでアドバイスを頂き、希望していた下記内容にて運用出来る様になりました。
-------------------------------------------------------------
「仕入番号」と「販売番号」が異なる場合、仕入番号で検索した時、
「仕入数量合計」－「販売数量合計」＝「在庫」をクエリ表示する。
-------------------------------------------------------------
ただ、下記事情の修正を行う為、再度アドバイスを頂きたく投稿させて頂きました。

＜現在の状況＞
　　　　仕入番号　販売番号　　　仕入数量　－　販売数量　＝　在庫
（１）　　Ａ　　　　　　　　Ａ　　　　　　　　１０　　　　　　　５　　　　　　　　５
（２）　　Ａ　　　　　　　　Ｂ　　　　　　　　　　　　　　　　　５　　　　　　　－５
（３）　　Ｂ　　　　　　　　Ｂ　　　　　　　　　５　　　　　　　５　　　　　　　　０

つまり、仕入番号Ａ、Ｂともに在庫は「０」個です。
　　　　　　　　　　　　　　　　　　　　　　
＜現在のテーブル・クエリ構成＞
・部品情報テーブル
　・ＩＤ（主キー）
　・販売番号
　・仕入番号

・仕入情報テーブル
　・ＩＤ（主キー）
　・仕入番号
　・仕入数量

・販売情報テーブル
　・ＩＤ（主キー）
　・販売番号
　・販売数量

・累計情報クエリ
　・仕入数量の合計
　・販売数量の合計
　・在庫：([仕入数量の合計]－[販売数量の合計]）

以前頂いたアドバイスにより、「仕入番号」で各テーブルを合計する事で（１）と（２）だけの場合は、仕入番号「Ａ」の在庫は０で表示されます。
ただ、（３）が加わった際に累計情報クエリの結果が下記になってしまいます。

　　　　仕入番号　販売番号　　　仕入数量　－　販売数量　＝　在庫
（１）　　Ａ　　　　　　　　Ａ　　　　　　　　１０　　　　　　　５　　　　　　　　５
（２）　　Ｂ　　　　　　　　Ｂ　　　　　　　　　５　　　　　　１０　　　　　　　－５

前述しました通り、実際のところは
仕入番号Ａの仕入数量合計１０個－販売数量合計１０個＝在庫０個
仕入番号Ｂの仕入数量合計　５個－販売数量合計　５個＝在庫０個
ですので、これをクエリ結果として表示したいのです。

この仕組みを現在運用中の為、出来るだけ現設定を残しつつ解決をしたいと考えております。
何卒宜しくお願い申し上げます。

No.7 ベストアンサー 回答者： noname#140971 回答日時： 2007/08/31 20:40

・部品情報テーブル

　・ＩＤ（主キー）
　・販売番号
　・仕入番号

・仕入情報テーブル
　・ＩＤ（主キー）
　・部品_ID <---->部品情報テーブル!ID
　・仕入番号
　・仕入数量

・販売情報テーブル
　・ＩＤ（主キー）
　・部品_ID <---->部品情報テーブル!ID
　・販売番号
　・販売数量

このように、テーブルを変更するのは簡単。
この改善を断行しなければ、一歩も先には進めないと思います。進んでもまた後戻りは必至。

・部品情報テーブル
ID___販売番号___仕入番号
1____AAA__________AAA
2____BBB_________BBB

・仕入情報テーブル
ID___部品_ID___仕入番号___仕入数量
1_________null__________AAA___________100
2_________null__________BBB__________200

[イミディエイト]
? CNNExecute("UPDATE 仕入情報テーブル, 部品情報テーブル SET 仕入情報テーブル.部品_ID = 部品情報テーブル.ID WHERE 仕入情報テーブル.仕入番号=部品情報テーブル.仕入番号")
True

・仕入情報テーブル
ID___部品_ID___仕入番号___仕入数量
1____________1__________AAA___________100
2____________2__________BBB__________200

ここでは、SQL文実行関数を使っていますが、このSQL文をクエリにコピペすれば＜更新クエリ＞が出来上がります。

※仕入情報テーブル.仕入番号は、残しておいても構いません。
※ただし、部品情報テーブル.仕入番号を標準とし仕入情報テーブルでは標準を加筆修正するという仕組みが前提。

僅かに２回SQL文を実行するだけの改善です。
お勧めしておきます。

この回答へのお礼

Husky2007様
丁寧なアドバイスを頂き、誠に有難うございました。
本方法にて、テーブル構成改善にトライ致します。

現在は、必要に応じてテーブル・クエリを付けたしながら、
最後の帳尻を必死に合わせている状況です。

御指摘頂きました様に、折を見て全体の見直しを行いたいと思います。

誠に有難うございました。

お礼日時：2007/09/02 03:30

No.6 回答者： CHRONOS_0 回答日時： 2007/08/31 19:34

>（１）部品Ａと部品Ｂはまったく同じ部品です

単品の在庫なんですか?そんなのでいいいの?

何度も同じようなことを書かれていますが
その説明をよくみてください、説明の中に時間経過があるでしょ

>（２）顧客１に対して、「仕入番号Ａで１０個」仕入れ、「販売番号Ａで５個」
>販売しました。（当方在庫５個）
その後
>（３）顧客２に対して、「仕入番号Ｂで５個」仕入、「販売番号Ｂで５個」完売
>しました。

>（４）顧客１が在庫過多の為、「仕入番号Ａの残り５個」を、顧客２に対して「
>販売番号Ｂで５個」販売しました。
のはずですよね
なのに顧客2が顧客1の後だという情報がテーブルにないのです

また顧客2が5個しか注文していなかったら
仕入番号Bの5個のほうを渡し、それより古い仕入番号Aのほうを残すのですか?

顧客Bの注文が6だったら普通は古い仕入Aの5個と、Bの１個を販売し
仕入Bの4個を残しますね
で
翌日顧客Cから10個の注文が入ったら、不足している6個以上を発注し
古いBの残り4個と新しく仕入れたCの中の6個を販売します

今のテーブル構造じゃこういうことが出来ません
つまり今のやり方では出来ないという結論になります

この回答へのお礼

CHRONOS_0様
丁寧なアドバイス頂き、誠に有難うございました。

ご指摘を頂きました通り、現在のテーブル構成では今後の管理が難しいと感じております。
現在はテーブルを無理やり組み合わせて、帳尻だけを合わせている現状です。

今後のために、折を見てテーブルの見直ししたいと考えております。

誠に有難うございました。

お礼日時：2007/09/02 03:34

No.5 回答者： kurodai2 回答日時： 2007/08/31 16:23

現在のテーブル構成では無理です。

なぜならば、販売情報には　販売番号しか存在しません。

実際に部品情報に、相互に重なり合ったものは登録できるでしょう。
IDが主キーなので

ID　　販売番号　　仕入番号
1　　　A　　　　　A
2　　　A　　　　　B
3　　　B　　　　　A
4　　　B　　　　　B

でも、販売情報の販売番号Bで、この部品情報テーブルを見ると
ID 3,4のデータ　2件が対象となり、どちらかは識別できません。
判断する情報が無いので、不可能ですね。

販売情報に、例えば 仕入番号Aの販売番号Bの物を販売したときに、
部品情報のID　3　も合わせて格納するように変更すれば可能です。
（部品情報　ID=3の情報さえあれば 販売番号B自体も持つことも無いのですが・・）

残念ながら、テーブルの見直しが必要ですね。

この回答へのお礼

kurodai2様
いつも丁寧なアドバイスを頂き、誠に有難うございます。

現在、必要な状況に迫られてからテーブル・クエリを追加している状況であります。
御指摘を頂きました通り、テーブル（及び全体）の見直しを図りたいと考えております。

本件に関しましては、部品情報ＩＤを「販売情報入力時」に参照出来る様、別の方からアドバイスを頂いた方法でトライ致します。

誠に有難うございました。

お礼日時：2007/09/02 03:40

No.4 回答者： kurodai2 回答日時： 2007/08/31 13:02

質問を良く見ていたら・・・疑問が

この例は、現実でしょうか？
（２）　Ａ　　Ｂ　５　　　　　　　－５

（３）　Ｂ　　Ｂ　５　　　　　　　　５

もしそうだとしたら
販売番号　Bに対する仕入れ番号は　A,Bの２種類が存在することになります。
販売情報の販売番号からでは、仕入番号を特定することは出来ないですね。
販売情報に登録する際に　同じ販売番号Bでも
仕入番号がAか、Bか識別できるような項目も設けないと
このままでは無理です。

この回答への補足

アドバイスを頂き、誠に有難うございます。

私の投稿内容が不明瞭な為、皆様にお手数をお掛けし申し訳ございません。
私の意図はKirodai2様御指摘の通りでございます。

＞販売番号　Bに対する仕入れ番号は　A,Bの２種類が存在することになります。
＞販売情報の販売番号からでは、仕入番号を特定することは出来ないですね。
＞販売情報に登録する際に　同じ販売番号Bでも
＞仕入番号がAか、Bか識別できるような項目も設けないと…

現状を説明させて下さい。

（１）部品Ａと部品Ｂはまったく同じ部品です。

（２）顧客１に対して、「仕入番号Ａで１０個」仕入れ、「販売番号Ａで５個」販売しました。（当方在庫５個）

（３）顧客２に対して、「仕入番号Ｂで５個」仕入、「販売番号Ｂで５個」完売しました。

（４）顧客１が在庫過多の為、「仕入番号Ａの残り５個」を、顧客２に対して「販売番号Ｂで５個」販売しました。

その後、累計情報クエリで集計したところ、投稿欄の結果となりました。
この時、販売情報テーブルの販売番号では無く、部品情報テーブルの仕入番号から算出する仕組みとなっております。

　　　　仕入番号　販売番号　　　仕入数量　－　販売数量　＝　在庫
（１）　　Ａ　　　　　　　　Ａ　　　　　　　　１０　　　　　　　５　　　　　　　　５
（２）　　Ｂ　　　　　　　　Ｂ　　　　　　　　　５　　　　　　１０　　　　　　　－５

御指摘の通り、販売番号Ｂに仕入番号ＡとＢを識別させ、集計の際に、在庫表示を「０」としたいのです。

度々のお願いで大変恐縮ではございますが、
何卒お力添えを頂けます様、お願い申し上げます。

補足日時：2007/08/31 15:38

No.3 回答者： kurodai2 回答日時： 2007/08/31 11:38

前回のクエリ3と同じことになるのですが、販売情報テーブルの販売番号で集計して販売数量を求めてはいけないです。

販売情報テーブルに、販売番号で部品情報テーブルを結合し、部品情報テーブルの仕入れ番号でグループをとって 販売数量を集計するようにしてください。
結果：仕入れ番号ごとの販売数量が取得できます。

No.2 回答者： 16August 回答日時： 2007/08/31 08:22

累計情報クエリで抽出条件を[仕入番号]=[販売番号]とすればとりあえず解決すると思います。

　
No1の方もおっしゃっていますが、在庫管理をするためには、入庫と出庫をひとつのテーブルで管理したほうが良いような気もします。　棚卸とか、在庫管理とかも行うのであれば、そういう方向で考えたほうが処理が楽ですよ。

No.1 回答者： CHRONOS_0 回答日時： 2007/08/31 02:38

テーブルの作り方がおかしいからうまく行かないのでは

普通はどの部品を(何時)いくら仕入れた、(何時)いくら販売した
というようなテーブルを作りますね

各テーブルはどのようにリレーションしているのでしょうか?
まさか主キーのIDじゃないでしょうね
それだと3つのテーブルはすべて1対1のテーブル
すなわち、ひとつのテーブルにすることが可能なものになってしまいますよ

それともIDは関係なく仕入と販売を多対多の関係にあるとみなして
部品情報というのが中間テーブル?

やりたいことはA仕入れで10個入り、B仕入で5個入荷
A販売で5個売れ、B販売で10個売れた
これを仕入のロットごとにFIFOで消しこみしていきたいということのようですが
そうなると日付を現すフィールドが絶対に必要になってきます

>この仕組みを現在運用中の為、出来るだけ現設定を残しつつ解決をしたい
これはちょっと無理みたいですね