テーブルデータの連番を1から振り直します。
注）オートナンバーのID(主キー)ではなく、
クエリで絞り込みした表に対して別の連番(カラム)に数値を1から順に振り直していきます。
大きなデータベースに対して各集合ごとの通し番号を設定したい場合に使用します。
■使用する初期設定テーブル
ID ＝ オートナンバー
連番 ＝ 1から順にIDと同じ数値
集合 ＝ 文字列"A"と"B"とする
レベル ＝ ランダムな数値とする
※今回使用するテーブルの内容
■上記テーブルに対して集合の値Aをクエリで抽出します
クエリで集合の値Aを抽出して、レベル列を昇順で並べ替えをした状態から、連番列を1から連番に振りなおします。
※テーブルに下記のような変更を加えます。
クエリのデザインビュー
クエリのデータシートビュー
連番振り直し後のクエリ結果・・・ID4の連番が3になります
連番振り直し後のテーブル状態
■VBAの記述内容
Private Sub renban()
Dim cnn As ADODB.Connection
Dim rst1 As New ADODB.Recordset
Set cnn = CurrentProject.Connection
Dim d As Long
Dim i As Long
i = 1
Dim syu As String
syu = "A"
Dim str As String
str = "SELECT * FROM テーブル WHERE 集合 = " & " '" & syu & "'" & " ORDER BY レベル"
rst1.Open str, cnn
Do Until rst1.EOF
d = rst1.Fields("ID").Value
DoCmd.RunSQL "UPDATE テーブル SET 連番 = " & i & " WHERE ID = " & d
i = i + 1
rst1.MoveNext
Loop
rst1.Close
cnn.Close
Set rst1 = Nothing
Set cnn = Nothing
End Sub
■VBA解説
'd ＝ 主キーIDの値を入れる変数
'i ＝ 連番の初期値1に設定
'syu ＝ 抽出する集合の値を入れる変数(ここではA)
Dim d As Long
Dim i As Long
i = 1
Dim syu As String
syu = "A"
'集合の値Aを抽出して、レベル列を昇順で並べ替えをする内容のSQLを作成
Dim str As String
str = "SELECT * FROM テーブル WHERE 集合 = " & " '" & syu & "'" & " ORDER BY レベル"
'抽出したテーブルセットをオープンして1行ずつ連番を振り直します。
'抽出したIDの値に対して連番を1から順に1ずつカウントアップして更新クエリでテーブルに変更を加えていきます。
rst1.Open str, cnn
Do Until rst1.EOF
d = rst1.Fields("ID").Value
DoCmd.RunSQL "UPDATE テーブル SET 連番 = " & i & " WHERE ID = " & d
i = i + 1
rst1.MoveNext
Loop
■ついでに集合Bを処理したらどうなるでしょうか
上記VBAの記述中のAをBに変えるだけです。
syu = "A"
↓
syu = "B"
■集合の値Bの連番振り直し結果
集合の値Bで抽出してレベル列を昇順で並べ替えしているので下記のような結果となります。
クエリのデザインビュー
クエリのデータシートビュー
最終的なテーブルの状態はこのようになります。
■最後に
上記ではレベル列で並べ替えしてますけど、連番列で昇順してから連番列の振り直しも出来たりします。
ここでは行数が少ないですが、例えば1000件あれば連番の数値を1から100に変えて処理したら連番の2を1にして振り直していく事になります。
番号の振り直しはいろんな場面で利用できます。まだまだ拙い記述ですが今回のVBAをご自身のシステムに改良、利用してより良いシステム作りに役立てて頂ければ幸いです。
