クロス集計クエリー

最近では、アクセス以外のデータベースでも一応は対応出来ているようだ。
ただ、コーディングが綺麗に見えないのは仕方ないのか？

この機能は、MSがどっかから買い取ったとかその当時言われていたのを思い出した。
そうだ、それを聞いた私はどんだけのものか？と興味を持って調べて覚えたものだった。

どっちがその技術を応用した物かは分かっていないが、ピボットテーブルも同じ技術的背景を持っていて作られている様だ。
イスラエルのソフト屋からだったか、技術買収してそれを応用したものだったと記憶している。15年以上前の話だからか？ソースは出てこなかった。

ピボットテーブルは制限なしでフィールドやレコード・データを扱えるのに対して、クロス集計クエリーはそれぞれ、３つ、１つ・１つという制限がある。これって差が大きすぎだ。

作った経験から言うと、出力するレコードのデータを考えて作っていないと、どちらも分かりにくい表になってしまう。

私の作ったプログラムでは、両方を採用している。又、どちらの長所も取り入れた作り方にしている。
その前処理と後処理を高速化して前述の制限も何のそので、上手く使えていると思う。見栄えも、最終的な出力でもないので、クロス集計クエリーでの見栄えのいい作表が苦手という点も気にならない。速度的にも、他のSQLでの実行に比べても、この部分に関しては遜色ないと思う。

大雑把に流れを整理すると、テキストファイル　ー＞　削除クエリー・追加クエリー　ー＞　テーブル　ー＞　不一致クエリー

テーブル　ー＞　クロス集計クエリー　ー＞　削除クエリー・追加クエリー　ー＞　テーブル　ー＞　クエリー　ー＞　テーブル（ライン・製品毎）

と、最後のテーブルが、原料・製品毎の集計した別バージョンも作って、それぞれをピボットテーブルで参照している形だ。
途中に桁やデータ型の変換や正規化などやったりして細かい処理が幾重にも重なってされているが、こちらは高速化の為だけに考えられたやり方なので、大筋には関係ない話になる。

アクセスの速度は、遅いように思われがちだが、開発スピードや最適化を上手くやれば結構使える物に仕上がる様だ。
PC単体での使用がほとんどだが、ODBCを活用するとネットワーク越しでもデータの再利用が可能になるので、SQLサーバなどにアップグレードしなくても使えている。今の所はデータ容量を見ても限界になったりすることも無いと言いきれるので、当分は現状維持になりそうだ。

クエリー（SQLを発行した後のデータの形として捉えてみる）

目次を、予告も兼ねてここで書いておこう
前回　テーブルをいかに作るか考える
今回　クエリー（SQLを発行した後のデータの形として捉えてみる）
次回　フォーム（入出力を画面などに行える、その場合のデータの取扱いや持ち方などについて）

それ以降　
　　　レポート（主に作表した結果を印刷するのに用いる）
　　　マクロ（アプリケーション内蔵の機能を呼び出すものがほとんど、アプリケーションを熟知すると自然に使えるようになってゆく。一部マクロ独自の機能もある）

　　　モジュール（複雑な条件分岐や、詳細な記述、変数など特殊なデータを扱いたい場合に向く、細かいエラーの出力を隠したりしたい時に扱いが楽になる。全体的にマクロを使うより更に高速に動作することも期待出来る。VBAは、イベントドリブン方式で書かれるので、タイミングに気をつけないと動きがつかみにくい点だけが困る。）

ここまで書くと、いかにもアクセス限定での話に見られがちだが、まだまだその種類は問わない。分かりやすいので、アクセスのデーターベースファイルの内容に合わせて説明したい。
データベース管理システム（DBMS）の仕様によっては、派手な作表を苦手としたり独自のマクロやモジュールを持たないSQL言語のみのアプリケーションもある。それら動作部分は別の所で説明されているものを参照されたし。

クエリーの種類には、色々有るがデータの動き・流れで分けると
・クエリー（通常の動きとしては止まっているが、参照データの更新やファイルオープンのタイミングで連想ゲームや表計算のセルの内容の様に、関係する先のデータが自動で更新される）
・追加クエリー
・削除クエリー
・更新クエリー
・テーブル作成クエリー
と５つに分けられる。

形で言えば、
・フィルターなどもその意味合いからは含まれる選択クエリー
・集計クエリー（同一データを集計して一つのデータに変更するクエリー）
・重複クエリー（データが重複しているかチェックしてその重複を抽出するクエリー）
・不一致クエリー（単純なパターンとして言えば、テーブルAとテーブルBの不一致データを抽出するクエリー、「AにあってBにない。」「BにあってAにない。」の2通りがある。）

・パラメータクエリー（動的に、実行と同時にボックス表示してそこへ入力されたデータ変更によって、抽出するクエリー）
・クロス集計クエリー（縦横で集計する、特殊なクエリー）
・ユニオンクエリー（複数のテーブルやクエリーの集合を一本化するクエリー、各テーブルなどのデータ型が揃っているとやりやすい。一方、元データをクエリー・テーブルと異なったものにすると、クエリーの持つ大雑把なデータ型合わせの必要が出てくる。そうでないとエラーや桁落ちが発生してしまう。例に上げているプログラムの一部クエリーでもクエリー・テーブルの2種類を対象にしたものが有ったので、その時はデータ型の一致に苦労したものだ）

