Access (VBA)

Access VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Access 2016)
店舗間の在庫移動リストを作成したい
投稿日時: 21/03/18 17:09:46
投稿者: 万葉の湯

売れていない店舗の商品を売れている店舗への移動させるための
移動リストというものを作成したいと思っています。
 
「送りたい店舗/商品リスト」の商品と同じ商品を欲しがっている店舗の中から
配送マスタに登録されている配送可能な県(近隣店舗)の店舗を探し出し、
移動リストを完成させてたいのですが
クエリで実現する方法が全く思いつきません。(クエリがダメならVBAを検討する予定です)
 
どなたかいいアイデアをお持ちでしたらご教授お願い致します。
 
<活用できるテーブル>
 ・配送マスタ
 ・送りたい店舗/商品リスト
 ・もらいたい店舗/商品リスト
<作成したいテーブル>
 ・移動リスト
 

【配送マスタ】
発送地	着地
東京都	東京都
東京都	千葉県
東京都	埼玉県
東京都	神奈川県
青森県	青森県
青森県	北海道
青森県	岩手県
青森県	秋田県
:	:
沖縄県	沖縄県

【送りたい店舗/商品リスト】
店舗	所在地	商品
A店	東京都	商品1
B店	沖縄県	商品1
C店	青森県	商品2

 
【もらいたい店舗/商品リスト】
優先順位	店舗	所在地	商品
1	D店	愛媛県	商品1
2	E店	沖縄県	商品1
3	F店	千葉県	商品1
4	G店	神奈川県	商品1
1	E店	沖縄県	商品2
2	H店	神奈川県	商品2
3	I店	神奈川県	商品2
4	D店	愛媛県	商品2
5	J店	岩手県	商品2
6	F店	千葉県	商品2
7	G店	神奈川県	商品2
8	K店	鹿児島県	商品2

 
【移動リスト】
移動元店舗 移動元地区 移動先店舗 移動先地区 商品
A店     東京都   F店     千葉県   商品1
B店     沖縄県   E店     沖縄県   商品1
C店     青森県   J店     岩手県   商品2

回答
投稿日時: 21/03/18 18:26:38
投稿者: sk

引用:
「送りたい店舗/商品リスト」の商品と同じ商品を欲しがっている店舗の中から
配送マスタに登録されている配送可能な県(近隣店舗)の店舗を探し出し、
移動リストを完成させてたいのですが
クエリで実現する方法が全く思いつきません。

(オブジェクト/フィールドの名前に "/" を用いるのはあまり推奨しませんが)
 
1. 以下のクエリ[Q商品配送可能パターン]を作成する。
 
( SQL ビュー)
----------------------------------------------------------------------
SELECT [送りたい店舗/商品リスト].[店舗] AS [移動元店舗],
       [送りたい店舗/商品リスト].[所在地] AS [移動元地区],
       [もらいたい店舗/商品リスト].[優先順位],
       [もらいたい店舗/商品リスト].[店舗] AS [移動先店舗],
       [もらいたい店舗/商品リスト].[所在地] AS [移動先地区],
       [送りたい店舗/商品リスト].[商品]
FROM ([送りたい店舗/商品リスト]
      INNER JOIN [配送マスタ]
      ON [送りたい店舗/商品リスト].[所在地] = [配送マスタ].[発送地])
INNER JOIN [もらいたい店舗/商品リスト]
 ON [送りたい店舗/商品リスト].[商品] = [もらいたい店舗/商品リスト].[商品]
AND [配送マスタ].[着地] = [もらいたい店舗/商品リスト].[所在地]
ORDER BY [送りたい店舗/商品リスト].[店舗],
         [もらいたい店舗/商品リスト].[優先順位];
----------------------------------------------------------------------
 
2. 以下のクエリ[Q商品移動リスト]を作成する。
 
