<HOME  <お願い事項  <Access2000 TOP   <Access97 TOP   <サイト内検索
  Ac2002--VBAの沼 > ひとつのフィールドの値を複数のフィールドに分ける
  



ひとつのフィールドの値を複数のレコードに分ける

さらにもう一例・・・こんなテーブルがあったとします。

ひとり複数箇所の受け持ち場所があるので、その様子を書き記したテーブルです。え?何の受け持ちかって?それはちょっといえないですね。
眺めるだけとか、印刷するだけとかいうなら、これでもいいんですけど、「福岡を担当している人は何人いる?」とか、「ゴジラさんは何箇所受け持ってる?」とかいうことを知りたい場合、どうやって出します?

まあー・・・印刷して、ひとつひとつ目出確認しながら数えればいいんですよね。

でもそれじゃあ・・・という場合は、なんとかして[担当場所]フィールドの中身を「場所」ごとにばらさないとなりません。
こういうテーブルにしちゃうと、リレーショナルデータベース的な扱いがすごく困難になり、非効率です。
でも、やむを得ずこういうテーブルになってしまった場合は、それなりに苦労しないとならないのです。
はっきりいってうんざりするほどめんどくさいです。半端じゃありません。
心して取り組んでくださいね。



    東京,大阪,名古屋

今回の場合、↑こういうふうに半角のカンマで区切った形で入力されています。
とりあえず、カンマで区切られているところに注目してください。
これ、

    東京大阪名古屋

とつながって入力されてしまってたら、ほぼ絶望的です。
コンピュータはそんなに頭がよくないです。
こういうテーブルを設計した人を呪って、「福岡を担当している人は何人いる?」とか、「ゴジラさんは何箇所受け持ってる?」とかいう集計表はあきらめましょう。

何かしら「区切り記号」が入っている場合は、まだちょっと望みがあります。
Split関数という関数があるので、これを何とか駆使してみましょう。

 Split("みかん,りんご,バナナ", ",")

この式の答えは、

    みかん
    りんご
    バナナ

こうなります。 "みかん,りんご,バナナ"  という文字列を、半角のカンマのところで区切って出せ、という命令です。
おお!使えるじゃないか!と喜ぶのはまだ早い。
Split関数は、指定された記号(文字でもいいんですが)のところで文字列を区切って、一次元配列という形で返す関数です。
いちじげんはいれつ???



要はですね。。。

  Dim AAA
  AAA= Split(“みかん,りんご,バナナ”, ",")

こういう構文を実行した場合(AAAはVriant型の変数ってことになるんですが)、AAAという変数の中にちょっとした「テーブル」みたいなものができるんです。コンボボックスとかリストボックスの値集合ソースの方がイメージが近いかな・・・。
なので、AAAにSplit関数の答えを入れたあとは、単にAAAを表示させようとしてもダメです。
「AAAの中の何列目何行目の値が必要です」と、列と行の指定をしてやらないとならないのです。



たとえば、仮に、ですね、ちょっとしたプロシージャを作ってテストをしてみたとして、

ってやると、プロシージャaaaaaをイミディエイトウィンドウで実行した結果は「りんご」になります。

AAA(1)で、、AAAという配列のなかの1行目((1)のように、括弧の中に入った数字のことをことをインデックスと呼ぶのですが、正確には「行数」とはちょっとニュアンスが違うかも・・・)という意味になりますのです。
コンボボックスのColumnも、Column(0)ってやると1列目のことを指してたと思うんですが、あれと同じようなもんです。
AAA(1)
で、「AAAという変数に代入されている配列の中の、2行目」を現しています。

こうすると、AAAの中に入っている配列の要素(りんごとかバナナとか)を全部出力することができます。

これ、最初から3つの要素があることがわかってるので、繰り返しは3回(0から2まで)と最初からわかります。
iという変数に繰り返しの回数を数えさせ、同時にIを「配列の行数の指定」として使っています。
ループの1回目はiは0、2回目のループは1、3回目のループは2で、iが2になったら、ループは終わりですもんね。
For Nextの使い方、問題ナシですか???
で、後は、i の繰り返しの数を、何とかして調べる方法を考えないとならないですね・・・。

うーん、一番確かなのは、カンマの数を数えるとか・・・ですかねぇ。
でもこの「カンマの数を数える」のがまためんどいんですよー

と、このプロシージャをイミディエイトウィンドウで実行した結果は、2になります。
カンマが4桁目と8桁目にあることを拾って、Cntという変数に入れて数えてくれます。

