Access クエリー DCount関数で順位付け

メルマガ発行内容

＜Access クエリー DCount関数で順位付け＞
どうも、三流プログラマーのＫｅｎ３です。

今回は、
Accessのクエリー演算フィールドで、
DCount関数を使って順位を付けて遊んでみたいと思います。

サンプルファイルは、
http://www.ken3.org/vba/lzh/vba088.lzh
にdb088.mdb(Access2000版)が保存されています。
＊Access97のマシーン熱暴走気味で不安定なので、
  Access2000になってます。

/*
 * １．今回のキッカケ
*/

メールでもらった下記の質問、
---------------
In message "アクセスVBAでwindowの閉じ方、グループでのデータ取得法...",
 >あとひとつは、やはりアクセスで、
 >グループ処理のとき、次のデータから
 >
 >　　　　fld1　fld2　fld3
 >　　 　　あ　　2　　　a
 >　　　　 あ　　1　　　b
 >　　　 　あ　　4　　　c
 >
 >「あ」でグループ化し、fld2から　最小値　1をえらび
 >　fld3　については、bを表示させたいのですが、
 >（fld2に属するfldsのデータを選ぶ)
 >
 >どうすればよろしいのですか。
---------------

そんな質問に対して、
No.87 Access DLookup関数を使ってみた
http://www.ken3.org/backno/backno_vba18.html#87
で、クエリーとDLookup関数を使って、処理してみました。

今回は、DCount関数を使って、少しイタズラしてみたいと思います。

/*
 * ２．クエリーでレコードに順位を付ける
*/

No.85 Access クエリーで演算フィールドを使ってみる
http://www.ken3.org/backno/backno_vba18.html#85
で、
クエリーでIIfとDateAdd関数などの関数を使用することができ、
これを演算フィールドと解説してました。

いろいろとＷｅｂを演算フィールドをキーワードに散歩していると
クエリーの演算フィールドに
順位: DCount("fld2","TEST_TABLE","fld2 >" & [fld2])+1
と
順位を付けるサンプルが転がっていた。

元データ
^^^^^^^^
ID	fld1	fld2	fld3
1	あ	2	a
2	あ	1	b
3	あ	4	c
4	い	5	a
5	い	6	b
6	い	4	c
7	い	10	d

に対して、さっそく使ってみると、
順位: DCount("fld2","TEST_TABLE","fld2 >" & [fld2])+1
をクエリーの項目として指定。

fld1	fld2	fld3	順位
あ	2	a	6
あ	1	b	7
あ	4	c	4
い	5	a	3
い	6	b	2
い	4	c	4
い	10	d	1

ははは、これだとたんにfld2の大きさで順位付けてるなぁ。
それに大きい順ジャン

まぁ、速度は別にして面白い方法だよね。
Dcountで自分より大きい該当レコードがいくつあるか？
を
"fld2 >" & [fld2]
を条件にして検索。
で＋１がポイントで、一番大きい値の時、
DCount("fld2","TEST_TABLE","fld2 >" & [fld2])
は０を返すから＋１して１位〜はじめてるのね。

DCount("項目","テーブルorクエリ","条件")
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
これをうまく使っているよね。
[]で項目を囲って、クエリー内で使っているのがポイントなぁ。

/*
 * ３．グループ別に順位を付ける
*/

順位付け、まぁなんとなくわかったけど、
何やりたいの？アナタは？
あっ、グループ別小さい順に順位をふって、頭の１位のデータだけ取りたくて

そうなんだぁ、大変だねぇ。
まずは、逆順（小さい順）、自分以下の値のデータが在るかカウントするか。
あっ、符号を逆にしてみるか。

順位: DCount("fld2","TEST_TABLE","fld2 <" & [fld2])+1
fld1    fld2    fld3    順位
あ      2       a       2
あ      1       b       1
あ      4       c       3
い      5       a       5
い      6       b       6
い      4       c       3
い      10      d       7

ここまでの小細工で、fldの小さい順に順位がふれたけど、
これでは意味無いんだよねまだ。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
えっ、なんで？
fld1='あ'別、fld1='い'別に順位を振りたいんだよねグループ別にAnd条件で。

