Q&A
差し当たっては、クエリ―の最適化、インデックスの追加、レコードソースの見直しあたりになると思いますので、以下を質問を編集し追記して下さい
・フォーム/サブフォームのレコードソースとリンク親/子フィールドの内容
・クエリーの内容(SQLビューの内容)
・クエリーおよびフォームのレコードソースの元になるテーブルの定義
前提・実現したいこと
はじめまして。自力では解決せず教えて頂きたいです。 Accessサブフォーム内で集計するデータベースを作りましたが サブフォーム内の計算の処理が、入力したレコードの増える毎に 遅くなります。（エラー等は出ず、動作自体はしています） クエリのフィールド内の処理が原因と考えていますが 速度の改善に具体的にどのような手立てがあるのか分かりません。
発生している問題・エラーメッセージ
サブフォーム内の選択しているレコードより1つ若いレコードの数値を 減算するフィールドがあるのですが、入力したレコードが増える毎に 処理が遅くなります。 (以下の説明図のサブフォーム内項目名「総重量」入力時、 サブフォーム内のレコードが5～6行あたりまで増えると 測定量の表示処理が数分かかることがあります。 実際の運用時には10行以上入力することもあります。)
該当のソースコード
総重量フィールド入力時に処理が絡むフィールドの中身は以下です。
・クエリ名"フィルター3"
測定量: IIf([No]=1,0,[総重量]-(DLookUp("[総重量]","フィルター3","[No]=" & Nz([No]-1,1)))) No: CInt(DCount("[フィルター1].[ID]","フィルター3","[フィルター1].[ID]<=" & [フィルター1].[ID]))
・クエリ名"フィルター1"
IDはオートナンバー型で入力した順番を見ています。
総重量はサブフォームで手入力されたデータの入れ物です。
ID,総重量フィールドはテーブル名"履歴"直結です。
（フィルター1クエリではメインフォームで入力した所属や日付、番号から
履歴テーブルを絞り込む役割で作りました。）
ID:ID 総重量:総重量
総重量入力時のVBAは以下です。
(正直、VBAに関してはよく分からず書いてます。
表示が上手く行ったのでこれでいいのかなレベルです…)
Private Sub 総重量_AfterUpdate() Me.測定量.Requery Me.測定量計算.Requery End Sub Private Sub 総重量_GotFocus() Me.測定量計算.Requery End Sub
測定量計算はサブフォームフッターにテキストで表示しています。
(説明図で表示されている総測定量です)
=Max("総重量")-DMin("総重量","フィルター3")
試したこと
VBAに書き換えることで速くなるのではと期待し、勉強していますが
私自身ネットに上げてくださっている既存のプログラムの一部を
切り貼りしながら初めてデータベースを作った程度の実力で
お恥ずかしながら自力で書いては上手く動かず苦戦しています。
補足情報（FW/ツールのバージョンなど）
Microsoft Access 2019(.accdb)
以下追記です。
・フィルター3のSQLです。
SELECT DISTINCTROW [フィルター1].ID, CInt(DCount("[フィルター1].[ID]","フィルター3","[フィルター1].[ID]<=" & [フィルター1].[ID])) AS [No], [フィルター1].[BNo], [フィルター1].日付, [フィルター1].[NGNo], [フィルター1].発注, 充填量.規定量, [フィルター1].規定量 AS F1規定量, IIf([No]=1,0,[総重量]-(DLookUp("[総重量]","フィルター3","[No]=" & Nz([No]-1,1)))) AS 測定量, [フィルター1].測定量 AS F1測定量, [フィルター1].総重量, [フィルター1].[所属], [フィルター1].種類, [フィルター1].区分, [フィルター1].担当者 FROM フィルター1 LEFT JOIN 充填量 ON [フィルター1].発注 = 充填量.発注
・フィルター1のSQLです。
SELECT 履歴.ID, Year([日付]) AS 移充填年, Month([日付]) AS 移充填月, 履歴.種類, 履歴.[所属], 履歴.[BNo], 履歴.日付, 履歴.区分, 履歴.[NGNo], 履歴.発注, 履歴.規定量, 履歴.測定量, 履歴.担当者, 履歴.総重量 FROM 担当者 INNER JOIN 履歴 ON 担当者.ID = 履歴.担当者 WHERE (((Year([日付]))=[Forms]![入力画面]![年選択]) AND ((Month([日付]))=[Forms]![入力画面]![月選択]) AND ((履歴.種類)=[Forms]![入力画面]![種類選択]) AND ((履歴.[所属])=[Forms]![入力画面]![所属選択]));
テーブルについては以下です。
・履歴テーブル(入力毎に増え最大200ほど)
ID：オートナンバー型
日付：日付/時刻型
区分、担当者、所属、種類：数値型(長整数型)
規定量、測定量、総重量：数値型(倍精度浮動小数点型、小数点以下3桁)
発注、BNo、NGNo：テキスト型(フィールドサイズ20)
・充填量テーブル(規定でそこまで増えません。データ数1500ほど)
規定量：数値型(単精度浮動小数点型、小数点以下3桁)
発注：テキスト型(フィールドサイズ7)
回収ID：数値型
情報が足りていなければ申し訳ありません。追記します。
ありがとうございます。質問編集致します。
取り敢えず、フィルター3やフィルター1のSQLの内容だけでも先ずは追記して下さい。
・フォーム/サブフォームのレコードソースとリンク親/子フィールドの内容
これについてはどういった情報が必要で何を追記していいのか浅学ゆえよく分かりません…教えて頂けると助かります
(また本日作業できる時間がもう無いので明日記載します…教えて頂く立場でお待たせして申し訳ないです)
フィルター3にある[No]は履歴テーブルの項目ですか？
[No]はテーブルには格納してないです。
測定量を計算したくて、自分より若いテーブルを認識するために作った式だけのフィールドです。
作ったのはフィルター1にですか？
また、それは履歴.IDの事ですか？
DCount（”ID”、”履歴", ・・・)などで作成している連番という事ならそれもきちんと明示して下さい。
[No]を作ったのはフィルター3クエリです。
No: CInt(DCount("[フィルター1].[ID]","フィルター3","[フィルター1].[ID]<=" & [フィルター1].[ID]))
履歴.IDはフィルター1クエリのオートナンバー型です。
メインフォームの年月や番号をソートしたものがサブフォームに表示されるのですが
履歴テーブル内のIDではサブフォーム上で数値が飛ぶ場合がありました。
[No]で番号を振り直して通し番号にしました。
[測定量]で通し番号を使ってIIF関数で引き算するか判断させました。
（自分の引き出しが本当にないので変な方法かもしれません。）
失礼。フィルター3クエリから見落としていました。
回答1件
0
ベストアンサー
クエリーからクエリーを呼び出す形になっているので遅いのだと思われます。
先ず、測定量計算は以下に変更してみて下さい
=Max("総重量")-Min("総重量")
改善されないようなら、フィルター１のクエリーを以下に変更して下さい。
以下のクエリーはフィルター３の内容も兼ねているので、フィルター３は不要です。
SQL
1SELECT 履歴.ID 2 , Year(日付) As 移充填年 3 , Month(日付) As 移充填月 4 , 履歴.種類 5 , 履歴.所属 6 , 履歴.BNo 7 , 履歴.日付 8 , 履歴.区分 9 , 履歴.NGNo 10 , 履歴.発注 11 , 履歴.規定量 AS F1規定量 12 , 履歴.測定量 AS F1測定量 13 , 履歴.担当者 14 , 履歴.総重量 15 , DLookup("規定量", "充填量", "発注='" & 履歴.発注 & "'") As 規定量 16 , Nz(DMax("ID", "履歴", "ID < " & 履歴.ID & " AND 日付=#" & 日付 & "# AND 種類='" & 種類 & "' AND 所属='" & 所属 & "'"), 0) As 前回ID 17 , DLookUp("総重量","履歴","ID=" & [前回ID]) As 前回総重量 18 , Nz(総重量 - [前回総重量], 0) As 測定量 19FROM 履歴 INNER JOIN 担当者 20 ON 担当者.ID = 履歴.担当者 21WHERE Year(日付) =[Forms]![入力画面]![年選択] 22 AND Month(日付)=[Forms]![入力画面]![月選択] 23 AND 履歴.種類 =[Forms]![入力画面]![種類選択] 24 AND 履歴.所属 =[Forms]![入力画面]![所属選択] 25;
それから、履歴テーブルに、(種類,所属,日付,ID)のインデックスを作成するとさらに性能が改善されるかもしれません。
ネスト構成のSQLは以下です。
ネストしている部分(step1・2)をそれぞれクエリーにしても可
SQL
1SELECT Step2.* 2 , Year(日付) As 移充填年 3 , Month(日付) As 移充填月 4 , Step2.規定量 AS F1規定量 5 , Step2.測定量 AS F1測定量 6 , step2.充填規定量 As 規定量 7 , Nz(総重量 - 前回総重量, 0) As 測定量 8FROM ( 9 SELECT Step1.* 10 , DLookUp("総重量","履歴","ID=" & 前回ID) As 前回総重量 11 FROM ( 12 SELECT 履歴.* 13 , DLookup("規定量", "充填量", "発注='" & 履歴.発注 & "'") As 充填規定量 14 , Nz(DMax("ID", "履歴", "ID < " & 履歴.ID & " AND 日付=#" & 日付 & "# AND 種類='" & 種類 & "' AND 所属='" & 所属 & "'"), 0) As 前回ID 15 FROM 履歴 16 WHERE Year(日付) =[Forms]![入力画面]![年選択] 17 AND Month(日付)=[Forms]![入力画面]![月選択] 18 AND 履歴.種類 =[Forms]![入力画面]![種類選択] 19 AND 履歴.所属 =[Forms]![入力画面]![所属選択] 20 ) As Step1 21) As Step2 22;
ただ、このようにネストさせても、データ件数が多く集計などが絡む場合に演算項目が確定しないケースもあります。
その場合は、ネストしている部分をワークテーブルに結果を書き出すなどして、値を確定させると改善します。
追記
更新対象のデータを参照するような作りだと、#エラーが表示される状況を回避するのは難しいかもしれません。
イベント処理で、計算した結果を書き込むようにする方が確実だと思います。
レコード単位の編集イベント
今回の場合だと、AfterInsertやAfterUpdateイベントで、測定量を編集するようにすれば良いと思います。
投稿2021/04/21 15:14
編集2021/04/22 05:00総合スコア25327
ありがとうございます！実際に書き換えて動作を見てみます！！！
(フォームとフィルター３のリンクずぶずぶなので書き換えに時間が掛かりそうです><）
いきなりフィルター１を変更するのではなく、別なクエリーで結果を確認してからにして下さいね。
取りあえずで行っていますが、本来、エイリアスの部分はネストさせて使用する方が良いので。
フィルター１のコピーという名前のクエリで回してみました。
指定されたフィールド'所属'がSQLステートメントのFROM句にある複数のテーブルを参照しました。と出ました。
自分なりに考えてみたくて切り分けてたのですが、
前回ID:Nz(DMax("ID", "履歴", "ID < " & 履歴.ID & " AND 日付=#" & 日付 & "# AND 種類='" & 種類 & "' AND 所属='" & 所属 & "'"), 0)
こちらを消すとエラー消えますが肝心のどこをどうしたらいいか
結局自分で解決出来ませんでした…。
（そして宜しければエイリアスの部分はネストするという意味も知りたいです。
ググったらエイリアス＝コマンド名、ネスト＝巣と出ましたがよく分かりませんでした…）
ラインを履歴.[ライン]にしたらエラーきえました！
確認してみると、前回ID、前回総重量、測定量ともに＃エラーの表示になりました。
(こちらの勘違いで何度もコメント編集して通知飛んでいたら申し訳無いです)
>前回ID、前回総重量、測定量ともに＃エラーの表示になりました。
履歴テーブルを更新した状態で、確認は行っていますが、編集しながらだとリフレッシュのタイミングなどにより厳しいかもしれませんね。
今までは遅いからデータが揃っていたけど、早くなるとデータが揃わない状態で表示しようとして結果的にエラーとなっている気がします。
取り敢えずはネストさせてみて、駄目な様なら、レコードの更新後イベントなどで、値を書き込むとか。
ただ、駄目な場合の手間を考えると更新後イベントで行うようにした方が良いかもしれません。
ご丁寧にありがとうございます！＃エラーはそのような理由で出ていたのですね。
書いていただいた解説やプログラムの意味をちゃんと咀嚼するには
なかなか時間が掛かりそうですがリンク先の記事等読んで勉強したいと思います。
では実際のフォームで動くようにする作業していきます。ありがとうございました！
あなたの回答
tips
プレビュー