けっこうめんどくさいもんなんだなぁ・・・。



さあーーーこのふたつをうまいこと組み合わせて、フィールドの値をバラすプロシージャ、作ってみましょう。
今回は、ループの中に小さいループが入る形になります。

 外側:[担当テーブル]のEOFまで繰り返すループ
     内側:[担当場所]フィールドに入力されている「場所名」をバラバラにするまで繰り返すループ

と、こんな感じになりますね。
で、ループをまわして得た結果を、やっぱりどっか別のテーブルに追加していきます。
そこで、こんな感じのテーブルを用意しました。

いちおう、[番号]と[ID]を主キーにしていますが、今回は練習なので、主キーはなくてもよいと思います。
[ID]というフィールドは、オートナンバー型でもいいんですけれど・・・これは、表向き使うつもりのないフィールドです。
テーブルというものは表計算ソフトのワークシートと違って、1枚の大きな表があるのではなく、レコードの集合体に過ぎません。だからこそ、データの並べ替え・検索に柔軟に対応できるわけなんですけど、そのためには「並べ替えの指示を出すことができるフィールド」が何かしらないとならないとこは、皆さんもうご承知ですよね。

[番号]順に並べた後、[担当場所]でうまいこと並べ替えたい(東京,大阪,名古屋となってたら、この順番で表示させたい)場合は、「東京」が1番上にくるよう、何かしら配慮をしておく必要があります。
オートナンバー型にしておけば、とりあえず「テーブルに追加した順番」に並べ替えをすることはできるようになりますよね。あるいは、オートナンバー型でないにしても、なんかしら「連続した番号」を付けるなどして、「東京、大阪、名古屋」の順番にレコードが並ぶようにしておきましょう。
「主キーとかレコードの並べ替えとか特に考えなくても、あたしの作ったテーブルでは、必ず入力順にレコードが並んで入ってますけど?」って言ってる人、それって単なる偶然ですからね。。。(この「ですけど?」っていう言い方、なーんかむかつくんだよなぁ)
これは、MS-Accessのお話ではなくて、パソコンのハードディスクとWindowsというOSの、「データの保存の仕方」によるものなので、理解しがたいところかもしれないですけどね。たまたま、ハードディスク内に連続した空き領域があって、たまたまきちんと入力順にデータが収まっていただけの話で、デフラグやったり、ハードディスク交換してバックアップから戻した場合とかは、どうなるかわかりませんからね。。。。



といっても・・・・今日はテーブル設計が主題ではないので、テーブルはなんでもいいです。
わたしは↑先ほどのようなテーブルで試しますんで、よろしければ同じようにやってみてください。

主軸はこんな感じですかね・・・。

そしたら、まず、外側のループです。
これは問題ないですよね。

問題は、この内側に入れるループです。
[担当場所]のフィールドをカンマで分けてバラバラにして、それぞれ1レコードずつにするわけですね。
そこで、このループのために、まず「配列」をこしらえなければなりません。
そして、配列の中の要素の数だけ、ループを繰り返すことになります。
このループの中で、[担当テーブル]のフィールドの各値を[担当テーブル2]に追加していきます。
通常、1レコードに対して1回やればいいはずのAddNewを、配列の要素の数だけ繰り返すわけですね。
ただし、AddNewする値は、[番号][名前]は毎回同じモノです。変わるのは[担当場所]の部分だけ。オートナンバー型にしていない場合は、[ID]のフィールドに「ループの回数」を代入していくようにします。

まず、配列用の変数が若干必要になるので、宣言しておきましょう。

わたしは全部、型の指定をしないで宣言しちゃいました(Vriant型になります)。

AにはSplit関数の答えを入れます。つまり、この中に「1次元配列」という形でならんだデータが入るわけですね。
配列のお話は、また機会がありましたら載せていきたいと思いますので、今回はVariant型でやってみてください。
Bには、[担当テーブル]の[担当場所]の内容を代入します。東京,大阪,名古屋とか、そういう文字列が入るわけですね。
これも・・・今回Variant型にしちゃいますが、本来はStringで行くべきところかなぁと思います。
 と  は、For Nextのループの回数を数えるために使う変数として宣言してます。
これも、そんな何万回もループするわけもないと思うんで、IntegerとかByteとかでいいと思うんですが、今回はいっしょにVariant型として宣言しちゃってます。今回は j を[担当場所]の文字列をカウントする際のループの回数を、i は配列をひとつずつ拾ってテーブルに代入する際のループの回数を数えるために使います。また、i は、配列の中で「今何行目?」を指示するためにも使うので、今回は非常に重要な役割を果たします。
Cntは、カンマの数を入れる変数です。これがそのまま、For Nextのループ回数になります。
変数名は同じじゃなくてもよいので、わかりやすいものをつけてくださいね。



