Accessクエリで数値型の連番を一部書き換えた際、再度そこから下の行の連番を再付与したい

Question

いつもお世話になっております。

現在、"T_従業員マスタ"テーブルをAccessにて作成しておりまして
表示順を定める一意の連番フィールド"整理番号"（主キーではない）を、数値型で作成しております。

オートナンバー型ではなく、数値型で連番を付与するのは
NZ(DMAX("整理番号","T_従業員マスタ")+1,1)で可能かと思いますが
たとえば、100番まで整理番号があったとして
50番目の従業員を25番目に変更したときと、
75番目に変更したときの両方に対応できるよう、
従業員の整理番号を再付与する方法をご教示いただけますと幸いです。

イメージとしましては
・50番⇒75番の場合は、50番から100番までを再度振り直す
・50番⇒25番の場合は、25番から100番までを再度振り直す
※キリ番は動的に変化する

対象となる従業員の変更前と変更後の整理番号は
フォーム"F_従業員情報"で既に取得できるようにしておりますものの、
そのあとの処理が思い浮かばず、こちらにご質問させていただきました。

そのフォームでの取得方法は
コンボボックスの値集合ソースに、"T_従業員マスタ"を設定して
従業員氏名・整理番号を追加し、
AfterUpdate時にColumn(1)を指定して、変更前の整理番号をテキストボックスAに反映させています。
変更後の整理番号は、テキストボックスBにユーザーが手入力するようにしています。

更新クエリの実行には以下SQL文を使用しています。

strSQL = "UPDATE T_従業員マスタ" _
       & " SET T_従業員マスタ.整理番号 = [forms]![F_従業員情報]![テキストボックスB" _
       & " WHERE (((T_従業員マスタ.整理番号)=[forms]![F_従業員情報]![テキストボックスA]));"

ここから、連番の再付与を行う更新クエリを実行する流れになるかと思います。

他に不足情報等ありましたら、お申し付けください。
どうかお力添えの程、よろしくお願い申し上げます。

Answer 1

単純に並び順の変更するだけなら、最初の番号を100番おきとかに振っておいて、空きを作っておき振替を不要とする方法も考えられます。

ですが、T_従業員マスタの従業員の並び替えを行うとしたら、役職だとか同じ役職なら入社年月日順とかのルールに基づくものだと思われるので、そういった情報での並び替えの方が簡単かもしれませんね。

Comments

[tailtera22]

sazi様

いつもお世話になります。

おっしゃる通り、通常は社員番号や役職で順序を決めておりますものの
この整理番号を使用せざるを得ないフォーマット(社員食堂の帳簿関連)がありまして
それ用に今回、独自の列順が必要になりました。

貴重なアドバイス、感謝申し上げます。

Answer 2

考えかたとしては、下記でいいかと思います。

50番の従業員を25番に変更したとき(更新前＞更新後)
50番をNullに更新
25番から49番までを1加算する。
Nullを25番に更新

50番の従業員を75番に変更したとき(更新前＜更新後)
50番をNullに更新
51番から75番までを1減算する。
Nullを75番に更新

「F_従業員情報」フォームのモジュール

`「F_従業員情報」フォームのモジュール
Public Sub test()
    Dim before As Long, after As Long
    before = Me.テキストボックスA
    after = Me.テキストボックスB

    Dim strSQL As String

    strSQL = "UPDATE T_従業員マスタ" _
             & " SET T_従業員マスタ.整理番号 = Null" _
             & " WHERE T_従業員マスタ.整理番号=" & before & ";"
    CurrentDb.Execute strSQL, dbFailOnError

    If before > after Then
        strSQL = "UPDATE T_従業員マスタ" _
                 & " SET 整理番号 = 整理番号 + 1" _
                 & " WHERE 整理番号 Between " & after & " And " & before - 1
    Else
        strSQL = "UPDATE T_従業員マスタ" _
                 & " SET 整理番号 = 整理番号 - 1" _
                 & " WHERE 整理番号 Between " & before + 1 & " And " & after
    End If
    CurrentDb.Execute strSQL, dbFailOnError

    strSQL = "UPDATE T_従業員マスタ" _
             & " SET T_従業員マスタ.整理番号 =" & after _
             & " WHERE T_従業員マスタ.整理番号 Is Null;"
    CurrentDb.Execute strSQL, dbFailOnError

End Sub

Comments

[tailtera22]

hatena19様

１００％的を射た素晴らしいご回答をご提供いただきましてありがとうございます。
ご提示いただきましたコードにより、本件は解決いたしました。

Betweenを使うところまでは何とか辿り着いたのですが
いただいたコードを拝見する限り、私の頭では到底たどり着けないような内容でしたので
ご質問させていただいて、本当に正解でした。

それから、SQL文をテーブルに反映させる１行のコード
CurrentDb.Execute strSQL, dbFailOnError
これには脱帽でした。

私は普段、以下のように記述しています。
　 Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
qdf.Execute

二重の収獲があり、大変感謝申し上げます。
また機会がありましたら、何卒よろしくお願い申し上げます。