Microsoft Access 掲示板

とりあえず確認

「入庫」「出庫」が同一月に発生することはありますか？その場合どのような表示になりますか？
「入庫」「出庫」が発生せず、「保管」だけが発生する月はありますか？

適当に「2024年3月に400個出庫」のデータを加えたサンプル

hirotonは「簡単な手法」が思い浮かばなかったので覚悟してください
複雑な要件なので問題を分解します

１．「保管」の累計個数をどう計算するか
２．「No」の連番をどう取得するか
３．「存在しない月のデータ」をどう補完するか

回答としては一緒くたにやっていますが、どの問題を解決するためのものなのか考えながら見てください

レポートのレコードソースのクエリ

SELECT
    T_入出庫一覧.区分,
    [移動日] - Day([移動日]) + 1 AS 移動月,
    Sum(IIf(
            [区分ID] = 1,
            [合計]
        )) AS 入庫数,
    Sum(IIf(
            [区分ID] = 2,
            [合計]
        )) AS 出庫数,
    T_区分マスター.手数料単価,
    Sum([手数料単価] * [合計]) AS 手数料
    T_区分マスター.区分ID,
FROM
    T_入出庫一覧
    INNER JOIN
        T_区分マスター
    ON  T_入出庫一覧.区分 = T_区分マスター.区分
GROUP BY
    T_入出庫一覧.区分,
    [移動日] - Day([移動日]) + 1,
    T_区分マスター.手数料単価,
    T_区分マスター.区分ID
ORDER BY
    [移動日] - Day([移動日]) + 1,
    T_区分マスター.区分ID
;

上記クエリによるレポート出力用の基礎データ例

レポートのモジュール

Option Compare Database
Option Explicit

Dim No As Long
Dim 保管月 As Date
Dim 保管個数 As Long

Private Sub Report_Load()
    保管月 = DateAdd("M", -1, Me!移動月)
End Sub

Private Sub 移動月フッター_Format(Cancel As Integer, FormatCount As Integer)
    No出力 Me!No_保管
    
    '//補完用データ確保
    保管月 = Me!移動月
    保管個数 = Me!保管個数表示
End Sub

Private Sub 移動月ヘッダー_Format(Cancel As Integer, FormatCount As Integer)
    If Me!移動月 = DateAdd("M", 1, 保管月) Then
        Cancel = True
        Exit Sub
    End If
    
    '//前のレコードが2月以上前なのでデータ補完
    No出力 Me!No_補完
    
    保管月 = DateAdd("M", 1, 保管月)
    Me!移動月補完 = 保管月
    Me!保管個数表示補完 = 保管個数
    
    Me.NextRecord = False
End Sub

Private Sub 詳細_Format(Cancel As Integer, FormatCount As Integer)
    No出力 Me!No_入出庫
End Sub

Private Sub No出力(ctl As TextBox)
    No = No + 1
    ctl = No
End Sub

レポートのデザイン

１．「保管」の累計個数をどう計算するか

入庫を足して在庫を減らして、それを積み立てれば累計（残在庫）です。ACCESSレポートではテンプレート的な用法があるのでそのまま使います

詳細セクションに「累計」テキストボックスを配置します

コントロールソース｜=Nz([入庫数],0)-Nz([出庫数],0)
集計実行　　　　　｜全体

詳細セクションでデータを表示する必要はないので枠線だけ表示されるように調整しています。（非表示＋枠線は別なコントロールでとかでもいいです）

「移動月」でグループ化して、フッターを配置し、「保管」行として使います
「保管」専用の行なので「区分」はラベルコントロールでそのまま「保管」を表示、その他必要なテキストボックスを配置します

保管個数表示
コントロールソース｜=[累計]

保管手数料単価
コントロールソース｜=DLookUp("手数料単価","T_区分マスター","区分ID=3")

保管手数料
コントロールソース｜=[保管個数表示]*[保管手数料単価]

２．「No」の連番をどう取得するか