中でも、このフィルターやソート・選択クエリーは各SQL言語の間でもそれほどコーディング量や方言などの差が見られない機能であり、速度やその他の機能との組み合わせの自由度だけに差がある。

重複クエリー、不一致クエリーはマスターデータを作成中には必要不可欠なクエリーだが、一度データが固定してしまうと使わないかも知れないクエリーだった。最短では、マスター作成時に一度は全件手入力必要が有ったりして、手入力では防止し難い二重登録してしまうなどのミスを防ぐのに役立つ。そんな場合に最低１回だけは、重複クエリーを実行する必要がある。その後は、テーブルの該当キーをユニーク指定にして追加時にエラーではねる処理で十分だ。定期的にデータのチェックをしたりで毎回処理を正確にするデータ間の整合性を保つのには重要な事だ。

では、クエリーの説明を例によって私が作成をしたプログラムを参考に似たような例を使ってしたい。テキストデータのインポートを削除クエリー、追加クエリーとやる必要があるが、これをマクロでそれぞれのクエリーを呼んでやってたら、当時は僅かに時間的がかかる処理だった。それらをモジュール変換をしてそれぞれSQLコールという形で実行した。通常マクロでの追加時にインポートエラーが有ると表示され、処理がストップするが、その表示なしで、連続処理させる事も出来た。

実際のデータ項目としては、ページ番号、短縮製品名、入り目（容量と小分包の数）、生産量合計、１から３１日の日々の生産数量、備考欄だ。

不要なデータは、ほとんど入ってこないように厳選しているのだがそれでもインポート時に残っているエラーを発生される原因のデータがある。それはインポートエラーで別にチェック出来るようにしている。（不具合発生時しか見ることも無いのだが）
インポート時には、テキスト（CHARタイプ）で取り込んで、その後に数値型変換するのとインポートエラーも減らす事が出来るようだ。これは、2クッションでデータを扱って変換するので、エラーデータを能動的に外せるのが理由。
インポート時に、桁数で取り込む固定位置データ入力方法と区切り文字で入れる方法がある。元のデータに区切り文字が混入したりするとデータの位置が不安定でおかしな場所になる。こういった区切り文字の選定をする場合は、入力データもそれに合わせる必要がある。

他にもメタ文字など、データの中にあったらばデータベースの動作時に不具合発生の原因となる文字が多々ある。それらをデータベースに入力してから削除したりするよりも、前処理としてテキストエディターのMIFESなどで一括して変更をかけておくと扱い易くなる。
エクセルからテキストデータが出された時には、タブ区切りが標準だったと思うが、タブを使うとエクセルのセルにはタブを入力するのが逆に困難なのでうまくいくようだった。
このプログラムでの禁止事項は、当然だが暦上にない日付けを入れてしまうと後で使用する追加プログラムでエラーを吐くことになる。例えば、2月31日とかだ（当然、閏年でも２９日しかない）。これをやると、後で月日を日のデータから興しているので、不具合になる。
書くのも何度目かになる所だが、全部で３１項目を日付で持っていないと、これもデータ項目のズレが発生するので、無い日付は元の予定表の上では該当セルの表示をしないか、表示0桁に変更という設定で逃げてもらうことにしている。
テキストファイルの連結した後にページの数値のコピーを全レコードの最初の桁に見つかった数値を使っている関係で、製品名の最初に数値を使ってしまうデータの不具合も有った、メモ書きなどを無駄にエクセルの表に入れてしまっているとこんな症状も発生した。こちらは、データベースよりもMIFES側でのMIL言語のプログラムの内容に合っていなかっただけで、単純にコピーと貼り付けをしている関係でこういったことも発生したのだ。この処理は、他の変更の処理とほぼ同時のタイミングで実行されている。表示を消してしまって実感が沸かないのが残念だが、文字数にして２万文字中の４００文字ほど、種類にして20文字ほどの変換を正確に一瞬でやってしまっているMIFESは凄い。
デバック中に処理結果を一々表示して、処理も一行づつ実行していたらかなりの時間を要す。作ったものにしか解らない凄さなのだが。。。
MIL言語での変換や計算を通して、かなりデータベースの様式になった。クエリーの説明のつもりが、その前段階のテキストデータの変換の話に変わって話が逸れてしまっている。話を元に戻す。

こういった前処理を済ませた事も手伝って、無事にインポートされたデータ。これだけでは元のデータを作業者などの人に見難くしただけのデータになっている。でも、データベースのデータとしては不足ない元データになっている。
このインポートデータのデータを、更に製品名のマスターと照合させて不一致が有ると、不一致リストとして作表するようにする。
毎回、たった数行の不一致データを印刷するのはもったいないし、修正後は不要になる。
場合によっては修正履歴を残しておく必要も発生するだろう。だが毎回必ず不一致の確認をする事になっているので、今の運用上は修正履歴として出していない。工夫した点は、レポート機能で作表した結果をプレビューで見るように、メニューのボタンにそのモジュールの割り付けをした。マクロの表示の種類にレポートのプレビューというのがあるので、それを使ってマクロ作成後にモジュール変換して取り込んだら作るのも早かった。一般的にやられているフォームをあちこちいじって表示させるよりも、シンプルになって見易かったのを良しとした。

