テーブルをつなげないクエリーとクロス集計クエリー

メルマガ発行内容

＜テーブルをつなげないクエリーとクロス集計クエリー＞
どうも、三流プログラマーのＫｅｎ３です。

今回は、
テーブルをつなげないクエリー
と
クロス集計クエリー
を使用して、小細工をしてみたいと思います。

今回の、サンプルファイルは、
http://www.ken3.org/vba/lzh/vba147.lzh
にdb147.mdb(Access2000版)が保存されています。
※クエリーやデータをいじって、遊んでみてください。

※あまり、お薦め出来ないけど、こんな方法もあるんだってことで。

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

VBA系の掲示板
http://www.ken3.org/cgi-bin/bbs/vba/wforum.cgi
に来た質問です。
----
以下のようなテーブルを
クエリーで横方向に展開することは出来るのでしょうしょうか？
何かいい方法があれば教えてください！
よろしくお願いします。

�� 商品  支払年月 支払回数 月額
1  商品A  200303     5     3000
2  商品B  200305     2     2000
3  商品C  200303    12     1000
            ↓
�� 商品  200303 200304 200305 200306 200307 200308〜200402
1  商品A 3000   3000   3000   3000   3000   
2  商品B               2000   2000
3  商品C 1000   1000   1000   1000   1000   1000　〜1000
--------------------------------------------------------
と
もらった質問で少し考えてみました。

/*
 * ２．クロス集計クエリーを使いたいけど、レコードが足りない・・・
*/

一番先に頭に思い浮かぶもの、
Excelだったら、分析系のピボットテーブルで行と列指定、
Accessだと、あまり使ったこと無いけどクロス集計クエリーってのがあったなぁ。

あっ、でも、
�� 商品  支払年月 支払回数 月額
1  商品A  200303     5     3000
の１レコードから、
�� 商品  200303 200304 200305 200306 200307 200308〜200402
1  商品A 3000   3000   3000   3000   3000   
と
５つの項目は作れないか。

レコードの作りが、
商品  支払年月 月額
商品A  200303  3000
商品A  200304  3000
商品A  200305  3000
商品A  200306  3000
商品A  200307  3000
ってなっていれば、行を商品、列を支払年月のクロス集計でいいんだけど。

ワークテーブル作って、支払回数分、レコードを発生させるかな・・・
^^^^^^^^^^^^^^^^^^^^
それが王道の処理なんだろうけど、
今回も小細工してやるぞ〜と、三流君の悪い癖が・・・
※参考程度にしてね。

/*
 * ３．問題なのは、レコードが足りないってことなので、強引に増やす
*/

問題は、はっきりしてるんだよね。
支払回数分レコードがあれば、クロス集計クエリーが使えるんだよねキット。

支払数分、レコードを増やすかぁ・・・う〜ん。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

まぁ、昔の人がよく言ってたり、偉そうな先輩が飲んだ時語っていると思うけど、
失敗を成功に生かす的な言葉で、
失敗は成功の元、会社によっては、失敗は成功の母とか言ってるよね。

Accessでクエリー作成していると、
２つのテーブルをつなげるリンクを忘れたりしないと思いますが、
もし、明細とマスターデータをつなげるのを忘れたら？どうなるでしょう？

隣のASPの例だけど、
http://www.ken3.org/cgi-bin/test/test073-1.asp
でテストできる
SQL From句にテストで２つのテーブルデータを指定(つなげない)
を見ると、
Select * From T_月別売上, T_商品
と、条件無しのSQL文を発行してます。
すると、どうなるか？
T_月別売上 が ８件
T_商品     が ６件
このデータ が ４８件のレコードを返してきます。
T_月別売上の1件に対して、リンクしてないので商品６件分データが作成されます。

これって、明らかにミスなんだけど、今回は、そのミスを利用してみます。

ダミーのテーブルを１つ作成します。
テーブル名はDummyCnt
フィールドはCNTと数値型でデータを作成して、
０〜データを入力します（０から連番で予想される最大支払回数まで作成する）


↑ダミーテーブル、データイメージ

そのダミーテーブル
と
DATAテーブルをつなげない、
そんな欠陥クエリーを作成します。
クエリーを実行すると、DummyCntで作成したレコード数分同じデータが作成されます

商品   支払年月 支払回数 月額  CNT
A1      200303  5       3000    0
A1      200303  5       3000    1
A1      200303  5       3000    2
    ・
    ・
A1      200303  5       3000    19
A1      200303  5       3000    20
B1      200305  4       5000    0
B1      200305  4       5000    1

とクエリーの結果が作成されます。
※０〜２０と２１個のダミーデータを作成してます。


↑ダミーテーブルとリンクしないとこんな感じです。

