Access (VBA)

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

 
(Windows 7 Professional : Access 2016)
レコードの上下間で計算をしたい。
投稿日時: 17/05/19 15:29:45
投稿者: ぽろんちょ

いつも大変お世話になっております。
 
現在、商品在庫の有無を確認し、在庫があれば『結果』フィールドが空欄で、
不足なら欠品と表記させたいと考えておりますが、
なかなかうまくいきません。下記のように作成したいのですが、
『残数量』フィールドの部分の計算ができません。
 
配車NO | 品目コード | 出荷数 | 在庫 | 残数量 | 結果 |
----------------------------------------------------------------
I0104 | AAAAAA   |   3   |  29 |  26 |    |
----------------------------------------------------------------
I560 | AAAAAA   | 20  |  29 |  6 |    |
----------------------------------------------------------------
I577 | AAAAAA   |  7   |  29 |  -1 | 欠品 |
----------------------------------------------------------------
K153 | BBBBBB  | 10  |  15 |  5  |    |
----------------------------------------------------------------
K178 | BBBBBB  | 10  |  15 |  -5 | 欠品 |
 
品目コードごとで配車NOの昇順でソートした状態で、『在庫』フィールドから『出荷数』フィールド
の数を引いた結果を『残数量』に表示して、その『残数量』から1レコード下の『出荷数』を引いて
また残数量を算出し、最終的に残数量がマイナスになったところを『結果』フィールドに欠品と表示させたいのですが、私の力不足で『残数量』フィールドの算出方法がどうしてもわかりません。
 
どなたかおわかりになる方、教えて頂けると大変助かります。
宜しくお願いいたします。
 

回答
投稿日時: 17/05/19 16:24:24
投稿者: sk

使用されているのは 1 つのテーブルだけなのでしょうか。
それとも複数のテーブルなのでしょうか。
 

引用:
配車NO | 品目コード | 出荷数 | 在庫 | 残数量 | 結果 |
----------------------------------------------------------------
I0104 | AAAAAA   |   3   |  29 |  26 |    |
----------------------------------------------------------------
I560 | AAAAAA   | 20  |  29 |  6 |    |
----------------------------------------------------------------
I577 | AAAAAA   |  7   |  29 |  -1 | 欠品 |
----------------------------------------------------------------
K153 | BBBBBB  | 10  |  15 |  5  |    |
----------------------------------------------------------------
K178 | BBBBBB  | 10  |  15 |  -5 | 欠品 |

上記が「最終的なアウトプットのイメージ」であるのは分かりますが、
[配車NO],[品目コード],[出荷数],[在庫]といった各フィールドを
どのテーブルから参照しているのかが不明瞭です。

投稿日時: 17/05/19 16:42:56
投稿者: ぽろんちょ

sk様
 
早速のご返信ありがとうございます。
テーブルは2つで「出荷データ」と「棚卸し」があり、品目コードで結合しており、
以下のテーブルから参照しています。
 
「出荷データ」テーブル
・配車NO
・品目コード
・出荷数
 
「棚卸し」テーブル
・在庫
 
残数量と結果のフィールドはクエリで集計した時か、
テーブル作成クエリでフィールドを作るか、いずれにせよどこかのタイミングで作ろうかと考えていました。
宜しくお願いいたします。
 

投稿日時: 17/05/19 17:04:08
投稿者: ぽろんちょ

ちなみに自分で途中まで試行錯誤して作ってみました。
出荷数を上から足算していき、別途『合計』フィールドを作ってそこに表示して、
在庫数から引いていけば同じような結果になるのではないかと....。
ただ、これだと品目コードが違ってもそのまま計算してしまうので、
アプローチの仕方が違うような気もしています....。
 
Public Sub MoveCurrent()
 
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim Goukei As Integer
    Dim i As Integer
 
    Set cn = CurrentProject.Connection
 
    Set rs = New ADODB.Recordset
    rs.Open "T在庫チェック", cn, adOpenKeyset, adLockOptimistic
 
    Do Until rs.EOF
        Goukei = Goukei + rs!出荷数
        DoCmd.RunSQL "UPDATE T在庫チェック SET 残数量 =" & Goukei
        rs.MoveNext
    Loop
 
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
 
End Sub

回答
投稿日時: 17/05/19 17:26:57
投稿者: sk

引用:
テーブルは2つで「出荷データ」と「棚卸し」があり、品目コードで結合しており、
以下のテーブルから参照しています。
  
「出荷データ」テーブル
・配車NO
・品目コード
・出荷数
  
「棚卸し」テーブル
・在庫

・テーブル[棚卸し]の主キーは[品目コード]である。
 
・テーブル[出荷データ]の主キーは[配車NO]である。
 (または[品目コード]と[配車NO]の値の組み合わせが一意となる)
  
・出荷実績のない([出荷データ]にレコードがない)品目については、
 [棚卸し]にレコードがあればそのまま表示するものとする。
 
とりあえず以上のような仕様であると仮定します。
 
引用:
品目コードごとで配車NOの昇順でソートした状態で、
『在庫』フィールドから『出荷数』フィールドの数を引いた結果を
『残数量』に表示して、その『残数量』から1レコード下の『出荷数』を引いて
また残数量を算出し、最終的に残数量がマイナスになったところを
『結果』フィールドに欠品と表示

