- ベストアンサー
ACCESSで累計数量管理 仕入時と販売時の部品番号が異なる商品を累計管理する方法２
私はACCESS初心者です。 会社用に製品の仕入・販売数量累計数量を閲覧出来る仕組みを作成しております。 以前こちらのサイトでアドバイスを頂き、希望していた下記内容にて運用出来る様になりました。 ------------------------------------------------------------- 「仕入番号」と「販売番号」が異なる場合、仕入番号で検索した時、 「仕入数量合計」－「販売数量合計」＝「在庫」をクエリ表示する。 ------------------------------------------------------------- ただ、下記事情の修正を行う為、再度アドバイスを頂きたく投稿させて頂きました。 ＜現在の状況＞ 仕入番号 販売番号 仕入数量 － 販売数量 ＝ 在庫 （１） Ａ Ａ １０ ５ ５ （２） Ａ Ｂ ５ －５ （３） Ｂ Ｂ ５ ５ ０ つまり、仕入番号Ａ、Ｂともに在庫は「０」個です。 ＜現在のテーブル・クエリ構成＞ ・部品情報テーブル ・ＩＤ（主キー） ・販売番号 ・仕入番号 ・仕入情報テーブル ・ＩＤ（主キー） ・仕入番号 ・仕入数量 ・販売情報テーブル ・ＩＤ（主キー） ・販売番号 ・販売数量 ・累計情報クエリ ・仕入数量の合計 ・販売数量の合計 ・在庫：([仕入数量の合計]－[販売数量の合計]） 以前頂いたアドバイスにより、「仕入番号」で各テーブルを合計する事で（１）と（２）だけの場合は、仕入番号「Ａ」の在庫は０で表示されます。 ただ、（３）が加わった際に累計情報クエリの結果が下記になってしまいます。 仕入番号 販売番号 仕入数量 － 販売数量 ＝ 在庫 （１） Ａ Ａ １０ ５ ５ （２） Ｂ Ｂ ５ １０ －５ 前述しました通り、実際のところは 仕入番号Ａの仕入数量合計１０個－販売数量合計１０個＝在庫０個 仕入番号Ｂの仕入数量合計 ５個－販売数量合計 ５個＝在庫０個 ですので、これをクエリ結果として表示したいのです。 この仕組みを現在運用中の為、出来るだけ現設定を残しつつ解決をしたいと考えております。 何卒宜しくお願い申し上げます。
- sou51
- お礼率66% (12/18)
- その他(データベース)
- 回答数7
- ありがとう数3
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
- ベストアンサー
・部品情報テーブル ・ＩＤ（主キー） ・販売番号 ・仕入番号 ・仕入情報テーブル ・ＩＤ（主キー） ・部品_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文を実行するだけの改善です。 お勧めしておきます。
その他の回答 (6)
- CHRONOS_0
- ベストアンサー率54% (457/838)
>（１）部品Ａと部品Ｂはまったく同じ部品です 単品の在庫なんですか?そんなのでいいいの? 何度も同じようなことを書かれていますが その説明をよくみてください、説明の中に時間経過があるでしょ >（２）顧客１に対して、「仕入番号Ａで１０個」仕入れ、「販売番号Ａで５個」 >販売しました。（当方在庫５個） その後 >（３）顧客２に対して、「仕入番号Ｂで５個」仕入、「販売番号Ｂで５個」完売 >しました。 >（４）顧客１が在庫過多の為、「仕入番号Ａの残り５個」を、顧客２に対して「 >販売番号Ｂで５個」販売しました。 のはずですよね なのに顧客2が顧客1の後だという情報がテーブルにないのです また顧客2が5個しか注文していなかったら 仕入番号Bの5個のほうを渡し、それより古い仕入番号Aのほうを残すのですか? 顧客Bの注文が6だったら普通は古い仕入Aの5個と、Bの１個を販売し 仕入Bの4個を残しますね で 翌日顧客Cから10個の注文が入ったら、不足している6個以上を発注し 古いBの残り4個と新しく仕入れたCの中の6個を販売します 今のテーブル構造じゃこういうことが出来ません つまり今のやり方では出来ないという結論になります
お礼
CHRONOS_0様 丁寧なアドバイス頂き、誠に有難うございました。 ご指摘を頂きました通り、現在のテーブル構成では今後の管理が難しいと感じております。 現在はテーブルを無理やり組み合わせて、帳尻だけを合わせている現状です。 今後のために、折を見てテーブルの見直ししたいと考えております。 誠に有難うございました。
- kurodai2
- ベストアンサー率38% (77/202)
現在のテーブル構成では無理です。 なぜならば、販売情報には 販売番号しか存在しません。 実際に部品情報に、相互に重なり合ったものは登録できるでしょう。 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様 いつも丁寧なアドバイスを頂き、誠に有難うございます。 現在、必要な状況に迫られてからテーブル・クエリを追加している状況であります。 御指摘を頂きました通り、テーブル（及び全体）の見直しを図りたいと考えております。 本件に関しましては、部品情報ＩＤを「販売情報入力時」に参照出来る様、別の方からアドバイスを頂いた方法でトライ致します。 誠に有難うございました。
- kurodai2
- ベストアンサー率38% (77/202)
質問を良く見ていたら・・・疑問が この例は、現実でしょうか？ （２） Ａ Ｂ ５ －５ （３） Ｂ Ｂ ５ ５ もしそうだとしたら 販売番号 Bに対する仕入れ番号は A,Bの２種類が存在することになります。 販売情報の販売番号からでは、仕入番号を特定することは出来ないですね。 販売情報に登録する際に 同じ販売番号Bでも 仕入番号がAか、Bか識別できるような項目も設けないと このままでは無理です。
補足
アドバイスを頂き、誠に有難うございます。 私の投稿内容が不明瞭な為、皆様にお手数をお掛けし申し訳ございません。 私の意図はKirodai2様御指摘の通りでございます。 ＞販売番号 Bに対する仕入れ番号は A,Bの２種類が存在することになります。 ＞販売情報の販売番号からでは、仕入番号を特定することは出来ないですね。 ＞販売情報に登録する際に 同じ販売番号Bでも ＞仕入番号がAか、Bか識別できるような項目も設けないと… 現状を説明させて下さい。 （１）部品Ａと部品Ｂはまったく同じ部品です。 （２）顧客１に対して、「仕入番号Ａで１０個」仕入れ、「販売番号Ａで５個」販売しました。（当方在庫５個） （３）顧客２に対して、「仕入番号Ｂで５個」仕入、「販売番号Ｂで５個」完売しました。 （４）顧客１が在庫過多の為、「仕入番号Ａの残り５個」を、顧客２に対して「販売番号Ｂで５個」販売しました。 その後、累計情報クエリで集計したところ、投稿欄の結果となりました。 この時、販売情報テーブルの販売番号では無く、部品情報テーブルの仕入番号から算出する仕組みとなっております。 仕入番号 販売番号 仕入数量 － 販売数量 ＝ 在庫 （１） Ａ Ａ １０ ５ ５ （２） Ｂ Ｂ ５ １０ －５ 御指摘の通り、販売番号Ｂに仕入番号ＡとＢを識別させ、集計の際に、在庫表示を「０」としたいのです。 度々のお願いで大変恐縮ではございますが、 何卒お力添えを頂けます様、お願い申し上げます。
- kurodai2
- ベストアンサー率38% (77/202)
前回のクエリ3と同じことになるのですが、販売情報テーブルの販売番号で集計して販売数量を求めてはいけないです。 販売情報テーブルに、販売番号で部品情報テーブルを結合し、部品情報テーブルの仕入れ番号でグループをとって 販売数量を集計するようにしてください。 結果：仕入れ番号ごとの販売数量が取得できます。
累計情報クエリで抽出条件を[仕入番号]=[販売番号]とすればとりあえず解決すると思います。 No1の方もおっしゃっていますが、在庫管理をするためには、入庫と出庫をひとつのテーブルで管理したほうが良いような気もします。 棚卸とか、在庫管理とかも行うのであれば、そういう方向で考えたほうが処理が楽ですよ。
- CHRONOS_0
- ベストアンサー率54% (457/838)
テーブルの作り方がおかしいからうまく行かないのでは 普通はどの部品を(何時)いくら仕入れた、(何時)いくら販売した というようなテーブルを作りますね 各テーブルはどのようにリレーションしているのでしょうか? まさか主キーのIDじゃないでしょうね それだと3つのテーブルはすべて1対1のテーブル すなわち、ひとつのテーブルにすることが可能なものになってしまいますよ それともIDは関係なく仕入と販売を多対多の関係にあるとみなして 部品情報というのが中間テーブル? やりたいことはA仕入れで10個入り、B仕入で5個入荷 A販売で5個売れ、B販売で10個売れた これを仕入のロットごとにFIFOで消しこみしていきたいということのようですが そうなると日付を現すフィールドが絶対に必要になってきます >この仕組みを現在運用中の為、出来るだけ現設定を残しつつ解決をしたい これはちょっと無理みたいですね
お礼
Husky2007様 丁寧なアドバイスを頂き、誠に有難うございました。 本方法にて、テーブル構成改善にトライ致します。 現在は、必要に応じてテーブル・クエリを付けたしながら、 最後の帳尻を必死に合わせている状況です。 御指摘頂きました様に、折を見て全体の見直しを行いたいと思います。 誠に有難うございました。