Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2016)
製造ロット毎の出荷数量を計算したい
投稿日時: 17/10/12 15:56:21
投稿者: ぽろんちょ

いつも大変お世話になります。
 
総出荷数量に対して、どの製造ロットの製品を払い出すかを計算したいのですが、
うまくできずに困っています。
 
使用するテーブルは「出荷テーブル」「入荷テーブル」「棚卸しテーブル」の三種類で、それぞれに
・品目コード
・製造ロット
・数量
と三つのフィールドがあります。
 
製造ロットにはハイフン(特にロットの指定がないもの)も存在し、出荷に関してはこのハイフン
で出荷指示される物が多く、その場合は製造ロットの若い順から出庫されるように表示したいです。
 
最終的には以下のように表示したいと考えています。
括弧表示の数量は総出荷数量となります。
 
品目コード    製造ロット    出荷数量    棚卸数量    入庫数量
AAAAAA          -     (300)         0        0
          201708      200        100       100
          201709      100        100       0
 
クエリ、VBAどちらでも対応できますので、どなたかおわかりになる方宜しくお願い致します。

回答
投稿日時: 17/10/12 16:27:11
投稿者: sk

引用:
使用するテーブルは「出荷テーブル」「入荷テーブル」「棚卸しテーブル」の三種類で、それぞれに
・品目コード
・製造ロット
・数量
と三つのフィールドがあります。

・3 つのテーブルの構造は全て同一であるのか。
 
・各テーブルには具体的にどのようなレコードが
 格納されているのか。
 
引用:
製造ロットにはハイフン(特にロットの指定がないもの)も存在し、
出荷に関してはこのハイフンで出荷指示される物が多く、
その場合は製造ロットの若い順から出庫されるように表示したいです。

・上記の 2 つの表現は矛盾するのではないか。
 ( "-" だけでは若い順も何もない。少なくとも、主語
 (それぞれの「製造ロット」がどのテーブルの[製造ロット]を指しているのか)
 が曖昧である)

投稿日時: 17/10/12 17:57:02
投稿者: ぽろんちょ

sk様
  
早速の返信ありがとうございます。
  
・3 つのテーブルの構造は全て同一であるのか。
 構造は全て同じになります。
   
・各テーブルには具体的にどのようなレコードが
 格納されているのか。
 品目コード ・・・AAAAAA、BBBBBなどのアルファベット4〜6文字
 製造ロット・・・ -(ハイフン)と201708、201709などの年月の文字列
 数量 ・・・数値。多くて4桁。
  
・上記の 2 つの表現は矛盾するのではないか。
 ( "-" だけでは若い順も何もない。少なくとも、主語
  (それぞれの「製造ロット」がどのテーブルの[製造ロット]を指しているのか)
  が曖昧である)
 出荷の製造ロット「-」は『特に出荷のロット指定はしないので、在庫もしくは入庫してくる製品の
若いロットから出庫してください』という意味になります。
 出荷テーブルの製造ロットで、棚卸しテーブルと入荷テーブルの製造ロットを指しています。
  
宜しくお願いします。

回答
投稿日時: 17/10/12 18:55:40
投稿者: sk

引用:
構造は全て同じになります。

各テーブルの主キーはどのフィールドでしょうか。
 
引用:
品目コード ・・・AAAAAA、BBBBBなどのアルファベット4〜6文字
製造ロット・・・ -(ハイフン)と201708、201709などの年月の文字列
数量 ・・・数値。多くて4桁。

各フィールドのデータ型や入力形式だけでなく、
各テーブルに格納されているそれそれのレコードの内容
(データシートビューで開いた際のイメージ)を、
最終的なアウトプットとの関係が分かる形で
例示されることをお奨めします。
 
引用:
総出荷数量に対して、どの製造ロットの製品を払い出すかを計算したい

引用:
製造ロットにはハイフン(特にロットの指定がないもの)も存在し、
出荷に関してはこのハイフンで出荷指示される物が多く

引用:
出荷の製造ロット「-」は『特に出荷のロット指定はしないので、
在庫もしくは入庫してくる製品の若いロットから出庫してください』
という意味になります。

例えば[出荷テーブル]の主キーが[品目コード](だけ)なのか、
[品目コード]と[製造ロット]の組み合わせなのか
([出荷テーブル]に格納されているレコード間で、
 [品目コード]の値の重複が発生し得るか否か)では
上記の記述の意味合いが変わってきます。