「保管」データの累計計算のためにセクションを跨いでしまったのでVBAで計算・設定します
非連結のテキストボックスに、データが出力されるたび+1してデータを設定するだけです。「データが出力されるたび」がどんな時かきちんと把握しておく必要があります

今回は適当に上から「No_補完」「No_入出庫」「No_保管」という名前にしました

出力のタイトルは「移動日」ですが、元データに「移動日」フィールドがあるので、データ段階では「移動月」の名前になっています。レポートに配置するラベルで「移動日」と表示すればいいと思います
この「移動月」でグループ化してヘッダー・フッターを設置します。デフォルトでは「グループヘッダー0」「グループフッター0」のような名前になっていますが、解説用にそれぞれ「移動月ヘッダー」「移動月フッター」と名前を変えています。

移動月ヘッダーの使い方は次での解説がメインですが、「No」表記が必要なのでここでの解説に「No_補完」が紛れ込んでいます

連番を付けるだけ、でいいのであればよくある手法があるんですが、その他の要求と組み合わせるとどうするといいのか悩みどころですね

３．「存在しない月のデータ」をどう補完するか

「新しいデータを読み込んだ時、古いデータとの比較で月が離れていたら、データを挿入する」方法を取ります
「データ」はレポートのクエリを基に詳細セクションで読み込まれるので、「『移動月』でグループ化し、グループヘッダーを設置すると、『新しいデータと古いデータの間』」になります

このグループヘッダーを出力しなかったり、出力したり、たくさん出力するようにすれば、存在しないデータの補完ができます

移動月ヘッダーで表示する「区分」も「保管」のみなので、区分はラベルコントロールです
その他のテキストボックスはそれぞれ

No_補完
コントロールソース：（非連結）

移動月補完
コントロールソース：（非連結）

保管個数表示補完
コントロールソース：（非連結）

保管手数料単価補完
コントロールソース：=[保管手数料単価]

保管手数料補完
コントロールソース：=[保管個数表示補完]*[保管手数料単価]

です。存在しない月データの補完用のセクションなので全部「補完」とつけてるだけです
新しいレコードに移って捨てられてしまうデータをVBAで保存しておいて必要に応じて（非連結の）テキストボックスに設定、セクションの出力（繰り返し出力）をするとかそんな流れになっています

その他

移動日/移動月については軽く触れていますが、そのほか、後の処理の（楽をしたい）都合で日付データとして各月の1日に統一したデータで扱っています
レポートの表示はテキストボックスの書式の設定（YYYY年MM月）で対応しています

「抽出の最小日以前から保管個数がある場合」は考慮していません

その他、細かいところは適当なので（代替の背景色が有効のせいで微妙に表示がおかしいとか）そういう調整は必要です

こういう案件の場合、ワークテーブルを用意してゴリゴリしてからそれをレポートにとかってなるんですかねぇ

Noの連番取るのにVBA使わないとダメなの何か方法あるんじゃない？って思わなくもないけど、保管用のデータ作るとこと考えるとそっちがめちゃくちゃ遠回りになりそうだしなぁとか
「とりあえずやってみたらそれっぽくできたので公開してるだけ」なのでこういう手法どうなの？とかあればください

・手数料単価が変更になる場合、変更になった月以前の計算結果が変わらないようにしなければなりません。

まずはこれの対策が先決ですね。

• 手数料単価の変更履歴テーブルを作成して、そこから対応する年月の単価を参照する設計にする。
• いっそのことレポートの出力形式とおなじになるテーブルを作成しておいて、VBAで出力する。

hirotonさんの提案の方法の場合は、前者の方法で手数料単価はDLookup関数で参照するように手直しすればいけると思います。
ただ、結局VBAが必須になるので、後者の方法でもいいかもしれません。

T_入出庫一覧に入庫情報・出庫情報を書き込む際に、以下のクエリを流してレコードを追加するようにしました。

