Microsoft Access 掲示板

Accessは( Microsoft謹製のDBMSでは )
相関サブクエリのパフォーマンスがJOINに比べて劣る印象はありますね

しかしながら、Access-SQLにおいて

削除したいと思っています

という場合、
つまり、UPDATE文やDELETE文を発行するケースでは
FROM句やJOIN句に sum, max, min といった集計関数を含めることは出来ません
( 利用できるのは、パフォーマンスが悪いD系の定義域集計関数だけ )

そのため、hatenaさんのアドバイスにあるとおり適切にINDEXを設定した上で
以下のようなSQLを発行するといいでしょう

SELECT * FROM Mtbl_所属歴 x
WHERE EXISTS
(
    SELECT 1 FROM Mtbl_所属歴 y
     WHERE x.社員No = y.社員No
    HAVING x.開始日 > min(y.開始日)
       AND x.終了日 < max(y.終了日)
);

　
結果セットとパフォーマンスが確認できたら
SELECT → DELETE に変更し

２．特定の名称（育児、介護の文字が含まれる）が含まれる

の条件も付与して下さい

なお、上記の相関サブクエリでも

パフォーマンスが悪すぎてまともに動きません

という場合は
hatenaさんの回答>> 2のサブクエリ Q1 の結果セットを
一旦テンポラリテーブルに格納するといいでしょう

フォームのレコードセットはDAOレコードセットなので、下記あたりを参考に学習してください。

VBA DAO レコードセット 更新 - Google 検索

フォームのレコードセットについては、下記で詳しく解説しています。

フォームの Recordset, RecorsetClone, RecordSet.Clone の違いとは？ - hatena chips

１．開始日の最小値と終了日の最大値以外の所属を抽出し、

意味があるのかしら？例えば、年度毎に保管期間5年間して過ぎたら削除する、みたいな感じじゃダメなの？

コンボ351 というのはサブフォームにあるのですよね。
ならば、hirotonさんが提案されたフォームのレコードセットを更新していく方法がいいと思いますよ。

Private Sub コンボ351_AfterUpdate()
    With Me.Recordset.Clone
        Do Until .EOF
            .Edit
            !suitei_tenko = Me!コンボ351
            .Update
            .MoveNext
        Loop
        .Close
    End With
    Me.Recalc
End Sub

更新クエリで、サブフォームに記述するコードなら、Me(サブフォーム自身) と Parent(親フォーム) を使えば、
ややこしい参照式を使う必要はないです。

SQL = "UPDATE public_stb_ra_time_shokin_5 INNER JOIN public_stb_ra_tpc_sabun " & _
      "ON public_stb_ra_time_shokin_5.race_id = public_stb_ra_tpc_sabun.race_id " & _
      "SET public_stb_ra_tpc_sabun.suitei_tenko = " & Me.コンボ351.Value & _
      "WHERE public_stb_ra_time_shokin_5.hizuke = " & Parent!開催日.Valu & "));"

追伸　それとフォームのレコードセットを更新する方法のほうがよいのかもしれないですが、そっちの理屈はまったくわけがわからないです。

早速返信していただきありがとうございます。恥ずかしいですけど、アップした画像のようなフォームになっています。親フォームがF_T0指数評価用で、サブフォームが埋め込み47またはF_TP指数です。（サブフォームを指定するときはどっちを書いたらいいものかいつも迷いますが、記憶では埋め込み47を書くのが正しいのではないか、と）。親フォームにあるテキストボックスの[開催日]を見て、テーブルの該当箇所にコンボボックスの選択値＝天気を入力しようと思ってます。が、できません。Formが見つかりません、というメッセージが出てきます。よろしくお願いします。

Private Sub コンボ351_AfterUpdate()
    Dim SQL As String
    SQL = "UPDATE public_stb_ra_time_shokin_5 INNER JOIN public_stb_ra_tpc_sabun ON public_stb_ra_time_shokin_5.race_id = public_stb_ra_tpc_sabun.race_id SET public_stb_ra_tpc_sabun.suitei_tenko = " & [Forms]![F_T0指数表示用]![埋め込み47]![Form]![コンボ351].[Value] & "WHERE (((public_stb_ra_time_shokin_5.hizuke) = " & [Forms]![F_T0指数表示用]![開催日].[Value] & "));"
    CurrentDb.Execute SQL
    Me.Refresh
End Sub

EXISTSを使った相関サブクエリは、基本的に遅いです。
相関(サブクエリからメインを参照する)を利用しない方法を検討しましょう。

下記でどうでしょう。

SELECT
 T1.社員No, T1.社員名, T1.所属
