MS Access Tips/Sample and VBA and Blog customize etc...

グループ毎連番を自動入力する関数

クエリで連番を表示する場合、DCount関数やサブクエリを利用する方法はあちこちで紹介されています。

しかし、この方法は自分より前のレコード件数をカウントするというロジックなのでレコード件数が多くなると幾何級数的に重くなります。また、グループ毎に連番を振るという仕様になると、条件式も複雑になってきます。

クエリは使わずにテーブルに連番フィールドを持たせて、そこに VBA で連番を書きこむようにすると高速に処理できます。

掲示板でもよくある質問で、これまでに何度もVBAコードを回答してきましたが、パターンは同じなので汎用関数にしてみました。

SetSequenceNumber関数

難易度:

仕様

下記のようなテーブルがあるとします。

Tbl1
フィールド名データ型
IDオートナンバー型
Group1数値型
Group2数値型
Data1テキスト型

Group1、Group2 でグループ化して、そのグループ内でData1の昇順で連番を振る。Data1には重複があるのでその場合は、IDの昇順で連番を振る。

クエリで DCount または サブクエリで

これをクエリで表示するには、下記のような演算フィールドを追加します。

出力結果
クエリ結果

結構、複雑な式になりますし、レコード件数が多くなると重くなるという二重苦です。
サブクエリにすれば処理速度は少しは改善されますがわずかです。

どちらも条件式内のフィールドにインデックスを設定すれば少しは改善されますがやはり重い処理には変わりありません。

VBAで

Tbl1 に下記のフィールドを追加します。

Tbl1
フィールド名データ型
SequenceNum数値型

標準モジュールに下記の関数を作成して実行します。

実行結果
関数実行結果

この方法だとロジック的に件数と処理速度は比例の関係になりますので、クエリの等比級数の関係に比べて、
レコード件数が多いときは大幅に処理速度が改善されます。

フィールドが余分に一つ必要というデメリットがありますが、速度改善のメリットは大きいと思います。
また、テーブルのデータ変更、追加があったときのみに実行すればいいというのもメリットになります。

汎用関数

上記のVBAの方法はメリットは大きいですが、コードは結構長くなります。(掲示板の回答で何度このようなコードを書いてきたことか)

ただ、仕様が変わってもコードのパターンは一緒なので汎用関数にすれば後は簡単なコードですみます。

2015 02 24 追記: 上記関数にバグがありましたので修正しました。

2015 11 15 追記: 対象レコード数が多い時にでる共有ロック数エラーの対策用コードを追加しました。

順位ではなく連番を入力するという仕様なので、並び順フィールドに重複があった場合、同順位とせずに、連番になりますのでご注意ください。

使用例

上記のクエリと同じ仕様なら、下記のコードですみます。

どうでしょう。シンプルなコードで処理速度も速いという一粒で二度おいしい仕様です。

特定のグループのみ更新したという場合は抽出条件を設定すれば特定のグループのみ更新することも可能です。

GroupBy引数、WhereConditionを省略すると、全レコードを通しての連番になります。
下記は、Data1 の降順に連番を振ります。DATA1が重複する場合はIDの昇順になります。


追記2015 02 15: 累計値を自動入力する関数も作成しました。

累計値をテーブルに自動入力する関数 - hatena chips

サンプルファイルが下記からダウンロードできます。
VBASetSequenceNumber_07.zip (Access 2007-2010 形式 - 26kb)
VBASetSequenceNumber.zip (Access 2002-2003 形式 - 21kb)
VBASetSequenceNumber_2k.zip (Access 2000 形式 - 21kb)

拍手する

1 Comments

kt says..."連番セット汎用関数、すばらしい!"

Accessで連番をうまく取得できず悩んでいました。非常に助かりました。VBAはExcelの基礎程度しかわからないのですが、Accessのほうも勉強していきたいと思います。

2014.12.11 13:39 | URL | #KOupoYgU [edit]

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/428-8cd4e530
該当の記事は見つかりませんでした。