INSERT INTO T_手数料 ( 区分, 移動日, 出庫数, 単価, 入出庫・保管料 )
SELECT T_在庫移動（出庫）tmp.区分, T_在庫移動（出庫）tmp.移動日, T_在庫移動（出庫）tmp.合計 AS 出庫数, T_区分マスター.単価, [合計]*[単価] AS 入出庫・保管料
FROM T_区分マスター INNER JOIN T_在庫移動（出庫）tmp ON T_区分マスター.区分 = T_在庫移動（出庫）tmp.区分;

INSERT INTO T_手数料 ( 区分, 移動日, 入庫数, 単価, 入出庫・保管料 )
SELECT T_入庫情報tmp.区分, T_入庫情報tmp.移動日, T_入庫情報tmp.合計 AS 入庫数, T_区分マスター.単価, [合計]*[単価] AS 入出庫・保管料
FROM T_区分マスター INNER JOIN T_入庫情報tmp ON T_区分マスター.区分 = T_入庫情報tmp.区分;

保管個数を計算するのは月末の１回だけになると思うので、保管個数計算用のフォームを作成してT_手数料にレコードを追加し、それを集計して目的のレポートにしようと考えています。

これで当時の手数料単価・手数料の計算結果が残るようには出来ました。

あとは、どのように保管個数を計算するかです。

・手数料単価が変更になる場合、変更になった月以前の計算結果が変わらないようにしなければなりません。

見落としていました。（というか、表形式で提示されているデータしか見てませんでした）

データ構造をどうするか？はもっと前の段階の話ですね。hatenaさん指摘の通りでいいと思いますが、どのような手法を取るにせよ「手数料単価の変更履歴テーブル」は有ったほうがいいと思います。「手数料の改訂」は案件の発生とは別に発生すると思われるので

手数料テーブルを作っておく場合

結局は>> 3の冒頭で提示しているレポートのようなテーブルを事前に作っておくという話なので、データ生成の流れは同じです。レポートのオブジェクト・出力仕様を駆使してやるか、それらを全てVBA内で自前で用意するかだけの違いです

累計計算になる「保管料の情報」がキャッシュされるので、それが正しいと保証する仕組みや追加分だけの計算ですますための、どこまでのデータは確定情報なのか（追加で計算すべきデータはどれか）判断できる仕組みが必要になると思います

■「正しいと保証する仕組み」について
遡りで入出庫の情報が修正されると“それ以降の月の保管料のデータが全て”修正対象になる

とか

当月保管料が未確定の状態で保管料のデータを作るかどうか
作る→都度修正する仕組みが必要
作らない→月半ばで一時的なレポート表示が欲しいとかで困る

とか、既にデータがある/ないが様々な処理に影響します

（レポートで）出力のたびに累計計算をやり直す仕組みはこういうものは考えなくていいので出力（計算時間）とのトレードオフですね

保管個数を計算するのは月末の１回だけになると思う

上述でちょっと指摘していますが、「当月の情報は当月が終わるまで未確定（出力無し）でよい」ならば、ですね
「月末に１回」正確に行われるか？も悩ましいところです

T_手数料集計用というテーブル（フィールド　No.：オートナンバー型　区分：短いテキスト　移動日：日付/時刻　入庫数：数値　出庫数：数値　保管個数：数値　単価：数値　入出庫・保管料：数値
に、入庫時・出庫時にレコードを入れ、

Q_総入出庫数集計用↓

SELECT T3_1_手数料集計用.区分, Format([移動日],"yyyy\年mm\月") AS 日, Sum(T3_1_手数料集計用.入庫数) AS 入庫数の合計, Sum(T3_1_手数料集計用.出庫数) AS 出庫数の合計
FROM T0_3_区分マスター INNER JOIN T3_1_手数料集計用 ON T0_3_区分マスター.区分 = T3_1_手数料集計用.区分
GROUP BY T3_1_手数料集計用.区分, Format([移動日],"yyyy\年mm\月"), T0_3_区分マスター.区分ID
HAVING (((Format([移動日],"yyyy\年mm\月"))<=[Forms]![F3_1_集計期間選択]![集計年月]))
ORDER BY Format([移動日],"yyyy\年mm\月"), T0_3_区分マスター.区分ID;

Q_保管個数↓