投稿日時: 17/10/13 10:10:14
投稿者: ぽろんちょ

sk様
 

引用:
各テーブルの主キーはどのフィールドでしょうか。

 
主キーは作成していません。
 
引用:
各フィールドのデータ型や入力形式だけでなく、
 各テーブルに格納されているそれそれのレコードの内容
 (データシートビューで開いた際のイメージ)を、
 最終的なアウトプットとの関係が分かる形で
例示されることをお奨めします。

 
全てのテーブルで以下のような内容になっています。
 
品目コード    製造ロット    数量
AAAAA        -        104
BBBBB        -        784
CCCCC        -        108
DDDDD        -        80
EEEEE        201707      1
EEEEE        201708      40
FFFFF        201708      80
 
 
引用:
例えば[出荷テーブル]の主キーが[品目コード](だけ)なのか、
[品目コード]と[製造ロット]の組み合わせなのか
([出荷テーブル]に格納されているレコード間で、
  [品目コード]の値の重複が発生し得るか否か)では
上記の記述の意味合いが変わってきます。

 
一つの品目コードに対して複数の製造ロットが存在する場合がありますので、
基本的には全てのテーブルで品目コードの重複は発生しております。
 
ややこしいですが宜しくお願いいたします。

回答
投稿日時: 17/10/16 11:39:27
投稿者: sk

引用:
全てのテーブルで以下のような内容になっています。

[出荷テーブル],[入荷テーブル],[棚卸しテーブル]の
それぞれに格納されているレコードを例示してみて下さい。
 
引用:
主キーは作成していません。

引用:
一つの品目コードに対して複数の製造ロットが存在する場合がありますので、
基本的には全てのテーブルで品目コードの重複は発生しております。

([出荷テーブル]に格納されているレコードについて)
 
・1 つの[品目コード]グループにおいて、
 [製造ロット]の値が同じであるレコードが
 2 件以上格納されているケースはあり得るのか。
 (特に[製造ロット]の値が "-" である場合)
 
・1 つの[品目コード]グループにおいて、
 [製造ロット]の値が "-" であるレコードと
 [製造ロット]の値が "-" ではないレコードが
 混在するケースはあり得るのか。
 
([入荷テーブル]に格納されているレコードについて)
 
・[入荷テーブル]にも、[製造ロット]の値が "-" である
 レコードが存在するのか。
 
・1 つの[品目コード]グループにおいて、
 [製造ロット]の値が同じであるレコードが
 2 件以上格納されているケースはあり得るのか。
 
・1 つの[品目コード]グループにおいて、
 [製造ロット]の値が "-" であるレコードと
 [製造ロット]の値が "-" ではないレコードが
 混在するケースはあり得るのか。
 
([棚卸しテーブル]に格納されているレコードについて)
 
・[棚卸しテーブル]にも、[製造ロット]の値が "-" である
 レコードが存在するのか。
 
・1 つの[品目コード]グループにおいて、
 [製造ロット]の値が同じであるレコードが
 2 件以上格納されているケースはあり得るのか。
 
・1 つの[品目コード]グループにおいて、
 [製造ロット]の値が "-" であるレコードと
 [製造ロット]の値が "-" ではないレコードが
 混在するケースはあり得るのか。
 
引用:
総出荷数量に対して、どの製造ロットの製品を払い出すかを計算したいのですが、
うまくできずに困っています。

・ここでの「総出荷数量」とは、[出荷テーブル]のレコードを
 [品目コード]でグループ化し、グループごとに[数量]の合計を
 求めた結果、という意味か。
 それとも[出荷テーブル]の各レコードの[数量]の値そのものが
 「総出荷数量」に相当するのか。

投稿日時: 17/10/16 16:06:02
投稿者: ぽろんちょ

sk様
 
ご返信有難うございます。
 

引用:
[出荷テーブル],[入荷テーブル],[棚卸しテーブル]の
それぞれに格納されているレコードを例示してみて下さい。

 
[出荷テーブル]
品目コード 製造ロット 数量
AAAAA     -     104
 BBBBB     -     784
 CCCCC     -     108
 DDDDD     -     80
 EEEEE     201707   1
 EEEEE     201708   40
 FFFFF     201708   80
 
[棚卸しテーブル]
品目コード    製造ロット 数量
AAAAA         -      263
AAAAA         201709    400
BBBBB         201709    1900
CCCCC         201708    802
DDDDD         201708    490
EEEEE         -        7
EEEEE         201707    15
EEEEE         201708    296
FFFFF         201705    41
FFFFF         201707    5
FFFFF         201708    288
 