アンタなに？言ってんの？And条件とか自分で言ってるジャン。
そのまま素直に書いたら？いつもヒネクレテるから素直なソース書けないの？

順位: DCount("fld2","TEST_TABLE","fld1 = '" & [fld1] & "' And fld2 <" & [fld2])
fld1	fld2	fld3	順位
あ	2	a	1
あ	1	b	0
あ	4	c	2
い	5	a	1
い	6	b	2
い	4	c	0
い	10	d	3
※+1今回からは止めました(０からの順位にしました)

あらら、素直に条件に、
fld1(が)= '" & [fld1] & "'"　フィールド１が同じで
 And
fld2(が) <" & [fld2])  フィールド２が自分より小さい
そんなレコードを数えて順位としてみました。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

/*
 * ４．先頭レコードに絞りたいので、順位=0にしてみた。
*/

で、レコードの折りたたみじゃないけど、
順位が０の先頭レコードだけ取り出したいので、
クエリーの条件に０を入れてみます。

fld1	fld2	fld3	順位
あ	1	b	0
い	4	c	0
と、先頭を取り出すことが出来ました。


↑抽出条件に順位０を指定

SQLビュー、、長い
SELECT TEST_TABLE.fld1, TEST_TABLE.fld2, TEST_TABLE.fld3, 
DCount("fld2","TEST_TABLE","fld1 = '" & [fld1] & "' And fld2 <" & [fld2]) AS 順位
FROM TEST_TABLE
WHERE (((DCount("fld2","TEST_TABLE","fld1 = '" & [fld1] & "' And fld2 <" & [fld2]))=0));

これでQ_CHKって、
グループ別のfld2が一番小さなレコードを取り出す
クエリーが無事に出来ました。
※順位付けしなくても、グループ化のクエリー集計でできるかもしれませんが。

あとは、このQ_CHKクエリーをつなげて遊んでみるかな。

会員情報
ID  入会日     更新日  point  住所    氏名     電話番号  記号
1   2003/04/01           120  名古屋  ドラキチ           あ
2   2002/03/01 2003/03/01 15  北海道  どさんこ           い
3   2003/05/01             0                             う

なんて、明細データの記号項目とQ_CHKをつなげて、Q_CHKのfld3を取り出してみます。

会員情報の記号とQ_CHKのfld1をまずつなげます。
その後結合プロパティを会員情報全てに変更します。
あっ、マスターが見つからない時（テストでは"う"の時）を考慮して


↑結合プロパティの設定サンプル、右押してプロパティ変えただけ

あとは、ほしいデータをセレクトします。

↑fld3をQ_CHKから選択してみました。

ID  入会日     住所    氏名     記号    fld3
1   2003/04/01 名古屋  ドラキチ あ      b
2   2002/03/01 北海道  どさんこ い      c
3   2003/05/01                  う      

と、無事にデータが取れました。

１つ順位付けのクエリーを使用したけど（逃げたけど）、
１つのＳＱＬで福問い合わせで書くと、なんか長くなりそうですね。

あとは、順位付けて細工したマスターデータ、量が多いと、
Q_CHKクエリーの中でDcountしているので、速度の注意が必要かなぁ。

もし繰り返し使うなら、一時テーブルに書き出す(テーブル作成orデータ削除・追加)、
集計や印刷作業
終了後一時テーブルを消す、
なんて方が、速度的には速いし、安心できるかも。

/*
 * ５．終わりの挨拶
*/

サンプルファイルは、
http://www.ken3.org/vba/lzh/vba088.lzh
にdb088.mdb(Access2000版)が保存されています。
＊Access97のマシーン熱暴走気味で不安定なので、
  Access2000になってます。

今回は、クエリーでDcount関数を使用した、
順位付けのお話、ついでにグループ別の順位付けの話。

拾い読みして、
１つでも何かの参考となれば幸いです。

Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
※評価は↓で投票してね。感想は掲示板かメールでくださいね。

ページフッター

ここまで、読んでいただきどうもです。ここから下は、三流君のホームページの紹介・案内です
目的の情報が見つかったか？少々心配しつつ、、、

種類別のリンク や 広告など

Blogとリンク:[三流君の作業日記]/ [VBAやASPのサンプルコード]/ 広告-[通販人気商品の足跡]