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段階になっていて、
なるほどと思いました。

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

こちらのスレッドでアドレスいただいた者です。

ベースになるデータの仕様が少し変わりまして、開始日・終了日を持たず、
発令日（=開始日）のみしか持たないデータソースを使うこととなりました。

SetRenban関数にて在籍期間Noをふり、開始日および終了日はSQLで在籍期間NoのMin、Max
にて計算するというものを実装しています。

今回、ご相談させていただきたいのは、開始日は在籍期間NoのMinで良いのですが、
終了日が、理論上は[在籍期間No] + 1のMinの前日となると思っていますが、
私ができる初歩的なレベルですと、もうひとつクエリを作って、両者を結合させるとかになります。

このあたりの実装でご相談させてください。

新しい質問を立てるのはどうでしょう？
主キーと関連するテーブルを過不足なく提示したり、スクショを提示したりするのがおすすめです。

今回、ご相談させていただきたいのは、開始日は在籍期間NoのMinで良いのですが、
終了日が、理論上は[在籍期間No] + 1のMinの前日となると思っていますが、
私ができる初歩的なレベルですと、もうひとつクエリを作って、両者を結合させるとかになります。

アクションテーブルに失効日のフィールドを追加しておいて、SetRenban関数のループ内で発行日の前日を前レコードの失効日に代入するという処理を追加すればどうでしょう。

Public Sub SetRenban()
    Dim rs As DAO.Recordset
    Dim c As Long, d 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
            d = Format(DateAdd("d", -1, Format(rs!発令日, "0000/00/00")), "yyyymmdd")
            rs.MovePrevious
            rs.Edit
            rs!失効日 = d
            rs.Update
            rs.MoveNext
        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

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

Mayuさま
どうもありがとうございます。
SetSequenceNumber関数、これは凄いものですね！
今後、使えるようにしておきたいです。

私がやったものとは格段に改善しました。

Hatenaさま
ありがとうございます。
>アクションテーブルに失効日のフィールドを追加しておいて、SetRenban関数のループ内で発行日の前日を前レコードの失効>日に代入するという処理を追加すればどうでしょう。

movePreviousとかで一旦戻って、Editすることができるんですね。
正直知りませんでした。今回、こちらのほうほうで完璧にできました。

今回、組織コードというおおくくりの組織の配下に、部門、部署というレベルがありまして、
表示する際は、氏名と開始日をもとに、所属歴を参照しては以下組織を持ってきていたのですが、
同じ在籍期間Noでの期間が長いと組織名称が変わっていたりして、終了時点の組織名称を参照する
方法に苦心していましたが、グループ内採番の日付Max値のデータを保持するクエリを作成して、
これを参照することで、Mayuさまのロジックを使わせていただき、なんとかこちらも対応できました。

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

対象組織の在籍期間Noごとに、役職・職種を持っているのですが、
単純に最新の役職を持つクエリ、最新の職種を持つクエリを作成
（それぞれのクエリで、在籍期間Noでグループ化して、役職・職種を先頭（最後）で抽出）
したものを、こちらのクエリから参照する方法もありますね（全くスマートではないですが）。

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