短縮製品名の不一致があると、マスターの修正、追加、削除のパターンと元データの変更という２パターンがあるが、数件の不一致で変更も覚えておく必要もないし、次回に入力に気を付けることで対応可能ならば、中間データを生で修正かけて、一致させる方法も考えられるので、そういうパターンにも対応してる。

一件でも製品名マスターとの不一致があると、他のマスターにも不一致が発生する場合が多いので、他の関係のあるマスターとの間で照合をかける。
他のマスターの間でも不一致がなくなれば、それ以降の処理を継続する。
処理時間や処理内容で考えると、インポートで処理内容全体から見て1割ぐらいの処理が終わる。
不一致データの確認は作業者の担当だが、そこで8割以上の時間を使って、確認や修正作業の手動操作が入っているが、データーベースファイルの見えてない部分では、該当するクエリーが複数、インポート作業やら、確認作業などで手動修正されたタイミングにだけ該当テーブルの更新と関係のあるクエリーの更新がかかる。見た目では、処理内容の割に高速で処理出来ている。何度も書いたが処理の最適化の為のチューニングを済ませているのと、更新の設定回数を1回のみ、クエリーの処理の為の準備する容量の最適値にアクセスインストール後の初期設定から変更をかけているからだ。
このデータベースの実行時の使用容量を最適化する作業は、やっていないと無駄にメモリーやHDDの容量を使ってしまい、効率が悪くなる。
その後の処理は、データベースファイルを閉める直前のタイミングを狙って、一瞬で全体の処理内容の３割ぐらいの処理をして、更に、次回使用時の為にデータベースファイルの最適化した後に、ピボットテーブルを有するエクセルファイルを開く処理につなげる。

ここまでの説明で出てきたクエリーの種類（自動実行を除いた物）

・削除クエリー
・追加クエリー
・不一致クエリー

補足説明：
追加・削除クエリーの２つをまとめてテーブル作成クエリーに置き換える事も可能だが、不測のエラー出力時に実行を止めてしまう割合が高いので、データを削除して追加というやり方にしている。テーブル作成クエリーのもう一つの難点は、出力されたテーブルのデータ型が大雑把な事もある。
別の使われ方での説明すると、自動作番されたIDなどで、削除追加をやると一度削除したIDは、運用上は再発行出来なくなってしまうので、IDがらみの処理にはその都度、向いているのか判断しないと使えない。
又、テーブル作成クエリーは後述もしているが、クエリーのデータ型が標準では余裕を持ちすぎていたので、オプションの変更をかけて、できる限り少ない容量に変更して必要量を確保するようにしたりした。一度テーブル作成クエリーで様子を見てテーブルを作って、そのテーブルの項目のデータ型などを最適化したものに、削除・追加クエリーでデータを扱ったりするのもデータベースの速く作成するのには役立った。同時に、クエリーの処理内容レポート作成機能も活用した。当時はデータベースファイルの内容のレポートを全て一度に出力すると長時間かかってしまったのが残念であった。今は、それほど時間も取られないのが助かっている。

オリジナルアイデアと思う機能：
途中、便利に使えたのはデータが1項目のテーブルで、１から３１までの連番のものだ。（日付け？）他に一項目一行のデータも作ってみたが、こちらも同様に良かった。
これを挟むと、SQLで複数条件を記述して実行するパターンよりもかなり高速にSQLが戻ってきて感動した覚えが有る。
この機能は、どのSQLでも同じだと私は思っている便利なSQL機能だ。
どうしても高速化したいが、条件が複雑になったのが原因で高速化が望めない場合の苦肉の作として覚えておくと良いと思う。
ネタばれだが、どうやらSQLコマンドの　読み込み時間　＋　構文エラーのチェック　＋　実行の為の準備（メモリーや計算領域の初期化や確保）　＋　もろもろの処理　よりも、一度読み込んでしまってからのデータの処理に最適化されるどのSQLにもこんな感触があるので、こういった高速化が可能になる様だ。
もう一つついでに、人が見てデータが大量になってしまって一見処理が遅くなるだろうと考えられる抽出なしのクエリーの結果、データベースの操作で行える関係で説明すると直積だが、それを一度テーブルに書き出して、単純にフィルタをかけるだけの処理でつなぐと案外高速化する。（直積は、基本中の基本のようでデータベースの教科書の最初のあたりには出ていたなぁ。）
これは、一度に複雑な抽出クエリーを使うとその処理の中、データがある一定容量を越した瞬間、鈍いクエリーに変身してしまっている弊害があるからだろう。
早い話、メモリーに収まりきれずにスワップファイルに書き込み、読み込みを繰り返すわけだ。それより、一度書き出しだけを行ってその結果を抽出かける処理のフィルタや抽出クエリーで再使用した方が理屈が解れば速くなるわけだ。
無駄にそんな高速処理化を続けていた結果、出来上がったデータベースプログラムを見てみると、今では一瞬で処理が終わるデータベースのプログラムに大化けしてしまっている。
当時解ったのはテーブルの容量の足し算で処理時間が伸びるのではなく、掛け算の様に指数的に増加してしまうのは、この容量分をスワップファイルに移さないと無理だったからだと思う。
ただ、今の一般的PCが持っている性能からすると無駄な悪あがきになるだろうと思う。最適化しなくても、数秒で終わるであろう処理全体の時間を1秒以内にしてもさほど効果的とも言えないからだ。
今、このプログラムを動かしているPC、ＣＰＵはi5で２Gのメモリーだ。プログラムの必要な容量からすると十分過ぎる性能になっているようだ。

