<HOME  <お願い事項   <Access2002 TOP   <Access97 TOP   <サイト内検索
 MS-Access2000超入門部屋--クエリで「ひとつ前のレコード」を参照する方法



クエリ開いたとき、一つ前のレコードの値を横に並べて表示させたい・・・って思ったことあります?
はっきりいって無理です(爆)
コレは多分、ExcelとかLotus1-2-3とか、いわゆる表計算ソフト系の操作に慣れてる方の発想だと思います。

表計算ソフトというのは、べろーんと1枚のシートを広げた状態がひとかたまりですよね。
何をやるんでもファイル単位で操作をします。
だから、一つ前のレコード、つったって、ひとつのシート内のお話ですから前もくそもなく、セルの座標で自由に好きなとこの値を参照できるわけです。
一枚のシート・・・とっても便利ですよね。手軽です。

方やAccessを初めとするデータベース系のソフトウェアのデータの持ち方というのはレコード単位です。
したがって、レコードひとつひとつにとってみたら、「前のレコード」という考え方はありません。
主キーとかインデックスといった種類のフィールドの中身を見てデータを整列させているだけなので、普段から表の形にレコードが整列しているわけじゃないんですね。
コレお話すると、「Accessってすごく不便。Excelの方が便利」とおっしゃる方もいらっしゃいます。
でも、レコード単位でデータを扱うということにもちゃんと意味があるんですよ。
Excelの方が向いてる処理っていうのもいっぱいありますから、甲乙つけずにうまく使い分けていってくださいね。

ここでご紹介する方法は、多少SQLと呼ばれるデータベース言語の知識が必要になります。
っていっても、別にそんな難しいことをどうこうというのではなくて、あったほうがきっと応用が利いてもっと理解が深まる、という程度ですけど・・・。
別にそんな詳しく理解しなくても、出るもの出ればいいや、という方は、とりあえずざっと読んでみてください。


突然ですが、こんなテーブルがあります。
今日は練習なので適当でいいんですけど、ポイントは「番号」というフィールドです。これ、主キーにしています。

別に主キーじゃなくてもいいんですけど、要するに、Accessから見てもちゃんとした並び順がわかるようなフィールドが1つ、必ず必要ですね。
「日付」のフィールドだと、6/30が2件入ってて、この2件がどういう順番で並ぶかによって「前のレコード」って変わってきちゃいますよね。データベースというものは「レコード単位」で物事を考えますので、基本的に「入力した順」というのはあまりあてになんないです。

こういうテーブルで、「一つ前のレコードの金額を、横に並べたい」ってのはちょっと無理。
しろくまの次がカバという保証はない、と考えてください。
上のような順番で並ぶように、必ずなんかしら並べ替えの目安になるフィールドを設けましょう。



早い話がこうしたいんですよね↓1行ずらして表示されるようにしていっていうか。。。

こういうことは基本的に無理なので、とりあえず「前のレコードを参照する」っていうのはあきらめて、他の方法を考えましょう(いきなりかい)

どっちにしても、普通のクエリは、「1レコードを1行として並べて表示する」ようになりますから、上のように別のレコードの列をずらして表示するようなことはできないんです。でも・・・ひとつだけ、方法があります。

じゃ、やってみましょう。
最初は練習ですんで、テーブルの名前は短めでわかりやすいものの方がよいと思います。
わたしのは「テブ」っていう名前です(よけいわかりにくいじゃん)


最初にテーブルを選ぶとき、うっかり2回、テーブルを選びましょう。2回続けて「追加」のボタンをクリックするのです。

すると・・・下の図のように、同じテーブルがふたつ選択されたことになっちゃうんですね。普段は失敗ですけど、今回はこれでいいんです。
テーブルの表示ウィンドウだけ閉じてください。


よく見ると、片方がテブ、片方がテブ_1って名前になってると思います。
で、テーブルがふたつなのに、結合線はありませんね。結合させちゃダメですよ。
ほんとはひとつのテーブルなんですからね。

と、こんな感じでとりあえず両方のテーブルから「番号」のフィールドだけ選んでみましょう。ちょっとわかりにくいので、並べ替えをしておくといいと思います。


・・・・。テーブルには8レコードしかないんですけど、64レコード出てきちゃうよ??

テーブルがふたつ選ばれてるのに、結合の条件がないので、どうやって結合させて1レコードとみなしたらいいのかAccessにはわからず、とりあえず一個ずつ結合させとくか・・・ということで、結果、二乗になってるわけですね。だから、8×8で、64件出てきちゃうんです。

ふだんはこんなクエリ、しっぱいなんですよ。でも・・・。よく見ると、中に使えそうなレコードがあるじゃないですか。