SELECT Sum(Q3_1_総入出庫数集計用.入庫数の合計) AS 総入庫数, Sum(Q3_1_総入出庫数集計用.出庫数の合計) AS 総出庫数, [総入庫数]-[総出庫数] AS 保管個数, Date() AS 移動日
FROM T0_3_区分マスター INNER JOIN Q3_1_総入出庫数集計用 ON T0_3_区分マスター.区分 = Q3_1_総入出庫数集計用.区分
GROUP BY Date();

を作成し、フォームで指定した期間までの総入庫数から総出庫数を引いた保管個数を求めることができました。
このQ_保管個数の保管個数にT_区分マスターの保管の手数料を掛ければ求めたい保管手数料を計算できると思うのですが、どのようにT_区分マスターの値を引っ張ってくればいいのか分かりません。

INSERT INTO T3_1_手数料集計用 ( 保管個数, 区分, 単価, 入出庫・保管料, 移動日 )
SELECT [総入庫数]-[総出庫数] AS 保管個数, DLookUp("区分","T0_3_区分マスター","区分ID=3") AS 区分, DLookUp("単価","T0_3_区分マスター","区分ID=3") AS 単価, [保管個数]*[単価] AS 入出庫・保管料, Date() AS 移動日
FROM T0_3_区分マスター INNER JOIN Q3_1_総入出庫数集計用 ON T0_3_区分マスター.区分 = Q3_1_総入出庫数集計用.区分
GROUP BY DLookUp("区分","T0_3_区分マスター","区分ID=3"), DLookUp("単価","T0_3_区分マスター","区分ID=3"), Date();

Q_保管個数を加工し、このようなクエリにしました。
あとはこのクエリでT_手数料集計用にレコードを追加できれば良いのですが、クエリ実行の際に総入庫数・総出庫数のパラメータを要求されてしまいます。T_手数料集計用に総入庫数・総出庫数のフィールドはありませんが、保管個数の演算用にこの2つのフィールドを消すわけにもいきません。どのようにすればよいでしょうか？

過去の書き込みを軽くみただけですが、「T0_3_区分マスター」「Q3_1_総入出庫数集計用」の2テーブルだけですべての項目が出力できるのかな？とは感じました。

とりあえずクエリを単純化して確認することをおすすめします。
「INSERT INTO」する前に「SELECT」だけで結果を見る、や文言の間違い、「GROUP BY」を使わない場合にほしい項目が揃っているか否か、などを順に確認するのが良いかなと。

パラメータを要求される場合、大抵は意図しないケアレスミスが原因であることが多いですね。

現状のテーブル設計がどうなっているか不明なので、回答が難しいです。

そもそものデータの作り方がまちがっているのでしょうか？

これについては、そもそも質問の最初の"T_入出庫一覧"テーブルの設計から間違っています。
テーブルの正規化ができていません。
前の質問の時も指摘しました。

レポートへの出力のレイアウトに合わせた設計にしたのでしょうが、データベースではこのようなデータの持ち方はしません。
今回のレポートの出力だけにしか使わないデータならいいのですが、今後、データベースの機能を活かした使い方に発展させていくことがあるのなら今のうちにテーブルの正規化から始めた方がいいでしょう。

正規化されていないテーブルはデータベースとしては使い物になりません。
正規化されていれば、そこから希望のレイアウトで出力することも可能です。

このデータは今回のレポート出力のみにしか使わないのでしょうか。
「在庫管理システム」ということなのでそうではないですよね。

一口に「正規化」といってもなかなか難しいですね・・・上手く理解できていないもので

正規化のコツ・正規化したテーブルの活用のコツ等はありますでしょうか？
とんちんかんな質問でしたら申し訳ございません。

最初の"T_入出庫一覧"テーブルを正規化すると下記のようになります。

T_区分マスター

正規化についてはWEB検索すれば解説ページは多数見つかりますので、わかりやすそうな所をいろいろ見て理解を深めてください。
とりあえず下記を紹介しておきます。

正規化とは - もう一度学ぶMS-Access

正規化の実例 - もう一度学ぶMS-Access