( SQL ビュー)
---------------------------------------------------------------------
SELECT [棚卸し].[品目コード],
       [棚卸し].[在庫],
       [出荷データ].[配車NO],
       [出荷データ].[出荷数],
       (SELECT Sum(tmp.[出荷数])
        FROM [出荷データ] tmp
        WHERE tmp.[品目コード] = [棚卸し].[品目コード]
          AND tmp.[配車NO] <= [出荷データ].[配車NO]) AS [累計出荷数],
       Nz([棚卸し].[在庫],0) - Nz([累計出荷数],0) AS [残数量],
       IIf([残数量]<0,"欠品",Null) AS [結果]
FROM [棚卸し]
LEFT JOIN [出荷データ]
ON [棚卸し].[品目コード] = [出荷データ].[品目コード]
ORDER BY [棚卸し].[品目コード],
         [出荷データ].[配車NO];
---------------------------------------------------------------------
 
以上のような選択クエリを作成なさればよろしいかと。

回答
投稿日時: 17/05/19 18:25:52
投稿者: sk

引用:
ちなみに自分で途中まで試行錯誤して作ってみました。

テーブル[T在庫チェック]の構造及びレコードが
最初の投稿におけるアウトプットイメージの通りであり、
既に[配車NO],[品目コード],[出荷数],[在庫]の値が
セットされている状態であると仮定するならば、
以下のようなコードを実行なさればよろしいでしょう。
 
(標準モジュール)
--------------------------------------------------------------------
Public Sub MoveCurrent()
  
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
     
    Dim strSQL As String
    Dim strCurrentGroup As String
    Dim lngSum As Long
  
    Set cn = CurrentProject.Connection
  
    Set rs = New ADODB.Recordset
     
    strSQL = "SELECT * FROM [T在庫チェック]" & _
             " ORDER BY [品目コード],[配車NO]"
     
    rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
         
    strCurrentGroup = ""
    lngSum = 0
     
    Do Until rs.EOF
        If strCurrentGroup <> rs![品目コード] Then
            lngSum = 0
            strCurrentGroup = rs![品目コード]
        End If
        lngSum = lngSum + Nz(rs![出荷数], 0)
        rs![残数量] = Nz(rs![在庫], 0) - lngSum
        If rs![残数量] < 0 Then
            rs![結果] = "欠品"
        Else
            rs![結果] = Null
        End If
        rs.Update
        rs.MoveNext
    Loop
  
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
  
End Sub
--------------------------------------------------------------------

回答
投稿日時: 17/05/20 10:37:40
投稿者: hatena
投稿者のウェブサイトに移動

ぽろんちょ さんの引用:
ちなみに自分で途中まで試行錯誤して作ってみました。
出荷数を上から足算していき、別途『合計』フィールドを作ってそこに表示して、
在庫数から引いていけば同じような結果になるのではないかと....。

 
このような要望も多いと思いますので、汎用関数を作成しました。下記で紹介さしていますので、ご参考に。
 
累計値をテーブルに自動入力する関数 - hatena chips
http://hatenachips.blog34.fc2.com/blog-entry-443.html
 
上記で紹介している SetCumulativeTotal 関数をコピーして標準モジュールに貼り付けます。
 
「出荷データ」テーブルには、「残数量」ではなく、「出荷数累計」というフィールドを追加します。
 
 
If SetCumulativeTotal("出荷数","出荷数累計","出荷データ","品目コード","配車NO") Then
     MsgBox "完了"
End If

これで、「出荷数累計」が書き込まれます。
 
あとは、クエリで「出荷データ」と「棚卸し」を品目コードで結合して、下記の演算フィールドを追加すればいいでしょう。
 
残数量: [在庫]-[出荷数累計]

結果: IIf([残数量]<0,"欠品","") 

回答
投稿日時: 17/05/20 14:54:21
投稿者: i-brown

ぽろんちょ さんの引用:
sk様
「出荷データ」テーブル
・配車NO
・品目コード
・出荷数
 
「棚卸し」テーブル
・在庫

 
残数量 = 棚卸しの在庫 − 棚卸し以降の出荷数 (+ 棚卸し以降の入荷数)
 
だと思いますので、
棚卸しの期日と、それ以降の入荷・出荷を区別する方法があれば、
1. クエリで品番コードごと最後の棚卸し以降の出荷数の合計を求める。(入荷があれば入荷の合計も。)
2. 棚卸しテーブルと1.のテーブルを品番コードで結合する。この時、在庫から出庫数の和を引いた(入荷があれば入荷数の和を加えた)フィールドもSELECT分の中に含める。
 
という手順で処理できると思うのですが、「棚卸し」「出荷データ」とも、日付がわかるような列はありませんか?
SQLで一度に書くこともできますが、慣れていないのでしたら単純なクエリをいくつか組み合わせる方が、途中計算結果の検証ができて動作確認し易いです。 

投稿日時: 17/05/23 09:49:18
投稿者: ぽろんちょ

sk様
 
お忙しいところ早速の回答ありがとうございました。
最初に教えて頂いたSQLで希望通りの数値を出す事が出来ました!
また、私が途中まで書いたコードでも作って頂きありがとうございました。
大変勉強になりました!
 
hatena様
 
リンク先拝見させて頂きました。
これに限らず多くの参考にできそうな内容がありましたので、
今後活用させて頂きたいと思います。ありがとうございました。
 
i-brown様
 
お忙しいところ回答ありがとうございます。
棚卸し日や出荷日はもちろんデータ上にありますので、今後検証していきたいと思います。
ありがとうございました。