ね?

0002の1つ前のレコード0001が横に並んでるレコードがありますよね。
左の図で、赤い印のついてるレコードです。

なんとかこういうのだけ取り出すことができれば、かなり遠回りですけど目的は達成できそうじゃないです??
結合線のないクエリーも、ちょっとした工夫で役にたちそうですよね。
Accessには申し訳ないですけど・・・ちょっと無理してもらいましょう。

んじゃ、残りのフィールドも並べておきましょうか。あ、別に「金額」だけでもいいですよ。
とりあえず絶対必要なのは「テブの番号」と「テブ_1の番号」の2つだけなので、あとはあってもなくてもいいです。

どういう抽出条件入れたらいいんでしょうね。
 「番号0001のときは前のレコードはないので、0001は必要ない」
 「番号0002のときは前のレコードは0001」
 「番号0005のときは前のレコードは0002」

  ・
  ・
ということになりますかね。つまりは、「テブの番号より小さい値を持つテブ_1の番号」ってことかな・・・。

あ、でも、0002のときは0001しかないですけど、0005から見ると、自分より小さい番号って0002と0001のふたつあるから、まだダブっちゃうか・・。0006のときは0005と0002と0001の3つになっちゃう。

0005のとき、0002と0001のうちの0002の方だけ取り出したいのだから、ええと、

「テブの番号より小さい値を持つテブ_1の番号の中で、もっとも大きい値のもの」

ってことになりますかね。これが、番号のフィールドに対する抽出条件・・・。
ひえー複雑。もうイヤになってきてしまいました。なんであたしがこんな苦労しなくちゃいけないのよっ。

と、言ってもいられないですね。がんばりましょう。
でも、こんなの、どうやって抽出条件のトコに書けばいいんでしょう。抽出条件に書くもの自体が、既にクエリみたいですよね。


Accessには「サブクエリ」という機能があります。詳しくはヘルプ見てくださいね。

クエリの中にちっこいクエリを入れ子にするようなイメージになります。

でも、クエリの中にクエリを作るんじゃないんですよ。SQLと呼ばれるデータベース言語を使います。

実のところAccessのクエリも、SQLというデータベース言語で書いてるようなもんなんですけど、わかりにくいんでデザイン画面なんか用意されてるって感じになります。興味ある方は、SQLを解説した書籍などを参考にしてください。



ここでは必要な事柄だけ書いちゃいます。

Select 選ぶフィールド名 from テーブル名 where 抽出条件 order by 並べ替えに使うフィールド名

という感じでつらつらと書いていくのがSQLです。
よく見ると、今見ているクエリのデザイングリッドで無意識のうちに指定していることを横に並べて書いてる感じですよね。
で、こういう構文をカッコで囲んで、抽出条件のところに書いてやればいいんです。

ええと、今回は・・・「テブ_1の番号」の方の抽出条件に書きましょうか。2番目に選んでるし。つまり

「テブ_1の番号の値が、テブの番号より小さい値を持つレコードを抽出。

でもってさらにそん中のもっとも大きい値のもの」

というSQL文を書きます。SQLご存知の方はすぐ書けちゃうと思いますので挑戦してみてください。


SQLとか言われてもよくわからない・・・という方は、テーブルとフィールドの名前のトコだけ押さえてください。
そうすれば後で応用できますよね。
後で別のテーブルで同じようなことをする場合、どの部分を書き換えれば使えるのか・・・その点押さえておけば活用できると思います。

あ、「テブの番号」「テブ_1の番号」っていうのは、[テブ].[番号][テブ_1].[番号]って書きます。
半角のカギカッコはいらないかな・・・。なくてもいいかもしれません。真中は半角のドットです。

まず、「テブ_1の番号の値が、テブの番号より小さい値を持つレコードを抽出」この部分から・・・。

select 番号 from テブ as テブ_1 where テブ_1.番号 < テブ.番号

間は半角のスペースをあけてください。ひえーややっこしいですねぇ。不等号使ってますから、左右逆になっちゃわないよう気をつけてくださいね。

asっていうのは、複数のテーブルを参照するときに使うんですけど、from 一個目のテーブル as 二個目のテーブル as
三個目のテーブル・・・っていうふうに書きます。fromの中に含まれることになりますので、その辺ご注意ください。テーブルが1個のときはasはいりません。

でもって・・・。「さらにそん中のもっとも大きい値のもの」っていうのは、いわゆる集計クエリの「最大値」っていうやつにあたります。

SQLで書くとすると、


select max(番号) from テブ as テブ_1 where テブ_1.番号 < テブ.番号

ってなりますね。
コレを半角のかっこで囲ってやって、テブ_1の番号の抽出条件の方に入れてやります。