-【けんぞう！】---------------------------------------------------------
月500円、タバコなら２箱、120円缶コーヒーなら４缶分の謝礼をＧｅｔするなら
http://www.ken3.org/etc/500yen/   ←無料アンケート系の広告です。
『チッ、がんばって回答して月500円かよ』（お馬鹿なプログラマー：31歳）
------------------------------------------------------------------------

/*
 * ４．増えたデータを調整する
*/

商品   支払年月 支払回数 月額  CNT
A1      200303  5       3000    0
A1      200303  5       3000    1
A1      200303  5       3000    2
こんな、欠陥データ作られても、困るんだよね。
固定でｎ件データが増えても使えないでしょ？
ほしいイメージは、
^^^^^^^^^^^^^^^^^^
商品  支払年月 支払回数 月額
商品A  200303     5     3000
だったら、
商品  支払年月 月額
商品A  200303  3000
商品A  200304  3000
商品A  200305  3000
商品A  200306  3000
商品A  200307  3000
の
５件なんだよ。
~~~~
ですよね、
そこで、演算フィールドを作成して、それに近いデータを作ります。
^^^^^^^^^^^^^^^^^^^^^^
まずは、200303,200304,200305,200306,200307と、月数を増やしたいので、
基準の200303の文字列を日付型にCdate変換してから、
DateAddでCNT月後を計算してみます。

支払中:
DateAdd("m",[CNT],CDate(Left([支払年月],4) & "/" & Mid([支払年月],5,2) & "/01"))
と、計算式を作成して、支払中フィールドを作成しました。
すると、
支払年月にCNTを＋したフィールドが作成されます。

商品 支払年月 支払回数 月額 CNT 支払中
A1   200303   5        3000  0  2003/03/01 ←03月+CNTで３月
A1   200303   5        3000  1  2003/04/01   03月+CNTで４月
A1   200303   5        3000  2  2003/05/01   03月+CNTで５月
A1   200303   5        3000  3  2003/06/01   03月+CNTで６月
A1   200303   5        3000  4  2003/07/01  03月+CNTで７月(5回ここで終了したい)
A1   200303   5        3000  5  2003/08/01   03月+CNTで８月(いらないのに)
A1   200303   5        3000  6  2003/09/01   03月+CNTで９月(いらない)

となり、１ヶ月単位でCNTの値を利用して変化させてます。

次は、支払回数が５回なら、５個のデータにして必要・不必要を区別したい。
ホントはレコードを増やさないのがベストだけど、
FLGとフラグ項目を作成して、CNTが支払回数以内ならON(支払い中)とフラグを付けます

FLG: IIf([cnt]<[支払回数],"ON","")
と、演算フィールドを作成しました。

商品 支払年月 支払回数 月額 CNT 支払中     FLG
A1   200303   5        3000  0  2003/03/01 ON  03月+CNTで３月
A1   200303   5        3000  1  2003/04/01 ON  03月+CNTで４月
A1   200303   5        3000  2  2003/05/01 ON  03月+CNTで５月
A1   200303   5        3000  3  2003/06/01 ON  03月+CNTで６月
A1   200303   5        3000  4  2003/07/01 ON  03月+CNTで７月(5回終了)
A1   200303   5        3000  5  2003/08/01   (""の空白)
A1   200303   5        3000  6  2003/09/01   (""の空白)
と、クエリーの結果データが作成されました


↑２つの演算フィールドを追加する

/*
 * ５．データがそろったので、クロス集計クエリーを作成する
*/
作成したクエリーのFLGがONのデータをクロス集計すれば、
目的のクエリーを作ることが出来そうです。
※クエリーを２つ使ってしまいますが。

クエリーの種類をクロス集計クエリーにして、
行：商品
列：支払中（作成した演算フィールドを使用）
値：月額
ｗｈｅｒｅ条件：ＦＬＧ＝ＯＮのデータ

で、作成すると、なんとかできそうです。


↑作成イメージ

-【けんぞう！】---------------------------------------------------------
ASPが利用可能なレンタルサーバーをお探しのアナタ、
http://www.ken3.org/asp/server.html ← けんぞうも使っているサーバーの紹介
『おっIISでbasp21でメール送信、mdbも使えるよ』（三流ＰＧ：31歳）
------------------------------------------------------------------------　

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

今回は、
つなげないクエリーでデータを増やし
クロス集計クエリーを使用して、行列で集計したデータを作成してみました。

半ば強引に作成したので、データ量が多かったりすると、
集計に時間がかかったりするので、
判断しながら使ってみてください。

何か読者の心に残れば、うれしいです。
＊私の独り言をうまく消化してくださいね。

いつも失敗？の負け組のＫｅｎ３でした。
~~~~~~~(↑オイオイ)

ページフッター

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

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

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