FROM
 Mtbl_所属歴 T1
 INNER JOIN
 (SELECT
   社員No, Min(開始日) AS Min開始日, Max(終了日) AS Max終了日
  FROM Mtbl_所属歴
  GROUP BY 社員No) Q1
 ON T1.社員No=Q1.社員No
WHERE
 T1.開始日>Min開始日 And T1.終了日<Max終了日;

これでもまだ遅いなら、
社員No、開始日、終了日 にインデックスを設定してください。

開始日の最小値と終了日の最大値以外の所属を抽出

[社員No]の値が同一であるグループごとに、ということでしょうか。

VBAでクエリを動的に生成し実行する場合、「クエリを実行する」という命令を記述する必要があります。

DoCmd.RunSQL メソッド (Access)

Database.Execute メソッド (DAO)

Private Sub コンボ353_AfterUpdate()

    Dim SQL As String

    SQL = _
        " UPDATE public_stb_ra_time_shokin_5 INNER JOIN public_stb_ra_tpc_sabun ON public_stb_ra_time_shokin_5.race_id = public_stb_ra_tpc_sabun.race_id SET public_stb_ra_tpc_sabun.suitei_tenko = " & [Me]![form]![コンボ_353].[value] & _
        " WHERE (((public_stb_ra_time_shokin_5.hizuke)=" & [Me]![開催日].[value] & "));"

    CurrentDb.Execute SQL 
    Me.Refresh
End Sub

※クエリ（SQL構文）そのものの正否は見ていません

「フォームに表示されている全データを更新したい」のであれば、フォームのレコードセットを更新していく方法もありだと思います

Private Sub コンボ353_AfterUpdate()
    With Me.Recordset.Clone
        Do Until .EOF
            Me!suitei_tenko = Me!コンボ_353
            .MoveNext
        Loop
        .Close
    End With
    Me.Refresh
End Sub

hatenaさん、ありがとうございます。
確かに何度も読込が繰り返されている様な
状態でしたので、仰っていることは
的を得ているかもしれません。
とても勉強になりました。
ありがとうございます。

書籍の作例は、同じような形式で真似るに留め、
経験を積まれたお二方のアドバイスに従い、
今回は連結で進行させた方が良さそうですね。

ありがとうございます。

りんごさんがピタゴラスイッチという表現をされてますが、そういう現象が起きているのでは？
要するに、連鎖反応で再読込が繰り返されているということではないかと。

内部動作は１ユーザーからは知る由もないので、あくまで推測ですが。

あるコントロールの値を参照して、さらにそれをサブフォームのリンクフィールドのプロパティから参照して、サブフォームデータにフィルターがかかる(再読込される)、それが複数のサブフォームで発生する、それが循環参照のような感じになっている・・・・

このような複雑な参照関係は、思わぬ動作の原因になるので、避けた方がいいというのが、私の経験則です。

りんごさん、ありがとうございます。

ご提案して頂いた方法は
既に出来上がっております。
確かに連結で同じように作ると、
質問にあるような現象は現れません。

今回質問に至った経緯は、書籍に掲載されている
メインに対して一つのサブがある作例を参考に、
サブを三つに増やした結果、現れた現象について、
考えられる原因を探りたくなったからです。

コントロールソース：=[Forms]![F_設定一覧]![sbf_設定一覧].[Form]![fld_口座番号]

ピタゴラスイッチをやめて、メインフォーム連結レコードソースをきちんと設定してメインサブを組めばいいんじゃない？

hatenaさん、ありがとうございます。
点滅は、その状態が維持されます。戻りません。
テーブルはローカルです。
宜しくお願いします。

「マウスポインタが砂時計になって点滅する」というのは、ずっとその状態が続いているのか、しばらくしたら通常の状態になるか、どちらでしょう。
また、しばらくしたら通常に戻る場合は、どのくらいの時間で戻りますか。

また、テーブルはローカルテーブルですか、それともネットワーク上のデータベースからのリンクテーブルですか。

どうにか動くようになりました。でも理由はわかりません。お騒がせしました

なるほど、このような機能があるのですね。

サブデータシートをうっかりさわってレコードを変更してしまわないように非表示にしておきます。

サブデータシートですね。
リレーションシップを設定すると自動で設定されます。
詳細は下記を参照。

サブデータシートはテーブルプロパティで手動設定できる | Access 2019 | 初心者のためのOffice講座

表示させたくなければ、テーブルプロパティで非表示に設定できます。これも上記のリンク先で説明されてます。

お世話になります。
リレーションシップを設定したところ、マスターテーブルの下に＋が表示されるようになり、クリックすると別のテーブルのレコードがでてくるのですが、これは問題ないでしょうか？

ロジックおよび動作確認できました。
当初はDAOによるキーブレイク処理を試みましたが、
最大、最小の日付を取ろうとして、何度も書いてもダメでした。