こうかな・・・。



と、こんな感じで出てきます。ひえー大変ですねー。

で、お気づきのことと思いますが、このクエリからは入力とかデータの書き換えとかできません。レコードセレクタのトコ、新規追加のためのウニみたいなボタン、クリックできなくなってますよね。いつも必ず一番下に存在する新規入力行も見当たらないし・・・。
このクエリからデータの書き換えとかされちゃうと、元のテブテーブルの中のどのレコードを書き換えるべきか、Accessには判断つかないんですね。ちゃんと結合してない妙なクエリですもんね。だから、見るだけなんです。
だってしょうがないんだも〜んもともと無理なんだから~。



あと、マイクロソフトのサイトに、こんな例が載ってましたので↓もし、続き番号のフィールドがあるならば、これもいけるかもしれないですね。
http://www.microsoft.com/JAPAN/support/kb/articles/J049/0/50.htm?LN=JA&SD=SO&FR=0


こいつを応用して、「金額の多い順に順位をつける」ってのもできますよ。もちろん更新不可能なクエリですけどね。
レコードは独立してますから、クエリで「1,2,3・・・」って感じで連番をふるのも一苦労なんですよ・・・挑戦してみます?

途中までおんなじっちゃーおんなじです。今回は「金額の大きいもん順」にならんだ結果がものをいいますから、金額のフィールドを双方のテーブルから一個ずつ選んでやります。
結果はこんな感じですよね。

で、これじゃ余分なレコードがいっぱいあるんですけど、ここで注目したいのが「順位」に当たるものはどうすればいいのかってことです。
もちろん、本当の意味の順位ってのは出せません。だからいろいろどたばたやるんですけど・・・。
「テブの金額と比べて、テブ_1の金額がそれ以上のレコードは何件あるか、って考えるんです。

15000000円以上のレコードは、テブ_1の方には1件ありますね。
5400000円以上のレコードは2件ありますよね。
と、いう調子で、ちょいとばかり眺めてみてください。

そう。このカウントした結果の数をサブクエリでゲットして、順位の代わりにしちゃおうかなって思ってるわけです。



「テブ_1の金額がテブの金額以上のレコードの件数をカウントする」っていうサブクエリを使って、適当な新しいフィールドを設けちゃいます。
ふふふ。めんどくさいでしょう。でもがんばってやってみましょう!

なんか適当なフィールド名: (select count(テブ.番号) from テブ as テブ_1 where テブ_1.金額>=テブ.金額)



すげー面倒・・・で、これを開いてみると・・・。

ね。なんか出るでしょ。

んでもってあとはさっきと同じ。テブ_1のフィールドを使って、抽出条件を作ります。
今度は、「以上」ってなってるとこ、注意してくださいね。
さっきは前のレコードだったけど、今度はべつに前のレコードってのは関係ないですから。。。以上ってしないと、順位1位は5400000円になっちゃいます。

順位が1のレコードに関しては、一番上の15000000ってのが残ればいいし、順位が2のレコードに関しては2行目の5400000のレコードが残ればいい・・・と考えていって、双方の金額にどういう関連があればよいか???(つづく)

(つづきです)

「テブ_1の番号の値が、テブの番号より等しいか小さい値を持つレコードを抽出」します。んでもってその中の最大値を抽出条件にするんですね。

(select max(テブ.金額) from テブ as テブ_1 where テブ_1.金額>=テブ.金額)

ううう・・・なんか目がうつろになってきた・・・。
でも、これ開くと・・・。なんとかかっこつくでしょ?



出ることがわかれば「金額」はふたつもいらないな・・・って思っても両方とも削除するわけに行かないんで、
かたっぽ非表示にしておくといいでしょう。


こういうの、結構大変なのよ〜。苦労していろんな技編み出してらっしゃる方、たくさんいらっしゃいます。
みなさんもいろいろ研究してみてくださいね。


同じ考え方で、累計をさせることもできそうですね。累計って、基本的にはレポートでやるのがいいと思うんですけどモ・・・。
これは、ぜひ皆さんで挑戦してみてくださいまし。

例えば、こんな感じのテーブルがあったとして、「金額」の累計を右端に表示させたいなぁと思った場合・・・。

同じように、こんな感じのクエリを作り、_1のテーブルの方の金額を合計して、列名を「累計」とでも変えてやります。

結果はこんな感じ・・・。考え方は同じですんで、このへんてこなクエリの基本構造が理解できれば、いろいろ応用ができると思いますよ。

ポイントは、基のテーブルの中に、「並べ替えの基準となる番号とか」があること。
基本的には主キーのないテーブルはこの処理には向かないと思ってください。