[入荷テーブル]
品目コード    製造ロット    数量CS
AAAAA         201709    400
BBBBB         201709    160
CCCCC         201709    448
DDDDD         201708    400
DDDDD         201709    880
EEEEE         201709    280
FFFFF          -      72
 
 
引用:
([出荷テーブル]に格納されているレコードについて)
  
・1 つの[品目コード]グループにおいて、
  [製造ロット]の値が同じであるレコードが
 2 件以上格納されているケースはあり得るのか。
  (特に[製造ロット]の値が "-" である場合)
  
・1 つの[品目コード]グループにおいて、
  [製造ロット]の値が "-" であるレコードと
 [製造ロット]の値が "-" ではないレコードが
 混在するケースはあり得るのか。

 
下記の品目コードEEEEEのように一つの品目コードに対して、
複数の製造ロットを持つ事があります。
ただし、一つの品目コードグループで同じ製造ロットのレコードは存在しません。
品目コードEEEEEであれば"-"、"201707"、"201708"は1レコードずつしかありません。
 
[出荷テーブル]
品目コード 製造ロット 数量
AAAAA     -     104
 BBBBB     -     784
 CCCCC     -     108
 DDDDD     -     80
EEEEE      -    50
 EEEEE     201707   1
 EEEEE     201708   40
 FFFFF     201708   80
 
 
引用:
([入荷テーブル]に格納されているレコードについて)
  
・[入荷テーブル]にも、[製造ロット]の値が "-" である
 レコードが存在するのか。
  
・1 つの[品目コード]グループにおいて、
  [製造ロット]の値が同じであるレコードが
 2 件以上格納されているケースはあり得るのか。
  
・1 つの[品目コード]グループにおいて、
  [製造ロット]の値が "-" であるレコードと
 [製造ロット]の値が "-" ではないレコードが
 混在するケースはあり得るのか。

 
下記のように[入荷テーブル]にも"-"の製造ロットは存在します。
品目コードDDDDDのように一つの品目コードに対して、複数の製造ロットが存在します。
(複数の製造ロットの中に"-"も存在します。)
ただし、一つの品目コードグループで同じ製造ロットのレコードは存在しません。
 
[入荷テーブル]
品目コード    製造ロット    数量CS
AAAAA         201709    400
BBBBB         201709    160
CCCCC         201709    448
DDDDD         201708    400
DDDDD         201709    880
EEEEE         201709    280
FFFFF          -      72
 
 
引用:
([棚卸しテーブル]に格納されているレコードについて)
  
・[棚卸しテーブル]にも、[製造ロット]の値が "-" である
 レコードが存在するのか。
  
・1 つの[品目コード]グループにおいて、
  [製造ロット]の値が同じであるレコードが
 2 件以上格納されているケースはあり得るのか。
  
・1 つの[品目コード]グループにおいて、
  [製造ロット]の値が "-" であるレコードと
 [製造ロット]の値が "-" ではないレコードが
 混在するケースはあり得るのか。

 
下記の[棚卸しテーブル]のように製造ロット"-"が存在します。
品目コードEEEEEのように一つの品目コードで複数の製造ロットが存在します。
ただし、一つの品目コードグループで同じ製造ロットのレコードは存在しません。
 
[棚卸しテーブル]
品目コード    製造ロット 数量
AAAAA         -      263
AAAAA         201709    400
BBBBB         201709    1900
CCCCC         201708    802
DDDDD         201708    490
EEEEE         -        7
EEEEE         201707    15
EEEEE         201708    296
FFFFF         201705    41
FFFFF         201707    5
FFFFF         201708    288
 
引用:
・ここでの「総出荷数量」とは、[出荷テーブル]のレコードを
 [品目コード]でグループ化し、グループごとに[数量]の合計を
 求めた結果、という意味か。
  それとも[出荷テーブル]の各レコードの[数量]の値そのものが
 「総出荷数量」に相当するのか。

 
下記の[出荷テーブル]の数量の値そのものが、総出荷数量になります。
 
[出荷テーブル]
品目コード 製造ロット 数量
AAAAA     -     104
 BBBBB     -     784
 CCCCC     -     108
 DDDDD     -     80
EEEEE      -    50
 EEEEE     201707   1
 EEEEE     201708   40
 FFFFF     201708   80
 
長くなってしまいましたが、宜しくお願いいたします。

