Accessレコードに同一キーコードのグループ内で連番を付与する方法2種まとめ(テーブル保存とクエリ生成)

【同一キーコードのグループ内】での連番とは？　例えば…

同一「ユーザーID」に対して、複数の履歴のレコードがテーブル上にあり
同じ「ユーザーID」のレコード内で、時系列順に連番を振りたい

そんな場合の、以下の２通りの連番付与の方法を解説しています

テーブルに書き込んで「連番」の値を保存する方法
クエリ上で一時的に「連番」を生成する(だけでレコードに保存はしない)方法

【同一キーコードのグループ内】での連番とは？　例えば…
そんな場合の、以下の２通りの連番付与の方法を解説しています

＜その１＞連番をテーブルに保存したい場合
＜その2＞連番をクエリ上で一時的に付与出来ればOKな場合

＜その１＞連番をテーブルに保存したい場合

左記のような「T_購入履歴」テーブルを例に、VBAを使用して作業用のクエリをレコードセットで開き、1レコードずつ連番を書き込んで行く方法を説明していきます。

手順 ①テーブルに連番記入用のフィールドを追加する

テーブルに連番記入用のフィールドを追加する
数値型の長整数型でOK
「規定値」の「0」は消しています

手順 ②レコードセットで操作するためのクエリを作成する

最低限必要となるフィールドは以下の３つになります
①キーコード(例えば「ユーザーID」)
②並び順をコントロールできる要素のフィールド(例えば時系列でソートするなら日付/時刻型の項目「購入日付」)
③そして「連番」を記入していくフィールド

手順 ③クエリにソートを設定して上書き保存する

クエリに「キーコード」→「並び順コントロール用フィールド」の順番でソートをかけ、保存する

手順 ④サンプルコードを標準モジュールへコピペして使ってね

Option Compare Database
Option Explicit

Sub 連番記入()

Dim DB As DAO.Database
Dim RS As DAO.Recordset

Dim UID As String
Dim SerialNo As Long

'念のための安全対策(作業用クエリ結果が0件なら終了)
If DCount("*", "Q_連番記入作業用") = 0 Then
    MsgBox "連番付与対象のデータが存在しません。", vbExclamation
    Exit Sub
End If

Set DB = CurrentDb

'連番の書込み更新を行うので、
'ダイナセット型のレコードセットで作業用クエリを開きます
Set RS = DB.OpenRecordset("Q_連番記入作業用", dbOpenDynaset)

    RS.MoveFirst
    
    '初期値をセット
    UID = RS.Fields("ユーザーID")　'キーコードのフィールドの値
    SerialNo = 1
    
    Do While RS.EOF = False
    
        'まずはカレントレコードへ連番を書き込む
        RS.Edit
        RS.Fields("連番") = SerialNo '連番記入用のフィールド
        RS.Update

        '次レコードへ移動し
        RS.MoveNext
                
        '結果レコードセットの末端(EOF)に到達ならループを抜ける
        If RS.EOF = True Then Exit Do
        
            'ユーザーIDが1つ前のレコードと同じなら
            If RS.Fields("ユーザーID") = UID Then
                '連番をカウントアップ
                SerialNo = SerialNo + 1
                
            'ユーザーIDが1つ前のレコードと異なるなら
            Else
                '新しいユーザーIDを変数へ格納
                UID = RS.Fields("ユーザーID")
                '連番を1へ巻き戻す
                SerialNo = 1
            End If
    Loop
    
    RS.Close
Set RS = Nothing
    DB.Close
Set DB = Nothing

MsgBox "連番の記入が完了しました。"

End Sub

＜その2＞連番をクエリ上で一時的に付与出来ればOKな場合

Dcount関数で頑張りましょう！
なお、Dcount関数方式の場合、同一キーコードのグループ内で並び順コントロールに使用するフィールドは、等号・不等号で判定できるタイプの値(数値型や日付型)である必要があります。
“”(ダブルコーテーション)と、クエリのフィールド表記に使う[]かっこの使い分けなど、引数の書き方が色々ややこしくて、正直私も苦手なんですが、うまくいくと気持ちいいよ！
ただし、レコード数が膨大な場合、レコード1件１件に対して集計を行うこの方式は重くなってしまうので、テーブル書き込み方式がオススメです。

