|
||
| <HOME <お願い事項 <Access2002 TOP <Access2000 TOP <サイト内検索 | |
|
|
||
| Access97 VBAの森＞縦のものを横に | ||
（１）
じゃあ（じゃあってことはないけど）、続いてひとつ「クエリーが苦手そうな処理」を作ってみましょうか。
これも、多分ふつうはこんな処理作ることはまずないと思います。練習ですから・・・あんまりがっかりなさらず、明るく挑戦してみてください。楽しくやりますから・・・。
テーブルからデータを抽出して並べ替えたり、データを書き換えたり追加したりする処理は、クエリーを使った方が安全で開発効率もよいと思うんです。
クエリーは、SQLであり抽出条件を記述するデータ抽出処理でありながら、抽出結果をデータシートビューに表示する能力も持ちあわせています。
とっつきにくいかもしれませんが、これを使い込むことで、ある程度開発コストを押さえる効果があるはずなんです。
それと、クエリーは「実行したときの、一番最新のデータの状態」を開くので、これぞマルチユーザー＆リレーショナルデータベースってカンジなんです。
dbに詳しい方ならご納得だと思いますが、これをパソコン環境でやっちゃうってのは、マジですごい技術なんですよ。
そんなクエリーにも、やっぱり弱点はあります。苦手なこと。例えばですね・・・。
こんな感じのテーブルがあったとしますよね。
なんかフォームから伝票を入力して、ここにデータが日々たまっていくようになってるとします。
なんでもそうですけど、下方向にデータが増えていくイメージになりますよね。
このテーブルを基に、月に１回、「売上月ごとに売上金額を集計した表がほしい」なんてこと、ありがちですよね。
クエリーには「集計クエリー」という機能があるので（Σ）、これと[売上日]のフィールドの値をうまく活用すれば、月ごとに縦にデータを並べることは可能です。
[売上日]の月部分
(グループ化）売上金額の合計 １月 265 ２月 1,414 ３月 900 ４月 850 ６月 480 ８月 900 ９月 700
まあ、こういう感じのデータ群は作ることができるんですね。
このままじゃ体裁悪いので、レポートを作ったりフォームを作ったりして、見やすくてわかりやすい集計表に仕上げることができるわけです。
こういう風に、縦に伸び縮みする構造のものは、Accessは全体的に得意かもしれないですね。
レポートもフォームも、縦に増減する動きに順応するように考えられてます。ふつうはなんでもそうなのかな・・。
でもでも、
− １月 ２月 ３月 ４月 ５月 ６月 ７月 ８月 ９月 １０月 １１月 １２月 売上金額合計 265 1,414 900 850 480 900 700
こういう感じで、横に展開するタイプのデータ群を作るのは、ちょっと苦手かも・・・。
こういう表、上のテーブルからどういう操作で作るか、イメージ浮かびます？
あと、基本的にクエリーは、基のテーブルに存在しないデータは出せないので（あたりまえだが）、[売上日]のところに５月の日付のデータがなければ、必然的に５月の集計レコードも存在しないので、上のように５月の欄だけ作ってデータが空とかゼロとか、そういうことはできないんですね。
こういう表を作る場合は、ちょっぴり工夫しないとならないです。
話はちょっと変りますが、クロス集計クエリーというやつがあります。ウィザードでカンタンに作れて、クエリーの弱い部分を補う役割も果たしています。
例えば、下のような表があったとして、
これを部門ごとに月ごとに、売上金額の集計をする表に仕上げたいと思います。
で、クロス集計クエリーウィザードを使って、行に[売上月]、横に部門名を選んで、売上金額の合計を計算させます。
だいたい、yy/mm/ddという感じで日付の入力ってなされてますよね。これを、月ごとに集計できるように、下地のクエリーを作りましょう。
datepart関数をつかって、月の部分だけ取り出すようなフィールドを作ってもよいし、もし、複数年にまたがっているなら、Format([売上日],"yyyy/mm")という感じでフォーマット関数で取り出してみてもいいでしょう。
"1月"と表記させたいならDatePart("m",[売上日]) &
"月"という感じで、後ろに文字をくっつけて表記すれば、1月、2月と表示されると思います。
このクエリー実行すると、こうなりますね。
これをもとにクロス集計すれば、月ごとの集計ができますよね。
と、こんな感じになります。
これもクエリーなので、売上テーブルの中に１０月、１１月のデータが入力されてくれば、即内容が反映されてレコードが増えていきます。
クエリーですからね。なので、一度こういうクエリーの定義を作って名前を付けて保存しておけば、次からはそのクエリーを開くだけで、いつでもその時点での最新集計情報を知ることができるわけです。
ただ、やっぱしクエリーなので、５月のデータがなければ５月はないです。
そして、クロス集計クエリーなので、縦横の要素が揃わないと作れないということになります。
すごく便利なんですけどね、もしかしたらあんまり融通きかないかもしれないですね。
さて・・・クロス集計クエリーというやつがあることをチョットの間忘れて、クエリーの機能として、上のような部門別売上月ごとの集計表作成について考えてみましょうか。
まず、ふつうに集計クエリーというものを作ってみます。
クエリーはふつうの選択クエリーを作るこのデザイン画面で、集計クエリーというものにきりかえることができます。
下の表は、伝票番号を中心に考えた、明細表示の表ですね。これがふつうの状態。
売上月 伝票番号 売上金額 １月 0001 250 １月 0002 150 ２月 0003 200 ２月 0005 180 ２月 0008 200 ３月 0010 190 ３月 0018 280 ・ ・ ・
で、下の表は、売上月を中心に考え、明細を考えず売上月ごとにグループ化して売上金額を集計した、集計表。
売上月 売上金額の集計 １月の売上件数 １月 400 2 ２月 580 3 ３月 470 2
これをクエリーでやろうというわけですね。基にしているテーブルは、あくまでも伝票番号を中心に考えた明細データなんですけど、これを開く都度集計して、合計とか平均とか件数カウントとか、同じ売上月のレコードをまとめていろいろな計算をしてくれるクエリーです。
で、この集計クエリーを利用して、上のように"さくら組"のレコードだけ絞り出し、さくら組の売上金額の集計を出すように作ります。
とりあえずデータシートビューで見ると、こんな感じ。
で、賢明な方はもうお察しかと思いますが、これと同じ要領で"すみれ組"という条件で抽出した集計クエリーをつくって、"ばら組"・・・じゃなくて"チューリップ組"という条件で抽出した集計クエリーを作って、この３つのクエリーを[売上月]で結合してそれぞれの[売上金額の合計]を表示するクエリーを作るのです。状況によって、結合の仕方など少し工夫しないとなりませんが・・・。
クエリーの中でも、フィールド名を独自に設定できたり、数値の書式を変えたりできるので、まあそこそこの見栄えはすると思います。
要するに、最終的な表の形を思い描き、横に並ぶ項目名（この場合は"さくら組""すみれ組""チューリップ組"の３つ）の数＋α、クエリーを作ることになるんですね。
（ピンク色の字のところは、わたしが付けたフィールド名です）
ふえぇぇ〜じゃあ、部門が20ある場合は、クエリーが最低でも21個いるんだー・・・。
以前のAccessにはクロス集計クエリーというものがなかったんで、こういうことを本気でやってたんですよー。
まあ、一回作っちゃえば別に手間はかからないですけど、集計ものの多いデータベースでは、やたらとクエリーが増えて・・・。
大変ですけど、こういうのこそ、作り手の技量が試されるところかもしれないですね。
ちょっと話が横道にそれますが、、クエリーはテーブルの中のデータをダイナミックに出してるだけなんで、同じクエリーの実行結果の中に、明細と合計を混在させることはできないんですね。。。これがどうしてできないかっていうのは、おわかりですよね。
売上月 伝票番号 売上金額 １月 0001 250 １月 0002 150 1月の合計 2件 400 ２月 0003 200 ２月 0005 180 ２月 0008 200 ２月の合計 3件 580 ３月 0010 190 ３月 0018 280 ３月の合計 2件 470 ・ ・ ・
うまく説明できないんですけど・・（じたばた）。
こういうことをやりたいなら、データベースとしてレコード/フィールドの管理がきちっとなされているデータベースソフトは使わず、ExcelやLotus1-2-3のような、縦横に異なる属性の情報を入力しても別に何も言われない表計算ソフトを利用すべきでしょう。どうしてもAccessでやるなら、グループフッターをうまく活用したレポートを作成します。
クエリーをいろいろ工夫して使っていくと、こういうデータの構造というか、目的の帳票やフォームの表示に近づけるための段取りみたいなのが見えてくるんです。
多分、この訓練をしておくと、VBAを使ってデータの抽出をするプロシージャを作るときも、最も効率のよい作り方ができるんじゃないかなと思うわけなんです。
わたしも早くクエリーを極めて、段取りのいい開発ができるようになりたいです。
えーと・・・なんだったっけ・・・あ、そうか。Accessのクエリーが苦手とすること、おわかりいただけました？
くどいですが、もちろん「できないこと」じゃないんですよ。
クエリーを使うと返って非効率になる動作、これをVBAを活用して補おう、というのが、理想的なんじゃないかなーと、かように思う次第です。
|
 |