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();
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();
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
ありがとうございました。コードを下記で記述したのですが 『＊＊＊大分類IDフィールドが見つかりません』のエラーが出ました。（大分類ＩＤでグループ化してまして、それの３を非表示にしたい）
Private Sub グループフッター1_Format(Cancel As Integer, FormatCount As Integer)
Cancel = [大分類ID] = 3
End Sub
コードの記述が間違ってますでしょうか？
Private Sub 詳細_Format(Cancel As Integer, FormatCount As Integer)
Dim j As Integer
Dim i As Integer
For j = 51 To 57
Me.DrawStyle = 0
Me.Line (Me("L" & j).Left, 0)-(Me("L" & j).Left, 144000)
Next
' For i = 2 To 12
' Me.DrawStyle = 2
' Me.Line (Me("L" & i).Left, 0)-(Me("L" & i).Left, 144000)
' Next
WordWrapOff Me.NAS用, Me.txtFld1
Me.NAS用.FELineBreak = False
Me.txtFld1.FELineBreak = False
End Sub
一口に「正規化」といってもなかなか難しいですね・・・上手く理解できていないもので
正規化のコツ・正規化したテーブルの活用のコツ等はありますでしょうか？
とんちんかんな質問でしたら申し訳ございません。
hatenaさんありがとうございます。”テーブルで可能なことは、テーブルで行う” は確かにその通りですね。今回の件以外で、ついついクエリ時の関数で複雑な分岐をしがちなので大変参考になりました。
現状のテーブル設計がどうなっているか不明なので、回答が難しいです。
これについては、そもそも質問の最初の"T_入出庫一覧"テーブルの設計から間違っています。
テーブルの正規化ができていません。
前の質問の時も指摘しました。
レポートへの出力のレイアウトに合わせた設計にしたのでしょうが、データベースではこのようなデータの持ち方はしません。
今回のレポートの出力だけにしか使わないデータならいいのですが、今後、データベースの機能を活かした使い方に発展させていくことがあるのなら今のうちにテーブルの正規化から始めた方がいいでしょう。
正規化されていないテーブルはデータベースとしては使い物になりません。
正規化されていれば、そこから希望のレイアウトで出力することも可能です。
このデータは今回のレポート出力のみにしか使わないのでしょうか。
「在庫管理システム」ということなのでそうではないですよね。
普段、SQL ServerのDBを使っているのでAccessでのクエリの動きははそんなに詳しくないのですが、「[総入庫数] - [総出庫数] AS 保管個数」を計算する[総入庫数]と[総出庫数]の追加先が「T3_1_手数料集計用」に無いからエラーになるってことでしょうか。
そうだとすると計算にはINSERT先はまだ関連してないのに、ちょっと不便な仕様ですね・・・。
例えばSELECT部分を意味なくまとめてもだめでしょうか？
SELECT
SELECT
FROM
GROUP BY
) as T1
、のような感じです。
また、どうしてもだめであれば追加先のテーブルに総入庫数・総出庫数フィールドを追加すればいいのではないでしょうか。
パラメータを要求される理由は分かっていて、保管個数の演算に使っている総入庫数・総出庫数フィールドの追加先を設定していないためです。
しかし、追加先のテーブルには総入庫数・総出庫数を入れるフィールドはなく、しかし総入庫数・総出庫数がないと保管個数が出せないため困っています。
過去の書き込みを軽くみただけですが、「T0_3_区分マスター」「Q3_1_総入出庫数集計用」の2テーブルだけですべての項目が出力できるのかな？とは感じました。
とりあえずクエリを単純化して確認することをおすすめします。
「INSERT INTO」する前に「SELECT」だけで結果を見る、や文言の間違い、「GROUP BY」を使わない場合にほしい項目が揃っているか否か、などを順に確認するのが良いかなと。
パラメータを要求される場合、大抵は意図しないケアレスミスが原因であることが多いですね。
そもそものデータの作り方がまちがっているのでしょうか？
Q_保管個数を加工し、このようなクエリにしました。
あとはこのクエリでT_手数料集計用にレコードを追加できれば良いのですが、クエリ実行の際に総入庫数・総出庫数のパラメータを要求されてしまいます。T_手数料集計用に総入庫数・総出庫数のフィールドはありませんが、保管個数の演算用にこの2つのフィールドを消すわけにもいきません。どのようにすればよいでしょうか？
あ、質問主です。
T_手数料集計用というテーブル（フィールド No.：オートナンバー型 区分：短いテキスト 移動日：日付/時刻 入庫数：数値 出庫数：数値 保管個数：数値 単価：数値 入出庫・保管料：数値
に、入庫時・出庫時にレコードを入れ、
Q_総入出庫数集計用↓
Q_保管個数↓
を作成し、フォームで指定した期間までの総入庫数から総出庫数を引いた保管個数を求めることができました。
このQ_保管個数の保管個数にT_区分マスターの保管の手数料を掛ければ求めたい保管手数料を計算できると思うのですが、どのようにT_区分マスターの値を引っ張ってくればいいのか分かりません。
入力チェックはテーブルで設定しておくのが確実ですね。下記もご参考に。
テーブルでの入力チェック、入力規制 - hatena chips
ただ、これだけだとシステムの出すメッセージは不親切だし、未入力項目にフォーカス移動させるなど細かい制御ができないので、それがしたいときはVBAを使うことになります。その場合は下記などもご参考に。
複数項目の入力チェックを共通モジュール化する - hatena chips
hatenaさんありがとうございます。確かに「値入力」プロパティを「はい」がシンプルで一番いいですね。要は必須項目が未入力なら保存不可にしたらいいですね。こうしておけば入力順番もそんなに考えなくてもいいですね。出てくる現象に対してだけ対処しても根本が不完全なら意味ないですね。全体を見て作成しなくては上手くいきませんね。
まだまだ勉強が必要よ痛感しました。大変参考になりました。
前の回答でふれたように現状の仕様ではいろいろ不具合が出てくると思いますが、
とりあえずレコード移動したときに、常に[F1]にフォーカスを移動させておきたいのなら、
サブフォームのレコード移動時のイベントプロシージャを下記のように設定したらどうでしょう。
あるいは、F1フィールドが未入力の場合のみ移動させるなら、
お返事ありがとうございます。
これを使いたいのですが，今のところ，Microsoft 365サブスクライパーのみとなっているみたいです。
パソコンについていたOffice2021にAccess2021だけを追加しているので，
これ以上お金をかけないでなんとかできないかな？と悪あがきをしているのですが…。
前にもいったようにフォーカス系のイベント内でSetFocusで制御しようとすると、今回のような場合も含めて、いろいろ複雑になり制御しきれません。ユーザーも必ず先頭項目から順に入力してくれるとは限りませんし、すべての項目を入力してから必須項目のデータを削除したりとかもありえます。それらも考慮するとますます泥沼になります。
一番確実なのは、入力必須フィールドはテーブルデザインで「値入力」プロパティを「はい」に設定しておくことです。
そうすればそのフィールドが未入力状態ではレコード保存できなくできます。
ユーザーに入力順を強制するというユーザーインターフェイス(UI)が本当に必要でしょうか。
レコード保存する前に、必須項目は入力されているか、入力値に矛盾はないか等をチェックして、問題があるときはメッセージを表示してユーザーに修正を即するというUIのほうが使いやすいし、設計もはるかに楽です。
先ほど投稿したのですが、内容間違えてました。メインの項目だけ入力して（サブには未入力）新規レコードボタンで移動することは現状カバー出来てました。失礼しました。
やはりメイン・サブの入力後に新規レコードボタンで移動するとサブの直近に入力した項目にフォーカスが残ったままなので（Ｆ２、Ｆ３・・・）に留まったままなのでメイン入力後サブに移動すると記述しているメッセージが出るのをなんとかしたいということです。一回毎にフォームを閉じるとこうならないのですが。どう制御するといいか分からないので。
hatenaさんありがとうございます。 MsgBox ("先にF1を入力して下さい。")のコードはＦ１以降の項目のフォーカス取得後（GotFocus）イベントに記述しています。防ぎたいことは例えばメインの項目だけ入力して（サブには未入力）新規レコードボタンで移動することです（他の移動ボタンにもそうですが）。確かに今の方法では都度の対処だけの感じですね。
全体で考えるならどんなアイデアがあるでしょうか？ 思いつかないもので。
このコードをどこのどのイベントに記述してますか。一部を抜粋するのではなく、
Sub から End Sub までをすべてコピペしてもらった方が話かはやいと思います。
どちらにしても入力必須項目をSetFocusで制御しようという仕様は無理があると思いますので、この仕様から考え直した方かいい気がします。
よくわからないけど、結局WebブラウザコントロールってIEベースなのでもうダメ（Acrobat
側がIEを切ってるのでどうしようもない）とかなんじゃないですかね
別なコントロールを検討するしかないんじゃないでしょうか？
最新のACCESSだと標準で新しいWebブラウザコントロールもあるようです
EdgeBrowserControl オブジェクト (Access)
見落としていました。（というか、表形式で提示されているデータしか見てませんでした）
データ構造をどうするか？はもっと前の段階の話ですね。hatenaさん指摘の通りでいいと思いますが、どのような手法を取るにせよ「手数料単価の変更履歴テーブル」は有ったほうがいいと思います。「手数料の改訂」は案件の発生とは別に発生すると思われるので
手数料テーブルを作っておく場合
結局は>> 3の冒頭で提示しているレポートのようなテーブルを事前に作っておくという話なので、データ生成の流れは同じです。レポートのオブジェクト・出力仕様を駆使してやるか、それらを全てVBA内で自前で用意するかだけの違いです
累計計算になる「保管料の情報」がキャッシュされるので、それが正しいと保証する仕組みや追加分だけの計算ですますための、どこまでのデータは確定情報なのか（追加で計算すべきデータはどれか）判断できる仕組みが必要になると思います
■「正しいと保証する仕組み」について
遡りで入出庫の情報が修正されると“それ以降の月の保管料のデータが全て”修正対象になる
とか
当月保管料が未確定の状態で保管料のデータを作るかどうか
作る→都度修正する仕組みが必要
作らない→月半ばで一時的なレポート表示が欲しいとかで困る
とか、既にデータがある/ないが様々な処理に影響します
（レポートで）出力のたびに累計計算をやり直す仕組みはこういうものは考えなくていいので出力（計算時間）とのトレードオフですね
上述でちょっと指摘していますが、「当月の情報は当月が終わるまで未確定（出力無し）でよい」ならば、ですね
「月末に１回」正確に行われるか？も悩ましいところです
上記の環境で，Accessのフォーム上に異なる3つのPDFファイルを表示する機能を，ActiveXのWebブラウザコントロールに変更して使っているのですが，何回か表示を繰り返すと，
Adobe AcrobatまたはReaderに問題があります
Acrbat または Reader を実行している場合，もういちどやり直してください(0:521)
というエラーが出たり，Accessが固まったりするので，困っています。あちこち調べたら，
・直前に，
XXX.Controls(Webブラウザコントロール名).Navigate "about:blank"
を入れる
・Adobe Readerの「環境設定」→「セキュリティ（拡張）」で「起動時に保護モードを有効にする」をオフにする
とあったので試したのですが，どちらも効果がありません。他に回避方法をご存じの方がありましたら，ご教授願えませんでしょうか？
T_入出庫一覧に入庫情報・出庫情報を書き込む際に、以下のクエリを流してレコードを追加するようにしました。
保管個数を計算するのは月末の１回だけになると思うので、保管個数計算用のフォームを作成してT_手数料にレコードを追加し、それを集計して目的のレポートにしようと考えています。
これで当時の手数料単価・手数料の計算結果が残るようには出来ました。
あとは、どのように保管個数を計算するかです。
まずはこれの対策が先決ですね。
hirotonさんの提案の方法の場合は、前者の方法で手数料単価はDLookup関数で参照するように手直しすればいけると思います。
ただ、結局VBAが必須になるので、後者の方法でもいいかもしれません。
その他
移動日/移動月については軽く触れていますが、そのほか、後の処理の（楽をしたい）都合で日付データとして各月の1日に統一したデータで扱っています
レポートの表示はテキストボックスの書式の設定（
YYYY年MM月）で対応しています「抽出の最小日以前から保管個数がある場合」は考慮していません
その他、細かいところは適当なので（代替の背景色が有効のせいで微妙に表示がおかしいとか）そういう調整は必要です
こういう案件の場合、ワークテーブルを用意してゴリゴリしてからそれをレポートにとかってなるんですかねぇ
Noの連番取るのにVBA使わないとダメなの何か方法あるんじゃない？って思わなくもないけど、保管用のデータ作るとこと考えるとそっちがめちゃくちゃ遠回りになりそうだしなぁとか
「とりあえずやってみたらそれっぽくできたので公開してるだけ」なのでこういう手法どうなの？とかあればください
３．「存在しない月のデータ」をどう補完するか
「新しいデータを読み込んだ時、古いデータとの比較で月が離れていたら、データを挿入する」方法を取ります
「データ」はレポートのクエリを基に詳細セクションで読み込まれるので、「『移動月』でグループ化し、グループヘッダーを設置すると、『新しいデータと古いデータの間』」になります
このグループヘッダーを出力しなかったり、出力したり、たくさん出力するようにすれば、存在しないデータの補完ができます
移動月ヘッダーで表示する「区分」も「保管」のみなので、区分はラベルコントロールです
その他のテキストボックスはそれぞれ
です。存在しない月データの補完用のセクションなので全部「補完」とつけてるだけです
新しいレコードに移って捨てられてしまうデータをVBAで保存しておいて必要に応じて（非連結の）テキストボックスに設定、セクションの出力（繰り返し出力）をするとかそんな流れになっています
１．「保管」の累計個数をどう計算するか
入庫を足して在庫を減らして、それを積み立てれば累計（残在庫）です。ACCESSレポートではテンプレート的な用法があるのでそのまま使います
詳細セクションに「累計」テキストボックスを配置します
詳細セクションでデータを表示する必要はないので枠線だけ表示されるように調整しています。（非表示＋枠線は別なコントロールでとかでもいいです）
「移動月」でグループ化して、フッターを配置し、「保管」行として使います
「保管」専用の行なので「区分」はラベルコントロールでそのまま「保管」を表示、その他必要なテキストボックスを配置します
２．「No」の連番をどう取得するか
「保管」データの累計計算のためにセクションを跨いでしまったのでVBAで計算・設定します
非連結のテキストボックスに、データが出力されるたび
+1してデータを設定するだけです。「データが出力されるたび」がどんな時かきちんと把握しておく必要があります今回は適当に上から「No_補完」「No_入出庫」「No_保管」という名前にしました
出力のタイトルは「移動日」ですが、元データに「移動日」フィールドがあるので、データ段階では「移動月」の名前になっています。レポートに配置するラベルで「移動日」と表示すればいいと思います
この「移動月」でグループ化してヘッダー・フッターを設置します。デフォルトでは「グループヘッダー0」「グループフッター0」のような名前になっていますが、解説用にそれぞれ「移動月ヘッダー」「移動月フッター」と名前を変えています。
移動月ヘッダーの使い方は次での解説がメインですが、「No」表記が必要なのでここでの解説に「No_補完」が紛れ込んでいます
連番を付けるだけ、でいいのであればよくある手法があるんですが、その他の要求と組み合わせるとどうするといいのか悩みどころですね
適当に「2024年3月に400個出庫」のデータを加えたサンプル
hirotonは「簡単な手法」が思い浮かばなかったので覚悟してください
複雑な要件なので問題を分解します
１．「保管」の累計個数をどう計算するか
２．「No」の連番をどう取得するか
３．「存在しない月のデータ」をどう補完するか
回答としては一緒くたにやっていますが、どの問題を解決するためのものなのか考えながら見てください
レポートのレコードソースのクエリ
上記クエリによるレポート出力用の基礎データ例
レポートのモジュール
レポートのデザイン
両方あり得ます。
入庫・出庫が同一月に発生する場合、１行目入庫、２行目出庫、３行目保管になるようにしたいと考えています。
とりあえず確認
「入庫」「出庫」が同一月に発生することはありますか？その場合どのような表示になりますか？
「入庫」「出庫」が発生せず、「保管」だけが発生する月はありますか？
ありがとうございました。テキストボックス配置しましたら思い通りになりました。
ＡＣＣＥＳＳレポートは複雑でこんな方法は全く知りませんでした。
このグループだけレポートフッターに配置した他のデータから参照した値を含めた合計で表示したかったのです。
お世話になりました。
ありがとうございます！
非連結にすればよかったのですね。
回答頂けるまでにいろいろ試して、区分・商品1～4・合計・移動日・在庫移動日・発注日フィールドを持つtmpテーブルを作成し、こちらをレコードソースにしてフォーム作成しました！
結果が1件だけなら、いろいろ方法はありますが、
フォームの「在庫移動日」は非連結テキストボックスにしておきます。そうすれば入力可能になります。
レコードソースの集計クエリをもとに追加クエリを作成して、追加先は「T_入出庫状況」にします。
追加元と追加先の対応するフィールドを設定して、次に新規列に下記の設定をします。
SQLだと下記のようなになります。
フォーム上にコマンドボタンを配置して、そのクリック時にこの追加クエリを実行するようにすればいいでしょう。
グループフッターに大分類IDフィールドをテキストボックスとして配置してください。
大分類IDを表示したくない場合は「可視」を「いいえ」にします。
ありがとうございました。コードを下記で記述したのですが 『＊＊＊大分類IDフィールドが見つかりません』のエラーが出ました。（大分類ＩＤでグループ化してまして、それの３を非表示にしたい）
Private Sub グループフッター1_Format(Cancel As Integer, FormatCount As Integer)
Cancel = [大分類ID] = 3
End Sub
コードの記述が間違ってますでしょうか？
"T_発注書取込"テーブルの構成は
取込ID（オートナンバー）主キー
No（数値）
社名（短いテキスト）
〒（短いテキスト）
住所（短いテキスト）
TEL（短いテキスト）
商品1（短いテキスト）
商品2（短いテキスト）
商品3（短いテキスト）
商品4（短いテキスト）
発注日（日付/時刻）
発注日が複数日に渡ることはないので、グループ化で結果は1件だけになります。
この集計クエリの結果は1件だけですか。それとも複数件になりますか。
複数件の場合は、「在庫移動日」はすべて共通でいいのですか。それともレコード毎に異なるものを入力しますか。
それ以前に、現状のテーブルのフィールド構成はどうなってますか。
主キー設定も含めて提示してください。
はい、そうです。
とりあえず試してみてうまく行かない場合、質問してください。
フォーマット時イベントのCancel引数をTrueにすれば、そのセクションの出力を停止できます。
そのグループフッターの可視プロパティをフォーマット時イベントで制御すればいいかな？
※各名称は適切なモノに置き換えてください
また、
とし、詳細セクションのフォーマットイベントにあった罫線スタイルの指定をなくしたところうまくいきました。
（前はここがダブってしまっていて、線がきれいに点線になっていませんでした。）
大変失礼しました。
以上にて無事に担当に提出することが出来ました。
本当に長々とありがとうございました。