ここでも左図のような「T_購入履歴」テーブルを例に、「ユーザーID」ごとに、「購入日付」の古いレコードから順に、連番を付与するケースで説明していきます。
新しいクエリを作成し、「T_購入履歴」テーブルを追加して、「連番」フィールドをDCount関数式で作成します。

DCount関数のおさらい

DCount関数には、3つの引数があり、
DCount(“①該当レコードを数えるフィールド名“,”②レコードを数える対象のテーブル名“,”③抽出条件“)
となります。また、Dcount関数の引数は、全て“”(ダブルコーテーション)で囲って表記するのがルールですよね。

“①レコード数を数えるフィールド名“は、特に指定しなくても問題ない場合は「“*”」でOK。今回のケースでは「“ユーザーID”」でも大丈夫ですね。
“②レコードを数える対象のテーブル名“は、この例だと「“T_購入履歴”」となります。

そしてキモとなる”③抽出条件“の書き方はこうなります

"ユーザーID = '" & [ユーザーID] & "' AND 購入日付 <= #" & [購入日付] & "#"

関数式全体はこうなります＆長い解説(私見)

連番: DCount("ユーザーID","T_購入履歴","ユーザーID = '" & [ユーザーID] & "' AND 購入日付 <= #" & [購入日付] & "#")

何故“③抽出条件“はこのような“”(ダブルコーテーション)やら「＆」やら、[]かっこの入り混じった式になるのか、私個人の解釈で説明すると…

③抽出条件冒頭の「“ユーザーID = ‘”」は、Dcount関数本来の書き方である、条件を設定するフィールド名に[ ]はつけずに””(ダブルコーテーション)内にそのまま書くというルールで記述がされている。
- 例えば「ユーザーIDが特定の値である」というケースだと「“ユーザーID = ‘A00001’“」と書く場合と同じ要領です。
- そしてこの“”(ダブルコーテーション)囲いの中に記述されている“ユーザーID”フィールドは、「“②レコードを数える対象のテーブル名“」の引数で指定されたテーブル上にあるフィールドを示している。
一方で[]かっこで囲われた[ユーザーID]の方は、【このDCount関数式があるクエリの中に存在している[ユーザーID]フィールドの値】を意味している
- 同じ名前のフィールドだけど、1.の“”(ダブルコーテーション)に囲われた“ユーザーID”とはある意味世界線が違うんですね。
- そして“”(ダブルコーテーション)の囲いの外にあるので、扱いは変数と同じになっている。(「‘” &」と「& “‘」に挟まれる必要がある。また日付型なら「#」も付ける必要がある。)
“購入日付”と[購入日付]の違いも同様で、“”(ダブルコーテーション)囲いはDCount関数本来の”②の引数テーブル上の世界“のフィールドで、[]かっこ囲いは、このクエリ内部の世界を示すものと考えてみてください。
そして日本語訳を考える時、主人公(関数式の主語)になるのは、[]かっこ囲いの、このクエリ内部の世界にいるレコードさんの方です。

以上を踏まえて、DCount関数式を日本語訳してみると

連番: DCount("ユーザーID","T_購入履歴","ユーザーID = '" & [ユーザーID] & "' AND 購入日付 <= #" & [購入日付] & "#")

＜日本語訳＞
"T_購入履歴"テーブル上において、
"ユーザーID"の値が、私自身(←クエリ内レコードの擬人化)の[ユーザーID]フィールドの値と等しく、
かつ、
"購入日付"の値が、私自身の[購入日付]フィールドの値以下のレコードが、
何件あるかをカウントし、「連番」フィールドに表示せよ

結果を見てみよう！

ユーザーID「A00001」の４レコードで検証してみると…
一番新しい(＝シリアル値的には大きい)日付「2021/03/24」以下のレコードは４件あるので、連番は「4」
一番古い(＝シリアル値的には小さい)日付「2021/01/10」以下の日付は、自分自身の１個だけなので、連番は「1」
「2021/02/12」以下の日付は３個あるので、連番は「3」

う～ん、まだまだ分かりにくいかな。折を見てまたブラッシュアップしていきます。こういう式をスラスラ読める人に私はなりたい。「主語のレコードはクエリ側」というのを、最初逆に勘違いしていて、この記事を書いている途中で気づきました。不等号ってややこしい。

【同一キーコードのグループ内】での連番とは？ 例えば…