変数の宣言のところ、今までお話した内容をもう少しフォローさせていただきますと、

 ■Dim 変数名 as 変数のデータ型 という書き方をします。
 ■なるべく、処理の先頭のほうで宣言しておくほうがよい。っていうか当たり前だ?
 ■データ型によって、その変数が使用するメモリ領域の量などが変わるので、適切なデータ型を宣言すること。
 ■データ型を何も指定しない場合は、Variant型というタイプになる。
 ■一行に、複数の変数を宣言することも可能。
  (例)
   Dim A, B, C   ←A、B、CというVariant型の変数の宣言
   Dim A as integer, B as String ←Aという整数型の変数と、Bというテキスト型の変数の宣言
   Dim A, B as String ←AというVariant型の変数と、Bというテキスト型の変数の宣言

データ型の詳細については、ヘルプを参照してください。
また、変数の宣言の方法などについても、すごく詳しく書いてあるので、目を通しておかれるとよいと思います。

たまに、問い合わせもらうことがあるんですけど、i って入力すると、大文字の I になっちゃうんですけど・・・って。
VBEの中でも同じようになるかどうかはわからないんですけど、これって、オートコレクトです。
なんか意味があるのかどうかわかんないですが、なぜかMS-Accessにもあるんですよね。
オートコレクト。MS-Accessのほうのウィンドウ(VBEじゃなくて)の[ツール]→[オートコレクト]で、i を一文字だけ入力した場合、自動的に I になるように設定されてるからじゃないでしょうかね。
でも結局のところ変数名としては、I でも i でもできると思うので、気にしないで進めてください。


このループの中で、さらに小さいループを回します。
まず、担当場所のカンマの数を数えます。

で、次に、Split関数を使って、横に並んだベタな文字列を、カンマで区切りつつ配列化します。

そして、配列の分だけ、レコードをAddNewしていきます。
このループの中で、AddNewするというところがミソですね。

こんな感じかな・・・。
多分、これで望みどおりの結果が得られるようになると思います



書き終わったらコンパイルして、スペルミスなどがないかどうかチェックしましょう。
このプロシージャを、例によってイミディエイトウィンドウで実行してみましょうか。
双方のテーブルの中身はこうなります。


[ID]は、配列から取り出したときの変数をそのまま代入している関係で0から始まった番号がついちゃいますけど、これが嫌なら、

  rs2!ID = i

これを

  rs2!ID = i + 1

ってやっちゃえば、1から始まる番号がつきそうです。
まあ、[ID]は今回、「担当場所フィールドに入力した順番で並んでほしいので」つけたようなもんなので、何番がついてもいいかな、と思ってます。

これで、「正規化されたテーブル」(っていうのかなぁ)になるわけで、こういう構造のテーブルなら、たとえば、集計クエリを使って、

各社員ごとに、「その社員が今何箇所くらい担当をしているのか」を数値化したり、

その場所が、いったい何人くらいの社員によって担当されているのか、など、いろんな角度の集計ができるようになります。
MS-Accessみたいな、リレーショナルデータベースっぽい考え方のデータベースでは、縦方向に伸びるようにテーブル設計しておかないと、集計したくても身動き取れない場合が多々ありますからね。



もし、何度も何度も繰り返し使う可能性があるなら、AddNewする前にチェックするのがいいかもしれないですね。たとえば

こんな感じにしてみてはどうでしょう。
ループにはいってからすぐに、Findメソッドを使って、書き出し先のテーブルに既にあるかどうかチェックします。
最後のメッセージボックスは(Else と MsgBox の行は)、最終的にはいらないと思います。
うまいこと機能しているかどうかのテスト用に加えてみました。

[担当場所]をうまくバラすことができれば、いろいろ応用できると思います。
今回は配列という方法を使いましたけれども、状況によっては他にもいろいろな方法が考えられそうですね。

このように、さまざまな条件が絡む場合は、どうしても追加クエリでは難しい場合があります。
クエリでも、何段階かに分けて作れば、できないことはないかもしれませんが・・・でも、それならば、コードを書いて処理をしたほうが、わかりやすい場合もあると思います。
各種クエリと、コードをうまく使い分けて、効率よいデータ管理をしていってくださいね。