ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.016

クエリで連番、グループ連番をふる

2003/2007/2010/2013

連番といえばオートナンバー型ですが、オートナンバーを使用せずに連番を作成したいということがあります。
今回は、クエリでDcount関数を使用した連番の作成です。

まずは、次のようなテーブル[テーブル1]があるとします。

テーブル1

フィールド[F1]、[F2]はテキスト型、[F3]は数値型、[F4]は日付/時刻型です。
日付データは交互に同じです。

<全レコードの連番>

まず、全レコードに連番をふります。
まとめてご紹介しますので、上図のテーブルの元データと連番の結果をよくご確認ください。

全データ連番

では、それぞれの使用した式です。

  F1昇順: DCount("*","テーブル1","[F1] <='" & [テーブル1]![F1] & "'")

  F2降順: DCount("*","テーブル1","[F2] >='" & [テーブル1]![F2] & "'")

  F3昇順: DCount("*","テーブル1","[F3] <=" & [テーブル1]![F3])

  F4降順: DCount("*","テーブル1","[F4] >=#" & [テーブル1]![F4] & "#")

まず、D~始まる定義域集合関数の最初の引数は、集計するフィールドを指定しますが、Dcount関数では該当するレコードの数を数えられればよいので、特定のフィールドでなく任意のフィールド「"*"」で構いません。
2つ目の引数は、データがあるテーブル名またはクエリ名を指定します。

Dcount関数で、ややこしいのが最後の引数、条件の指定です。
以下、1つ1つの式でのポイントをご案内します。

[F1昇順]では、連番をふるための基準とするフィールドを[F1]フィールド(テキスト型)にしています。
条件を設定する最後の引数の式は文字列式で設定しますが、文字列式の文字列条件の前後に「'」を挟んでいます。

[F2降順]では、[F1昇順]のときと大なり小なりの記号の向きが違うことと、同じデータがある場合、同じ番号(順位の最大値)という結果となります。
順位の最小値と同じ番号としたい場合は、式中の「>=」などの「=」を抜き、「+1」するとうまくいきます。

  F2降順: DCount("*","テーブル1","[F2] >'" & [テーブル1]![F2] & "'")+1

[F3昇順]は一番単純に書ける式です。連番をふる基準のフィールドが数値型であるため、クォーテーションは必要ありません。

[F4降順]では、連番をふる基準のフィールドが日付/時刻型です。条件設定で、日付の前後には「#」が必要です。[F1昇順]の「'」の位置が「#」となっています。

<グループ連番>

グループごとの連番をふりたい場合です。
昇順降順の条件のほかに、グループ化する条件を加えます。

グループ連番

使用した式は、次のとおりです。

  F2グループF1降順: DCount("*","テーブル1","[F1] >='" & [テーブル1]![F1] & "' And [F2]='" & [テーブル1]![F2] & "'")

  F2F3グループF1昇順: DCount("*","テーブル1","[F1] <='" & [テーブル1]![F1] & "' And [F2]='" & [テーブル1]![F2] & "' And [F3]=" & [テーブル1]![F3])

[F2グループF1降順]では、[F2]フィールドでグループ化しています。[F2]フィールドで同じデータという条件を加えます。

[F2F3グループF1昇順]では、2つのフィールドをグループ化した連番です。

Dcount関数については、ヘルプ「条件に一致するレコードの件数を求める(ExcelならCOUNTIF関数のような) 《Dcount》」をご参照ください。

twitter hatena line pocket

関連ヘルプ

クエリの演算フィールドについて、式ビルダーの使い方
クロス集計クエリの結果のNull値を「0」と表示する
重複のない一通りデータを取得したい
テーブルのデータ型をクエリで変更する 《データ定義クエリ》
外部結合とは
削除クエリで、別のテーブルまたはクエリと共通するレコードを一括削除する
複数テーブルをつなげてレコードを結合する 《ユニオン クエリ》
クエリで空白(でない)データを抽出したい、Accessの空白について
重複クエリとは
部分一致のパラメーター クエリを作成する、パラメーター クエリの設定方法
更新クエリでテーブルのデータを置換する(文字列の一部更新)
クロス集計クエリを元にパラメーター クエリを実行「~認識できません」エラー
特定のレコードのみ最初に並べ、他のレコードは普通に並べ替えする
基本的な抽出条件の書き方について
ワイルドカードや数式を使用した高度な抽出
アクション クエリのメッセージを非表示にするには