ちょっと解り辛い説明にしてしまったが、まとめると作業時間＋処理時間はほぼ8割以上が人手による確認作業の時間になる。
これは、パッと不一致ファイルの出力結果を見ている時間を指している。見ているだけのようだが、確認するまでに
比較的得意分野になる見栄えの良いレポートなどを作成出来るアクセスでの処理時間　＋　それを人が確認する時間　
は、通常のSQLのコマンドの処理をしているデータベースの処理時間に比べてかなり長い時間になっている。
プログラムの運用上、ほとんどの処理はデータ修正が入らないので、パッと見てOKとなっている。これは速いようにも思うが、そのパッと見るまでに8割以上の時間が経ってしまうのだ。
逆に複数の修正箇所があるようだと、印刷作業に移り、確実に修正を行えるようメニューに印刷ボタンを割り付けている。
不一致クエリーリストのレポートのプレビューを、モジュールから開いているので、通常の操作のように印刷もそこから続けて出来そうなものなのだが、プログラム編集作業中とは違って印刷ボタンが出ていなかったので、印刷はメインメニューに別に割り当てる事になった。これはアクセスの仕様なのだろう。最新版ではその辺も変わっている事も希望的観測で考えられる。
まとめると、ちょっとでもデータの修正が入れば、全体の作業時間　＋　プログラム処理時間のほぼ１００％が人手のデータ修正作業の時間になってしまう。時間がかかると言っても、通常業務の休憩時間にでも出来るほどの短時間で終わっている。

ここまで書いて全体にまとまり見られないのは、私の思い出すままに書き出しているからであって、個人個人記憶の場所や関連性に差があるのでお許し願いたい。関連があるから、記憶が蘇るのであって、逆に記憶する場合にも関係ある物事だと強く記憶されるものだとポジティブに捉えてもらえば幸いである。