回答
投稿日時: 17/10/19 14:19:47
投稿者: sk

引用:
下記の品目コードEEEEEのように一つの品目コードに対して、
複数の製造ロットを持つ事があります。
ただし、一つの品目コードグループで同じ製造ロットのレコードは存在しません。
品目コードEEEEEであれば"-"、"201707"、"201708"は1レコードずつしかありません。

引用:
下記のように[入荷テーブル]にも"-"の製造ロットは存在します
品目コードDDDDDのように一つの品目コードに対して、複数の製造ロットが存在します。
(複数の製造ロットの中に"-"も存在します。)
ただし、一つの品目コードグループで同じ製造ロットのレコードは存在しません。

引用:
下記の[棚卸しテーブル]のように製造ロット"-"が存在します
品目コードEEEEEのように一つの品目コードで複数の製造ロットが存在します。
ただし、一つの品目コードグループで同じ製造ロットのレコードは存在しません。

現状のテーブル設計のままでは、要求されているような出力処理を
行なうのは無理なのではないでしょうか。
 
少なくとも、[入荷テーブル]及び[棚卸しテーブル]において
[製造ロット]の値が "-" であるレコードの扱われ方と、
それらのレコードと[出荷テーブル]のレコードとの関係性が
アバウトすぎます。
(ロット不明の在庫って何やねん、ということ)
 
また、この場合の[出荷テーブル]というのは、
出荷履歴というより受注履歴としてのデータが
記録されているようにも見えますが、もしそうなら
「ある品目の受注に対し、その受注が発生した時点での
 在庫から、どの製造ロットの商品を、何個出荷したか」
を示すレコードを格納するためのテーブルが
別途必要になるはずです。
(そのテーブル自体が「最終的なアウトプット」と成り得る)
 
引用:
[出荷テーブル]
品目コード    製造ロット    数量
AAAAA         -              104
BBBBB         -              784
CCCCC         -              108
DDDDD         -               80
EEEEE         -               50
EEEEE         201707           1
EEEEE         201708          40
FFFFF         201708          80

引用:
[入荷テーブル]
品目コード    製造ロット    数量CS
AAAAA         201709         400
BBBBB         201709         160
CCCCC         201709         448
DDDDD         201708         400
DDDDD         201709         880
EEEEE         201709         280
FFFFF         -               72

引用:
[棚卸しテーブル]
品目コード    製造ロット    数量
AAAAA         -              263
AAAAA         201709         400
BBBBB         201709        1900
CCCCC         201708         802
DDDDD         201708         490
EEEEE         -                7
EEEEE         201707          15
EEEEE         201708         296
FFFFF         201705          41
FFFFF         201707           5
FFFFF         201708         288

仮に、各テーブルに上記の通りにレコードが
格納されているとして、最終的にどのような形で
アウトプットされればよいのでしょうか。

投稿日時: 17/10/23 11:58:25
投稿者: ぽろんちょ

sk様
 

引用:
仮に、各テーブルに上記の通りにレコードが
格納されているとして、最終的にどのような形で
 アウトプットされればよいのでしょうか。

 
少し見づらいかもしれませんが、以下のようなリストを作成しようとしていました。
例えば品目『AAAAA』の総出荷数量679ケースのうち、どの製造ロットを何ケース払出すのか、
在庫が不足していた場合の入庫はあるのかなどを、一目で見てわかるようにしたかったのです。
 
現場の作業員たちにこの流れがわかると効率が良くなると言われ作成しようと試みました。
 
 
品目コード    製造ロット    在庫数量    入庫数量    ロット別出荷数量    総出荷数量
AAAAA       201709      352       0        352          679
         201710      1360      1360       327    
BBBBB       201709      85       0        85           328
         201710      1360       0       243    
CCCCC      201709       0        168       80           80
DDDDD       201709      2393      640       2498          2498
EEEEE       201710      0       952       176          176
 
実は色々と試行錯誤した結果、なんとか希望のリストを作成することが出来ました。
自分としても製造ロット"-"の考え方や、ロット別出荷数量のように総出荷数量に対して、
どの製造ロットをいくつ払出すかの計算方法などで苦慮しましたが、
煩雑にはなりましたがクエリをいくつも作成することで、なんとかそれらしく作ることが出来ました。
 
sk様にはいろいろとご指導いただき大変助かりました。
またなにかの機会でお力をお借りする事があるかもしれませんが、その時はどうぞ宜しくお願い致します。
お忙しいところ本当に有難うございました。