テーブルの結合
品目マスタと照合
共通のキーを持つ、複数のファイルを結合する事例を、もう一つ上げます。
この例は、商社が、ユーザから受けた「注文リスト(50,000件)」を、「仕入先」別に仕分けして、「品目コード」単位に数量まとめした「集計リスト」を作成する処理です。
「注文リスト」は、「品目マスタ(15,000件)」と照合して、「単価」や「仕入先」を設定します。
「注文リスト」と「品目マスタ」を、単純に照合すると、50,000件×15,000件／2＝3億7千5百万回 の照合作業が発生します。これでは、いくらパソコンの速度が速くても、それなりの時間がかかってしまいます。
Excelでやろうとすると、マクロを使わないと難しいでしょう。VBAの「連想配列」を使って「品目コード」に索引を付けて処理する方法は「品目マスタと照合」に書きました。
Access を使えば VBA を使わなくても簡単？に、高速で集計できます。
CSV ファイルをインポート
Accessの選択クエリで集計
演算クエリフィールドを設定
Excel ではマクロを使って結合
CSV ファイルをインポート
先に、右の画面のように、「品目マスタ」と「注文リスト」のテーブルを定義しておきます。
このテーブル枠に CSV ファイルをインポートします。
Accessの選択クエリで集計
作成した「注文リスト」は、品目オーダの羅列なので、同一品目で複数のオーダが含まれます。
このため、まず「注文リスト」を品目単位で数量集計します。
クエリーのオブジェクトで、新規作成として、「デザインビュー」を表示します。
対象とする「注文リスト」テーブルを、「追加」します。
デザイン・ビューで「Σ(集計)」を押すと、項目に「集計」行が表示されます。
この場合は、「品目」は「グループ化」のままにして、「数量」で「集計方法」の選択肢から「合計」を指定します。
このクエリに名前を付けて保存します。
クエリのデザイン グリッドの [集計] 行の集計関数とその他のオプションについて
演算クエリフィールドを設定
クエリーのオブジェクトで、新規作成として、「デザインビュー」を表示します。
対象として、テーブルから「品目マスタ」を、クエリから「オーダ集計」を「追加」します。
結合線をマウスの右クリックして表示されるメニューで、「結合プロパティ」を表示させて、「両方のテーブルの結合フィールドが同じ行だけを含める」になっていることを確認します。
作成するクエリで表示する項目を追加していきます。この場合は「調達先」「品目」「数量の合計」です。
既存のテーブルやクエリに存在しない項目を、計算で追加するために、「式ビルダー」を使います。
「式ビルダー」を表示するには、フィールドにカーソルを置いた状態で、
１．右クリックして表示されるメニューで「ビルド」を指定します。もしくは、
２．リボンの「デザイン」から［クエリ設定］グループの［ビルダー］をクリックします。
演算フィールド名を指定せずに数式だけ設定した場合、フィールド名には［式1］など、任意の名前が当てられますが、ここでは、表示したい項目名を指定すべきです。
「式ビルダー」で、表示項目(この場合は「重さ: 」)を入力した後、使う項目と演算子を選択してクエリを完成します。
使わない ≪Expr≫ は削除します。
式ビルダについて★
式ビルダを使用する
式の概要
式の作成について
式ビルダを使用して式を作成する
フォーム、レポート、またはデータ アクセス ページで算術演算を実行する例
数値計算の結果は、書式で「標準」を指定すると、カンマ区切りで数値を表示できます。
「数値」だとカンマ区切りにならないので注意下さい。
結果は、「調達先」順「品目」順にしたいので、「並び替え」で、それぞれ「昇順」を指定します。
後は、クエリの
(実行) を押すだけで、集計結果が表示されます。
<![CDATA[
var win = new Window(Application.getNewId(), {className: "spread", title: "初めてのAccess",
top:500, left:100, width:800, height:560,
resizable: true, url: "JoinTypesAndHowTheyAffectQueryResults_FS.htm",
showEffectOptions: {duration:1}})
win.show();
]]>
Excel ではマクロを使って結合
Excel では、VBA の「連想配列」を使って、「品目コード」に索引を付けて照合 します。
