Microsoft Access 掲示板

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

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

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

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

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

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

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

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

職種変更  　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  　　　総務

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

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

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

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

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

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

方法は色々とありますけど、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 ;

　
■結果

mayuさま

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

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

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にその条件判断を追加する必要はあります。

hatenaさま

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

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

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

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

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