
	<HOME　　<お願い事項　　 <Access2000 TOP　　　<Access97 TOP　　　<サイト内検索

MS-Access2002チョ～入門部屋＞クエリを極める

49. 売上金額順の順位をつける

こはっきり言って、無理です。
ExcelのRANK関数みたいなのですって？？？
無理ですよそんなのー。

だって、MS-Accessは「データベース」ですよ。
レコードは1件1件独立して存在してるんです。1枚の大きな表があるわけじゃないんです。
そのレコードの売上金額が他のレコードの売上金額に対して、何番目くらいにいるかなんて、わかりませんよ。
ならば、Excelにエクスポートして、ExcelでRANK関数使ったらどうですか。絶対そっちのほうが楽です。
MS-Accessでやるなら、レポートを作って、レポートで連番つけるようにすればよいと思います。
とにかく、データシートビューの状態で「順位」をつけるのは、無理です。

で、今回は、無理を承知で、すごくややこしいことをやります。
がんばれそうだったら挑戦してみてください。

考え方としては、順位というよりも、「このテーブルの中に、自分より売上金額が少ないやつが何人いるか数えた数」を表示するって感じになります。

そして、このクエリは「表示専門」です。
ここからレコードの追加とか金額の変更入力とかはできません。

［社員テーブル］のレコードを、売上金額順に並べ替えてみました。

で、上から順番に「１、２・・・」と番号をつけていきたいと思うんですけどね。
1番のジョージは、1番だから順位が「1」なのではなくて、自分より売上が多いネコが自分ひとりしかいないから1と考えます。2番目の人は2番目なんじゃなくて、自分より売上が多いネコが、自分と、1位の人とふたりだから、2なのですね。

こういう考え方を持っておけば、売上の金額が変動しても、このクエリを開けば適切な順位を知ることができるはずです。また、単に「順位」を表すわけではないので、まったく同じ売上金額のネコ同士は基本的に「同率」になるはずです。

では、もう少し細かく考えてみましょう。
たとえば、ジョージひとり分の情報で考えてみますと、ジョージの売上金額は、全体のどの位置に属するかをまず考えます。
すると、こういう図になるわけで、ジョージの売上金額は右側の一覧表と比較をすることになります。
（全体の順位を出すわけですから、もちろんジョージ自身の売上金額も比較の対象になります）

他のネコもそれぞれ、テーブルの中の全レコードと比較して、自分より売上が多いネコの数を数えます。
ジョージの場合は、自分自身と「引き分け」なので、１分け１４勝です。
同じことを、ぐっさんもエルフィンもアキラも、やります。15人全員やるわけです。
このように、総当たり戦（自分自身とも戦う）みたいな感じですね。
そうすると、15人いたら15人×15人分のレコードで、えーと225とおりの組み合わせを見ていく必要があります。
「この売上金額とこの売上金額はどっちが多い？」という比較を、225通りやるわけですね。
で、そのうち、「次郎の売上vsだれかの売上」という比較が15通りあるわけで（自分自身の売上金額とも比較するから）その15通りのうち、いくつが「負けまたは引き分け」だったのかを考えます。

で、どうやって数えるか、というところなんですが、方法はいくつか考えられます。
でも基本的には「総当たり戦」なので、今回の場合は［社員テーブル］×［社員テーブル］みたいなことをやる必要があります。こういうときによく用いられるのが「自己結合」という方法です。
つまり、同じテーブルを2回使うクエリです。

クエリを作るときに、同じテーブルをわざと2回追加します。

そうすると、まったく同じテーブルなんですけど、まるで別のテーブルがあるみたいに横に並びますね。
で、右側の［社員テーブル］の名前、_1という名前がつきます。
これが自己結合です。しかも、結合線がありません。いらないのです。

試しにこの状態で、両方のテーブルから［名前］を選んで結果を見てみましょう。

総当たり戦になってること、おわかりいただけますか？？？

この255のレコードの中から、「自分より売上が少ない奴」だけ抽出します。
あ、「自分自身も」だから、「自分の売上以下の奴」ってことになりますね。でないと1位の人が「0位」になっちゃう。

ここでポイントなのが、［社員テーブル］の［売上］（比較するほう）に対して、［社員テーブル_1］の［売上］（比較されるほう。15人分の売上金額がある）が少ないか等しいか、という比較をするってところです。
ややっこしいですねー。
いちおうこんなふうに↓［売上］を2つ並べて、かたっぽに抽出条件を入れて、そっちを非表示にしてみてください。

結果を見ると？？？
件数、減ってますよね。

で、これを集計クエリにします。

［名前］と［売上］をグループ化し、もうひとつの［売上］はWhere条件というやつにします。
もし、同姓同名で売上金額も同じの人がいる場合は、［番号］も選んでくださいね。
でないと、同じ人とみなされちゃいますから・・・。

と、こんな感じになります↑とりあえず、15件にはなりました。

で、いよいよ仕上げです。
なんか適当なフィールドをもう一個選んで、カウントさせます。
なんでもいいですよ。まあ、［番号］が一番当たり障りないかな。。。
主キーだから「入力し忘れて空っぽ」ってことはないですから、数えるのにはもってこいのフィールドです。

どうすか？え？なんかバラバラ？

並べ替えしたらええがな。

ね。どうでしょう。順位っぽいでしょう？

欲を出せば、［番号のカウント］じゃなんなんで、何かしら名前をつけるといいですね。

もし、仮に、売上金額がまったく同じネコがいたとしたら・・・。
この状態だと、下のように、13位の次が15位、15位になります。

もし、13位の次が14位、14位、16位・・・っていうふうにしたい場合は、もう一工夫しましょう。

「自分より売上が低いひと」と「自分自身」を分けて、「または」っていう条件でつないだ形にします。

こっちのほうがふつうなのかな・・・。

今回のこのクエリって、いわゆる「直積」なんで、結果は15件でも実際には225件のレコードを相手にしているクエリってことになります。テーブルに50件ならクエリでは2500件、500件なら25000件になり、それだけ表示も遅くなります。
もとのテーブル（今回の場合は［社員テーブル］）のレコード数が多い場合は、やめたほうがいいかもしれませんね・・・。
今、5000件のテーブルで試してみたんですけど、20分経ってもデータシートビューになりませんので怖くなって強制終了させました（あたしってバカ？）

グループごとに順位を出すってのも可能ですよ。
もう一段階、グループでグループ化して、グループ名同士結びつけるようにしてやればオッケーのはずです。

データシートビューじゃちょっと見難いですけどね・・・。
やっぱこういうのは、グループ化したレポート作った方が絶対いいですよ。
こちらは、かなり大掛かりなクエリになっちゃって図が入りきらないので、SQLだけ書きますね。