hatenaさまのロジックは、2段階になっていて、
なるほどと思いました。

どうもありがとうございました。

hatenaさま

ありがとうございます。
私もレコードセットで回してキーブレイク処理で、
判定フラグを立てる方法を試行錯誤していたのですが、
どうしてもできずにいたところでした。

まずは、じっくり内容確認させていただきます。

SQLもいいのですが複雑になるので、私がするなら、メンテナンス性とパフォーマンスを考慮して、
テーブルに在籍期間判定用のフィールドを追加して、
VBA(DAO)でそこに在籍期間ごとの連番を入力していくようにするかな。

Public Sub SetRenban()
    Dim rs As DAO.Recordset
    Dim c As Long
    Dim pre氏名, pre組織名
    Dim strSQL As String
 
    strSQL = "SELECT 在籍期間No, 氏名, 開始日, 終了日, 組織名 FROM アクションテーブル " & _
             "ORDER BY 氏名, 開始日, 終了日;"
 
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbFailOnError)
 
    Do Until rs.EOF
        If pre氏名 = rs!氏名 Then
           If pre組織名 <> rs!組織名 Then
                c = c + 1
                pre組織名 = rs!組織名
           End If
        Else
            c = 1
            pre氏名 = rs!氏名
            pre組織名 = rs!組織名
        End If
 
        rs.Edit
        rs(0) = c
        rs.Update
        rs.MoveNext
    Loop
 
    rs.Close
    Set rs = Nothing
 
    MsgBox "完了"
End Sub

上記の実行結果

SQL

SELECT t.氏名, t.組織名, Min(t.開始日) AS 開始日, Max(t.終了日) AS 終了日
FROM アクションテーブル As t
GROUP BY t.氏名, t.組織名, t.在籍期間No
ORDER BY t.氏名, t.在籍期間No;

SQL出力結果

終了日と次のレコードの開始日の連続性は考慮していないので、もし、連続しない(例えば休職とか)場合があるなら、
VBAにその条件判断を追加する必要はあります。

mayuさま

ありがとうございます。
これすごいですね。

SQLでここまできるとは。
結果セットも3万レコードくらいできるのですが、パフォーマンスも高いです。

ありがとうございます、作成を進めてみます！
Hatenaさん、Hirotonさん、いつも的確なアドバイス・回答をありがとうございます。

組織の在籍期間を算定しようと思っています。
⇒赤色でハイライトした箇所が正しく拾えず、黄色の箇所が結果として合っている
⇒出戻りの、更に、出戻りもある可能性もあります

方法は色々とありますけど、SQLのサンプルを載せておきます

■アクションテーブル

　
■SQL

SELECT 氏名
     , 組織名
     , 開始日
     , Min( q.終了日 ) As 終了日
FROM
(
    SELECT x.氏名
         , x.組織名
         , x.開始日
         , y.終了日
    FROM アクションテーブル x
    INNER JOIN ( アクションテーブル y
                 INNER JOIN アクションテーブル z
                         ON y.氏名 = z.氏名 and y.組織名 = z.組織名
    )
    ON x.氏名 = y.氏名 AND
       x.組織名 = y.組織名
    WHERE x.終了日 <= y.終了日
    GROUP BY x.氏名
           , x.組織名
           , x.開始日
           , y.終了日
    HAVING Sum(
               IIf( 
                       ( z.開始日 <  x.開始日
                            AND 
                         x.開始日 <= DateAdd( 'd', 1, z.終了日 )
                       )
                   OR
                       ( y.終了日 <  z.終了日
                           AND 
                         z.開始日 <= DateAdd( 'd', 1, y.終了日 )
                       )
                 , 1
                 , 0
               )
           ) = 0
) q
GROUP BY 氏名
       , 組織名
       , 開始日
ORDER BY 1, 3 ;

　
■結果

画像を見た限りでは特に問題はなさそうです。

氏名　　　　　　　開始日　　　　　　終了日　　　　　組織名　判定フラグ
青山 太郎 20140401 20140525 人事 0
青山 太郎 20140526 20140531 人事 1
青山 太郎 20140601 20150331 人事 1
青山 太郎 20150401 20170930 経営企画 0
青山 太郎 20171001 20210331 法務 0
青山 太郎 20210401 20220331 人事 0
青山 太郎 20220401 20230331 総務 0
青山 太郎 20230401 99991231 総務 1

失礼いたしました、転記ミスです。
他の条件も検討する必要があるとのことですね。

想定しているデータは>> 3の通りですが、補足しているようにデータによっては他の条件を検討する必要がでます

また、終了日を含めて一つの結果にするにはそれなりのクエリ（計算）が必要になります
質問中には「総務」のレコードは2件しかありませんので、途中結果であれ「総務」が3レコードになるのはどこかに間違いがあるのでしょう
hirotonの提案は1発で開始日、終了日両方合わせたような結果が出るようなものではないので、元データと、どのようなクエリを組んだのか確認してみてください