データベースの作り方(テーブルをいかに作るか考える）

作ろうと思うデータベースの全体から見た構想を立てる。小説やゲームなどでいう世界観だろうか。

その中のデータを、動きのある物にするか固定にするかだが、これはプレーヤーなのかNPCなのかというところか。
先ずはデータの範囲を予想して、その範囲に有ったデータ型を用意しないといけない。
キーになるデータを決めて、そのキーに対応したリレーションを取れるように考える。
キーの数はその時々で変わるし、途中で変更もありだが何度も変更する様では、手間がかかりすぎる。この辺が経験が物をいうところになる。

データの転がしも、動きの途中で考えておかないと、いきなり結果を求めたシステムだと無理があって使いにくい物になりがちだ。
転がし方も、最終データ出力の為なのか、途中のシステムの効率の為なのか。システムの要になるものなのかといった方向性で変化する。

私の作ったデータベーステーブルを例に出すと、手入力で間違いの多いデータをユニークな短縮形に変化させて間違い、勘違いの要素を極力減らし、短縮名称をキーに品名マスターとのリレーション（関係）して正式名称やコードを取得する。

ここまで書いて、データベースって何のアプリケーションを指して言っているのか？って疑問が沸いたかも知れないが、使う道具は関係ないのだ。どんなものでも、そのシステムの仕組みを分かって作っていくと、後はプログラミングコードを実装してゆくだけの作業が残っているだけだからだ。
プログラムを作るのではなくシステムとして考えて、後は単純にコーディングするだけなのだ。
慣れると、システムが天から降りてくるような感じで一瞬でアイデアが沸くものだ。その時にコーディングの種類やプログラム言語の方言を知って要れば、何をコーディングしたいのかも自ずと解るものだ。

話はそれたが、手入力データは間違いが多いものである。それをそのままデータベースで使うとなると至難の技だし、間違えるなと言うのも酷な話である。
間違えを減らすために、コード番号などを決めて入力したり、3文字アルファベットなど覚えやすい形にした名前に変更したりと工夫するものだが、情報の冗長性と言うのに幅を持たせるとそれに伴って間違いも発生しやすい。
漢字などでも、読みが同一でも一見同じに見えて詳細な部品構成の異なる字が複数ある物もあり、ユニークとは言えない。フォントとしてみると、一種の芸術作品の様に同じ形は無いものだ。
それらの違いを、全て共通フォントで長音、濁点、破裂音などを省いて共通化の一助にしているのだ。
例）　シミュレーション　＝＞　シミレシン　といった様な形に変える。
これなら、元がシュミレーション　でも　シミレシン　になるのでよくある間違え方の差はなくなる。

製品名などにこれを当てはめると、そこまで微妙なネーミングしている会社も無いことだろうから、こういった手法も有効に働くと考える。
自社の製品でも、数多くあったり英名やカタカナ・ひらがな表記で読みがバラバラになってしまうものも多々あるだろうから間違えるなっていう精神論で仕事をすすめても無理が出るものだ。

同じく、フリーで書かれた文章をテキスト文字に起こしたりするのも難があるので、入力は表計算などで項目ぐらいは固定位置で書かれたものでないと、データとして使うには敷居が高くなる。
最低ラインで、表計算の固定位置に手入力と言うのが元データとしては譲れないレベルだろうか？

一方の、コーディング作業ではデータベースに用いられるSQL言語のコマンド数やその影響やカバーする範囲の広さもあって、他のプログラム言語と比較にならないぐらい少ない行数（コーディング数）でプログラムが簡潔に書けるのも魅力の一つだ。
UNIXのshコマンドなどの中で使われるパイプライン的なデータの扱いに慣れていると、更にSQLの応用範囲は広がる。
データをユニーク化することに成功したら、後はそれをキーとしてマスターデータとリレーションを繰り替えして、欲しいデータまで加工するのみだ。

主要なデータの流れで説明すると製品名、生産量、生産日１から生産日３１というデータの並びがあって、
それを、製品名の短縮名称に変換した物、生産量、その生産の日付けというデータに変化させる。
この変化までに、省略出来る長音・濁点・記号などを取ってしまい。各日付けの位置のデータの有る無しで、データのあったものだけを抽出して、該当日付けのデータと共に新規にテーブルに追加する。

例）
（数量）／日付け　　　　１　　　２　　　３　　　４　　　５　　　…
名称
ホットケーキ　　　　　　１　　　　　　　３　　　５

ジャム　　　　　　　　　１　　　　　　　２　　　１

はちみつ　　　　　　　　　　　　１　　　　　　　１

バター　　　　　　　　　　　　　１

こんな感じでデータが有れば、


ホトケキ　　　　　　　　　　　　１　　　　　　　３　　　５
シム　　　　　　　　　　　　　　１　　　　　　　２　　　１
ハチミツ　　　　　　　　　　　　　　　　１　　　　　　　１
ハタ　　　　　　　　　　　　　　　　　　１

と省略して

省略名　　　　　日　　　数
ホトケキ　　　　１　　　１
ホトケキ　　　　３　　　３
ホトケキ　　　　４　　　５
シム　　　　　　１　　　１
シム　　　　　　３　　　２
シム　　　　　　４　　　１
ハチミツ　　　　２　　　１
ハチミツ　　　　４　　　１
ハタ　　　　　　２　　　１

とデータベースとしては良い形まで変形するのだ。
これを、短縮品名、品コード、正式製品名の入ったマスターとリレーションする。
マスターは、こんな感じ。

短縮品名　　　　品コード　　　　正式製品名
ホトケキ　　　　０００１　　　　ホットケーキ
シム　　　　　　０００２　　　　ジャム
ハチミツ　　　　０００３　　　　蜂蜜
ハタ　　　　　　０００４　　　　バター

データベース処理の途中では、コードを扱った方が桁数による必要容量は少なくなり、処理速度は上がる。このマスターとリレーションした後に

品コード　　　　日　　　数
０００１　　　　１　　　１
０００１　　　　３　　　３
０００１　　　　４　　　５
０００２　　　　１　　　１
０００２　　　　３　　　２
０００２　　　　４　　　１
０００３　　　　２　　　１
０００３　　　　４　　　１
０００４　　　　２　　　１

と形を変える

で、それぞれのレシピ・処方をデータに持っている原単位マスターとリレーションする。原単位マスターはこんな感じ
品コード　原料コード　数量
0001　　　0001　　　　300
0001　　　0002　　　　200
0001　　　0003　　　　10
0001　　　0004　　　　10
0001　　　0005　　　　1
0001　　　0006　　　　20
0001　　　0012　　　　2
0002　　　0006　　　　200
0002　　　0007　　　　300
0002　　　0008　　　　50
0003　　　0009　　　　500
0004　　　0010　　　　500
0004　　　0011　　　　15

原料名のマスターもいる。作り方によっては製品名と同一のマスターに組み込んでもOKだが、ここでは話が分かりやすくするため別のマスターとしておく。

原料コード　　　　　　　原料名
０００１　　　　　　　　小麦粉（薄力粉）
０００２　　　　　　　　水
０００３　　　　　　　　バター
０００４　　　　　　　　ジャム
０００５　　　　　　　　卵
０００６　　　　　　　　砂糖
０００７　　　　　　　　いちご
０００８　　　　　　　　ゼラチン
０００９　　　　　　　　蜂蜜
００１０　　　　　　　　牛乳
００１１　　　　　　　　塩
００１２　　　　　　　　ベーキングパウダー

ここまでで、データの数字の品コード・原料コードも文字も全てテキストデータで数量のみ数値のデータ型を用意した方が、後の処理としては扱いやすいだろう。これでメインキャストは揃ったことになる。この出会い（リレーション）は次の回に説明することにする。

注意）　ここで取り上げているデータ並びに例題は、思いつくまま上げているだけで、実際の処方やレシピとは無関係です。これによって味などに不都合が生じても一切の保証は致しませんので悪しからず。
又、説明のため、厳密にデータの半角倍角やコード化の有効な手法などの区別していません。
この生データをそのまま使い動作を試みられても動くことは無いと思います。
あくまで、イメージとして捉えてください。

連携

手元に有った、アクセスとエクセルの連携本を見ていて気がついたことが1点。

連携をうたっているのに、ODBCについて触れられていなかった。
これは別のデータベースにつなぐ場合にも有効だが、標準でついているMSQUERYを間に挟むと、更に使いやすくなる機能だ。
私は、連携をとる時には直接かQUERYを挟むかを選ぶ事にしている。

どういう場合に良いかと言うと、中途半端にクエリーがオリジナルで要るような場合だ。
ちょっとしたフィルターを挟みたいとか、テーブルが複数有るがアクセスでやるほどでも無い時。ソートをエクセルで見る時だけ使いたいとか、色々とパターンが考えられる。
このQUERYは、今のエクセルのバージョンではテストしていないので不明だが、標準のインストールでは省かれている事が昔はあったので要注意である。ようは、エクセルのおまけで付いているソフトなのだが、おまけとしてはかなり優秀な出来だと思う。

データベースを覚えたての頃には重宝したソフトだったし今でも重宝している。
どっちがメンテや作るのが速いかを吟味して使うと、一番のやり方になるように思う。

このODBCは、元がCSVでもなんでもデータベース形式になってればOKなので、使いこなせるとバッチリと言える。

データベースの構築

私の永年、経験したプログラムをソフトウエアに進化すべく、作り方をオープンにしたいと思う。一読されて、これからデータベースを作ろうとされる方の参考になれば幸いです。背景を簡単に説明すると、現在、務めている会社で残業代を少し貰っただけで作成したプログラムなのだが、10年以上は安定して動作している今では外せないプログラムになっている。
仕事内容の詳細については、ここでは明かせないのが残念だが、処理の形態として言えばエクセル上の一ヶ月間の日々の生産予定データの入力されている表をテキストファイル化して、さらにデータベースの形に整え、データベースで細かいデータの加工処理を行う。その結果出来上がったのは、原料の使用量の予定データである。それをピボットテーブルとしてエクセルで作表する。途中、新製品や原料の変更などから来るマスターテーブルへの追加データがあればデータ入力が発生するが、ルーチン業務になっている製品に関してはデータの追加の打ち込み作業なしで処理を終える。生産数量のみの変更の場合も然り。

更にメインの変換方法を具体的に説明すると、テキストエディターMIFESのマクロであるMIL言語を用いてエクセルから抽出したCSV形式のデータから冗長性を欠除するべく加工（データベースのデーターとしてキーになるデータのユニーク化に役立つ処理）し、インポートエラーに繋がるデータも削除する。
通常は、データベース処理としては複雑になるテキストデータベース的な使い方でMIL言語での処理（例えば、全文検索でヒットしたデータの置換を特定データの数桁手前の数字を文字列の中から抽出する処理。アクセスでやるとSQLのネストやSQLの発行文字数が異常に増加する事になる）に向いていると思われる次月・前月に繋がる生産量などが入力されていた備考欄のデータの加算・減算などしている。（生産は連続しているので月ずれなどで生産予定がコロコロと変わるのだ。）

次は順を追って処理を見てみる。
１，抽出したいデータを指定範囲のみテキストデータに変換するエクセルのマクロを別ファイルに入れて元のエクセルのデータ表を参照する形で利用する。逆に、元の生産予定表は行列共に位置固定で作表していないとバグってしまう。実行させるマクロも変更不可にしている。これは別の心配事であるマクロウイルスにも強いと思う。
２，複数ページを一本化するために、ページ番号をデータの１桁目に入れ連結する。これはバッチファイルで作成可能。（コピーの順番を決め打ちしてるだけ。）

ここからMIFESのMIL言語で処理する。
３，バッチファイルで繋がったデータのレコード間でのコピーをMIFESのマクロで連続作成して、少しデータベースのデータの形に変わる。
４，備考欄の数量から加算・減算を前後の文字から条件処理する。
５，空白行の削除（データベースでも可能だが、出来るだけここでやっておく）エクセルから書き出す行数を決め打ちしてるだけ＾＾）
６，ゴミデータの削除（例えば、休日などを表す記号をデータ０として変換）
以下のMIL言語での作業は細かすぎるので割愛。

これらの作業を、矛盾なく高速で行うようにしている。

高速化の一番は、表示を止めるって事だが、エクセルでもMIFESでも当然やっている。見慣れていないと、フリーズ状態と間違うぐらいだ。
このフリーズに見える不具合への対策は、やりたい処理とは別の表示を使う事やフリーズと心配するであろう時間以内に終わってしまえば良い。私は高速を売りにしているので後者を選んだ。これらの処理で、データベースにインポートしてもエラーが少ないデータに変換されている。インポートエラーを吐いても、なんら支障はないがエラーは少ないに越したことはないと思う。（昔話で恐縮だが、エラーログが知らないうちに溜まり込んでしまい、それが原因でプログラムがクラッシュしたという事例をこのプログラムとは別のところで見たことがある。これは、外注したシステムだった事もありログのボリュームなどチェックしておらずクラッシュまで気がつかないのだった。）

ここからは、データベースの出番になる。データベースならどれでも使えるので、何でも良かったのだが会社の指定データベースがアクセスだったので、アクセスを使った。
開発当時、他のSQLで記述すると複雑になる、ピボットテーブル風のクロス集計クエリというのが目新しく、その機能を処理の途中で使うことにより、プログラム処理全体を単純化出来た。
他のSQLやマクロやVBAは、処理時間とデータの増減、処理の単純化の優先順位で適材適所に使うようにした。
SQLでもクエリーを使うと、初期設定では更新が複数同時進行する関係で当初はメモリーの限界まで使い切ってしまって、スワップファイルを使わざるを得ない状態だった。この対策は、クエリーの自動更新を１回のみにし、SQLとしてほとんどの部分をマクロやモジュールに記述して順次処理に変え対処した。同時にデータの件数の上限がほぼ同じといった処理になっていたので、上限を予想されるデータ量より少し緩く採った。これらの最適化でメモリー使用量が激減したようだ。（メモリー使用量のチェックで調べられる。）
追加、削除、更新クエリーも適材適所で使った。複雑で見難かったのが月の全日数分を１本化しているところで、３１個の日数分のデータ変換をSQLに組み込んだが、これはこれで眺めて見ると単純なSQLだ。（アクセスのクエリーの編集にはデザインとSQLがあるが単純というのはSQLで見た時の事だ。念のため）
実際の処理としては、１から３１まで並んだデータフィールド毎に、データがあれば日の１から３１の日データと共に該当のデータを追加をするだけだ。

実際SQLの作り方としては、便利なデザインモードを最初は使い回して、細部の調整はSQLを決め打ちするのが、作業としては慣れもあるが早かった。
別のデータベース言語への変換をするなら、SQL表示モードでコピーしてバッチファイルなりに載せて動かすのがいいが、アクセスは方言がキツイので苦労することになるだろう。
何度か言語の変換を試みたが、途中に肝になっているクロス集計クエリが便利すぎて、言語の変換をやめてしまっている。便利と言っても、今回の案件にぴったり合っていただけだろう。
お勧めは、プロトタイプをアクセスで速攻で作って、それを別のデータベースに変換するのが良いと思う。標準と言う形でMSが勧めているSQLサーバーも候補に上がる。自動変換するソフトのもあるので楽だろう。（MSの回し者ではないのでそれ以上の説明はあえてしない私。）

処理時間は、当時CPUがペンティアムで、メモリー５１２MBほどだった我が社で使わせてもらっていたPCでの話。
反応の戻り（SQLを実行してから処理完了するまで）、１秒が壁になっているようで、１秒以上の処理は指数的に増加していたが、戻りが１秒以下になっているSQLコマンド、それらを集めて複数同時に実行しても（といっても２０個も無いが）１秒以内で反応が戻ってきた。
これを実験的観察で知った私は、すべてのSQL処理を１秒以内に収めるべく試行錯誤したのだった。（これは、ほとんど趣味の領域になってしまっていたかも？）オラクルなどは、以前０．１秒以内を目標にSQLを最適化しているように聞いたことがある。

結局、完成したプログラム全体を見直すと、モジュールに内蔵したSQLとして８つほどになる。
処理を順に言えば、ファイルオープンと同時に旧データの削除と新データのテキストファイルからのインポートし、その後メニューを開き、各マスターデータとトランザクションデータ（ここではインポートしたデータのみ）の差分チェックをしたり、その後追加削除したデータがあるたびにマスター同士の差分チェックをメニューの項目を選ぶ事で確認や修正を行えるようにした。更に中間データをダイレクトに修正も可能にした。マスター全てとトランザクションに矛盾がなくなったら、出力データをセレクト（購入商品や転売商品などのデータを外すなど）して商品に必要な原料を、必要な日と該当商品、該当原料、担当部署別といった仕分けをピボットテーブルで出来るよう、冗長性を増した元データに加工する。（元とは最終出力表から見た元である。）
包装形態が違っても、製品名や種類が同じだと同一処方になるので、種類毎に集計した結果を（類）という一括りで出力するようにした。（関係ない話になるが私の担当する部署の生産品目では、この（類）が多数出現している。）
更に、元データとしては原料毎に各部署の使用量が分かると引当て時のチェックに使えると当時の原料引き当て担当者が言うので、その原料別（行）部署別（列）一覧表の元データも作った。（括弧内の行列はピボットテーブルでの位置関係）
この２つの元データをエクセルのピボットテーブルで参照すると、見やすく内容も濃い表が２つ出来るのだ。
途中までの処理は同一データなのだが、最後のピボットテーブルで見やすくという一点で、冗長性に差をつける為に元データ処理を分岐している。

開発当初を振り返ると、ピボットテーブルでの出力など最初は考えていなくて、手早く作ったデータベースの結果の表は商品名なしの原料のトータル数量のみ出力したものだった。こちらは、取り掛かってすぐに作れたのだが担当者には見難く使えないデータを出力した表になっていて、少々がっかりさせてしまった代物だった。
深く考えると生産が途中で変更されたりして、すでに引き当てている原料の消し込みなどが必要になるようで、個別にデータをチェック出来ないと折角のデータベースの仕事も無駄になってしまうようだ。実際業務では引き当て済みの原料にマーカーでチェックをしていたのを何度も見た。そういった点で、チェック表と個別の結果の表としてピボットテーブルに出力されていると、消し込みなども容易になる。

追加処理のプログラムなどについて
特定原料や特定商品は、更に細かい増減を知る必要があったので、連日の増減を予定生産量と期首の在庫量から追跡出来るようにしたり、在庫量が限られている原料や中間原料に関して、日々の引当量と在庫量の増減表を作ったり、棚卸しや税務報告に対して正確に作業する為に使えるような基礎データを作成したり、マスターデータのみを使ったものでは、原料の変更の影響を知るために該当する原料の製品一覧表（マスターデータの抽出とソートのみの表）を作ったりした。

プログラム作成後の仕事内容の変化について
ほぼメインのプログラムだけで原料引き当て担当者の仕事量が５時間／月ほど削減された。このプログラムを作るまでは全て電卓で計算していたのだ。さらに周りの追加処理のプログラムで、数時間／月の仕事を削減している。一番減らしていると思われる生産計画予定表を作成する担当の課長においては、シミュレーションとして利用が可能になり生産計画予定表作成後の影響のフィードバックを高速に行う事に繋がった。これで担当課長が異動してもこの生産予定表に関しての引き継ぎが容易になっているようだ。

今後の課題としては、今は生産計画からのデータを起こしている所を、販売計画からデータを起こせるようにする事。それが出来ると約１日かかっている生産会議自体が簡素化される事だろう。きっと人件費削減や周りに対しての影響も半端ない事になると思われる。それと同時に、1ヶ月単位で計算しているのを適時にリアルタイムで計算といった形にも変化が出来れば、必要量を必要な時に生産する事にも繋がると思う。

補足説明：プログラムの主な各部品点数など。
予定表　エクセルで10ページほど
マスターテーブル　２０ほどをアクセスとエクセルの参照テーブルの形で持っている
クエリー（追加、削除、更新なども含む）　開発途中のソースとして保管しているのも合わせて２００ほど
フォーム　メインメニューと数点の期首データなどの入力画面
レポート　チェック表や増減表など数ページ（後述の出力表とも、かぶってカウントしている。）
マクロ　メインのデータベースで２０ほど、追加プログラムでは各5個ほど
モジュール　メインメニューのオープン、クローズ時の自動実行をマクロからＣａｌｌして使用している2つと追加プログラム数点には必要なので使っており、全部で１０ほどある。

出力表
原料予定表（ピボットテーブル　各部署毎で10ページほど。こちらは製品毎に生産順に作表している）
原料各部署毎予定表（ピボットテーブル　原料予定表と同じデータを1つに集約している表）
特定原料増減表（引き当てを任意に行えるようにエクセル表の半分にアクセスのデータを参照貼り付けしただけの物で作るのは速かったが、かなり使える物になっている。）
特定中間原料増減表（中間原料だけに、原料と製品の量に影響する。チェックがし易い点が売りだ。こちらは、アクセスのレポート形式で出力）
包装材料予定表（こちらはデータの流れとしては原料と似ているが、詳細に包材毎に一覧表を作っている点で違っている。現在の倉庫在庫のデータもフォローしている点で更に使いやすいかと思っている）

クエリーでは、その違いを明確にカウントしていないがプログラムの自己監査用のレポートや動作チェック用の物、試行錯誤の結果、現在は使わないのが得策になっているプログラムやテーブルなどルーチンの流れに載っていない物など合わせてシーラカンスのように化石化したものなどかなり残っているので、現実には森に木の葉を落とした様な部品構成になってしまっている。時間が有れば整理するべきなのだが。。。
そんな流れに載っていない部品を自動整理するような事が出来れば、アクセスはもっと使い出が有るように思える。
面白い使い方と思ったのが、レポートやフォーム上で見せないデーターをテーブルの替わりに利用して計算や処理を複雑化させられる所だ。これは、慣れないとチェックがし辛いし、流れが追いにくく見え難くなる原因にもなり兼ねないが、エクセルなど表計算を作るのに慣れていないと作れない機能だろう。これは、出力表の最後に紹介した特定中間原料増減表で活躍した機能でもある。作ったのはエクセルなどの裏で計算している部分に近いものになる、エクセルのデバック作業用に裏の動作が見れる物もあったと記憶しているが、無駄に見ても弊害が多いのを理由にして、ここでは詳細は書かない）

最後に開発を振り返って、今までクエリーでやっていた処理も、定型データとなる固定したデータに変えられるようならば、テーブルに置き換える。更に標準化や正規化などしてそのテーブルをデータ変更時のみ追加・削除した方が処理が高速化出来るので、そのへんの見極めが肝心だと思う。モンスターマシンのPCならば、あまり気にならないだろうけど。
アクセスのみで動作と言うのも有りだが、使いやすさや処理の見やすさを追求するとエクセルやMIFESなど向き不向きを考えて作るのも良いと思う。
反論覚悟で言うと、誰も分からないようなソフトを使って開発すると引き継ぎ不可になり兼ねないと何度か言われた事あったが、全体的に高度な処理をしているのでアクセスでもかなり使いこなしていないと意味不明なコマンドが並ぶことになるので、同じ事だと言える。最近のエクセルならば、似たような処理が単独で出来そうだとも思えるが、私の力量や趣味に有っていない事もあり変更はしていない。ここで紹介したプログラム以外で、単純なデータベースに関してだが今では陳腐化してしまって、アクセスでやる必要性がなくなっている物も出ている。昔はエクセルの扱えるデータの限界が小さい容量ということが原因だったのだが、今まではアクセスでやらざるを得なかった容量の数倍の容量でも余裕で扱えるようになっているエクセル。良い環境になったものだ。