[三流君] −−＞
[ＶＢＡで楽しく] −−＞
[バックナンバー一覧]
−−＞ No.088 Access クエリー DCount関数で順位付け
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関数を使用した、
順位付けのお話、ついでにグループ別の順位付けの話。
拾い読みして、
１つでも何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
※評価は↓で投票してね。感想は掲示板かメールでくださいね。
フィードバック
VBA系の→[掲示板]←を覗く、質問を書き込む
評価・感想
ページフッター(リンクや広告など)
[三流君(TOP ken3.org へ戻る)]
-- [VBA系TOPへ]
---- [VBA系バックナンバー目次へ移動]
------ [VBAでIEを操作 CreateObject("InternetExplorer.application")]・・・実は当店一番人気、VBAでIEを操作するサンプルです
------ [VBAでOutlookの操作 CreateObject("Outlook.Application")]・・・Outlookを使い、メール関係の処理です
------ [Access から Excel 連携 CreateObject("Excel.Application")]・・・人気のAccessからExcelへデータ書き出しなどです
------ [AccessのUserForm/サブフォームを操作]・・・アクセスでフォームを使ったサンプルです
------ [Accessのレポートを操作]・・・レポートを操作してみました
------ [Access クエリー関係やその他関数]・・・あまりまとまってませんが、スポット的な単体関数の解説です
------ [Excel UserForm(ユーザーフォーム)を操作する]・・・エクセルでユーザーフォームを作成して入力などを行ってます
------ [ExcelからAccessを操作する]・・・ExcelからAccessのマクロを起動してみました、
------ [Excel関係 関数、その他]・・・その他Excel関係です
------ [VBAでテキストファイル(*.txt,*.html,*.csv)の操作]・・・テキストファイルを使ったサンプルです
------ [VBA 標準関数関係とその他解説]・・・その他、グダグタ解説してます
広告
-- [通販系の売れ筋広告へ] ←主に楽天やAmazonのランキングです
blog
-- [三流君の作業日記] ← 日々の作業を少々
-- [通販あしあと] ← 通販ページの足跡を一覧で羅列
情報を探す
情報を探すならGoogleかな？
↓Web全体、サポート情報(support.microsoft.com)や三流君VBA(ken3.org)から検索する
書籍の購入
Webだけじゃさすがに勉強しきれないので、プログラミング関係の書籍も読んでみては？？
コンピュータ書籍の発送がハヤイ専門店
 コンピュータの本・専門店
|
※種類が豊富で探し易いです。※在庫ありが48時間以内発送が急ぎで資料や書籍がほしい時、とても助かります。
お奨め本の目次を見るだけでも勉強になったり
|
amazon.co.jpでキーワード別チェック
下記、私が設定したキーワードですが、こんな感じで資料や書籍を探ってみては？
[VBA全体を把握する] -- やはり全体をさらっと見たいですよね。
[SQL関連でＤＢ力UP] -- システムはデータベース設計が重要
[ADO接続を探る] -- VBAなのでADO接続を押さえておく
[Windows APIを探る] -- さらにAPIになて知ってれば強力だ！
[.NETを探る] -- と言っても時代は.NETに流れてるし
プログラミング以外でも知りたいことは多くって、
[人間関係] -- で、客先・上司、まわりに気を使い。
[プログラマーの自己啓発] -- プログラムだけじゃなくいろいろと向上したいよ
[コーチング・育成] -- 先輩になったら後輩(部下)の面倒をみてね。
そんなこんなでプログラマーっていろいろと大変なんだってば・・・