( SQL ビュー)
----------------------------------------------------------------------
SELECT [Q商品配送可能パターン].*
FROM [Q商品配送可能パターン]
INNER JOIN (SELECT tmp.[移動元店舗],
                   tmp.[移動元地区],
                   tmp.[商品],
                   Min(tmp.[優先順位]) AS [優先順位]
            FROM [Q商品配送可能パターン] AS tmp
            GROUP BY tmp.[移動元店舗],
                     tmp.[移動元地区],
                     tmp.[商品]) AS [Q最優先店舗]
 ON [Q商品配送可能パターン].[移動元店舗] = [Q最優先店舗].[移動元店舗]
AND [Q商品配送可能パターン].[移動元地区] = [Q最優先店舗].[移動元地区]
AND [Q商品配送可能パターン].[商品] = [Q最優先店舗].[商品]
AND [Q商品配送可能パターン].[優先順位] = [Q最優先店舗].[優先順位];
----------------------------------------------------------------------
 
こういうことが出来ればよい、ということでしょうか。

投稿日時: 21/03/18 18:55:59
投稿者: 万葉の湯

sk様
 
ご回答本当にありがとうございます。
またご指摘ありがとうございます。質問がわかりやすいようにと実際のオブジェクト名と
違うもので投稿させていただきましたが、結果紛らわしくなり申し訳ございません。
 
 
早速、確認させていただきました。
(1つ目のクエリまでは考えたのですが、レコード数が倍々ゲームのようになり途方に暮れてました)
 
結果はものすごく実現したいものに近くなりました!!感動です!
ただ1点だけ…
説明不足だったのですがもらいたい店舗の優先順位が高い店舗にたくさんの在庫が集まるような結果になりました。
移動可能県内で あげたい店舗と商品が10レコードあるとしたら、
もらいたい店舗の1位にすべてが移動されるという結果になりました。
1つ目のあげたいが もらいたい1位にいったら、2つ目のあげたい は もらいたい2位にしたいんです。
1対1で引き当てていきたいと考えています。 これは難しそうですよね。。。
 
この度教えていただきました内容はほかでも役立ちそうなので
きちんと理解し勉強させていただきたいと思います。
ありがとうございました。
 
 

回答
投稿日時: 21/03/19 11:34:49
投稿者: sk

引用:
説明不足だったのですがもらいたい店舗の優先順位が高い店舗に
たくさんの在庫が集まるような結果になりました。
移動可能県内で あげたい店舗と商品が10レコードあるとしたら、
もらいたい店舗の1位にすべてが移動されるという結果になりました。

単純に[優先順位]の値が最も小さい(優先度が高い)ものを
ピックアップしているので当然そうなります。
 
引用:
1つ目のあげたいが もらいたい1位にいったら、
2つ目のあげたい は もらいたい2位にしたいんです。
1対1で引き当てていきたいと考えています。

その場合、「もらいたい」(移動先)の優先順位とは別に、
「あげたい」(移動元)の優先順位がどのようにして決めるのか、
という問題が生じます。
 
そもそも[送りたい店舗/商品リスト]の構造からして
「 1 つの店舗につき、各商品の在庫は 1 個までしか保持されていない」
という状態になっているのが、現実に即していないと言えます。
(同一商品の在庫数は 0 個以上であるはず)
 
・他店舗に対して 2 個以上の同一商品の配送希望を出すことがある。
 (配送希望を受けることがある)
 
・2 個以上の同一商品が必要となった場合、各店舗の在庫状況によっては
 複数の他店舗からその商品をかき集めなければならない場合もある。
 
・複数の他店舗から同一商品の配送希望を受けた場合、
 「どの店舗の方が先に注文してきたか」
 「それぞれの店舗はその商品を何個求めているのか」
 「注文を受けた時点においてその商品の在庫は何個残っているか」
 「発送地と着地との間の距離や運賃はいくら掛かるか
 (どこに送る方が早く届くか/安く済むか)」などの
 条件に応じて、配送の可否や、どの店舗にどの商品を何個
 配送するべきかを判断しなければならない。
 
 
以上のような問題をも含めた話なのであれば、
現在挙げられているテーブルだけでは必要な情報が
足りていませんし、またルールやパターンが複雑であれば
クエリのみで実現することは難しくなります。
 
ともかく、まずはその辺りの業務分析や仕様策定を
進められることをお奨めします。

トピックに返信