hatenaさま
ありがとうございます。

こちらで正しいです。アクションが空白のデータもありますし、
アクション違いで、複数の同一組織、氏名のレコードは存在します。
職種変更  　20171001   20171001    20210331    法務
      　　　20200801   20171001    20210331    法務

おっしゃる通り、こちらが正解です。
氏名    　開始日  　　　終了日      　組織名
青山 太郎　20140401  20151031　　　　人事
青山 太郎　20151101　20170930 　　　経営企画
青山 太郎　20171001　20210331 　　　法務
青山 太郎  20210401　20220331　 　　人事
青山 太郎　20220401  99991231  　　　総務

hiroton さま
ありがとうございます。
なるほどです。こんな考え方があるのですね。

1点だけ不備がありまして、赤のところが0,1逆になりました。

氏名　　　　　　　開始日　　　　　　終了日　　　　　組織名　判定フラグ
青山 太郎 20140401 20140525 人事 0
青山 太郎 20140526 20140531 人事 1
青山 太郎 20140601 20150331 人事 1
青山 太郎 20150401 20170930 経営企画 0
青山 太郎 20171001 20210331 法務 0
青山 太郎 20210401 20220331 総務 0
青山 太郎 20220401 20230331 総務 0
青山 太郎 20230401 99991231 総務 1

職種変更  　20171001   20171001    20210331    法務
      　　　20200801   20171001    20210331    法務

上記の部分は、下記のものの転記間違いでしょうか。

職種変更  　20171001   20171001    20200731    法務
      　　　20200801   20200801    20210331    法務

あと、
開始日      終了日       組織名
20140401　　20140525    人事
20140526    20140531    人事

上記のように、終了日と次のレコードの開始日が20140525→20140526 というように連続していたら、一つの在籍期間として扱うということでしょうか。

クエリの最終出力フォーマットは、下記のような感じにですか。

氏名    　開始日  　　　終了日      　組織名
青山 太郎　20140401  20151031　　　　人事
青山 太郎　20151101　20170930 　　　経営企画
青山 太郎　20171001　20210331 　　　法務
青山 太郎  20210401　20220331　 　　人事
青山 太郎　20220401  99991231  　　　総務

それとも質問のように、空欄も必要なのですか。

日付が連続するとしているならば、各組織での開始日を、不要なレコードを削除することによって求めることができます。各レコードに於いて、「『開始日』の前日が終了日であるレコード」の組織が同じ組織であれば、そのレコードは連続した組織のデータであり、開始日として不要なデータとなります

そのようなデータであるかどうかを求める場合

DCount("組織","T_アクション","組織='" & [組織] & "' AND 終了日='" & Format(DateValue(Format([開始日],"@@@@\/@@\/@@"))-1,"yyyymmdd") & "'")

このような計算をすると、各組織の開始日に当たるレコードだけ「0」の結果が得られます

※質問中のデータの場合、6行目（アクションが空白のレコード）も抽出されます

「開始日だけ」のデータが出来上がれば、「次のレコードの開始日の前日」が「終了日」です

ありがとうございました。

その機能のことを、すっかり忘れていました。

こちらで解決できるかしら？
Accessのクエリで結合した2つのテーブルのうち一方にしかないデータを抽出する方法

普通そう思いますよね。
基幹システムが全く機能していなくて、所属歴が取れないので苦肉の策です。

基幹人事システムならば、社員所属テーブルがあるはずですが？何故、クエリで捻り出さないといけないの？

心理状態（エントリー時）、心理状態（クローズ時）も同じマスターテーブル（M_心理状態）から値を拾うのですが、エントリー根拠のように別テーブルにする必要はありますか？

データが同じなら、別テーブルにする必要はないです。
リレーションシップを設定するときに、マスターテーブル（M_心理状態）を２つ追加してそれぞれと結合されせればいいでしょう。

T_トレード履歴_エントリー根拠・T_トレード履歴_クローズ根拠のテーブル作成、リレーションシップの設定をしてみましたが、こんな感じでよいのでしょうか？
フォームの入力確定時に保存用テーブル（T_トレード履歴・T_トレード履歴_エントリー―根拠・T_トレード履歴_クローズ根拠）にレコードの書込みを行いたいので、tmpとしています。

M_●●はマスターテーブルです。

画像修正しました。

ありがとうございます。Hatenaさん、Hirotonさんに提案されている正規化をやってみたいと思います。

心理状態（エントリー時）、心理状態（クローズ時）も同じマスターテーブル（M_心理状態）から値を拾うのですが、エントリー根拠のように別テーブルにする必要はありますか？