Access では無論、表の集計を簡単に表示することができます。 簡単な実例のために、最初に家計簿を作成します。 (この家計簿はあとで「クロス集計」のためにも使用します。)

新規に空のデータベースを作成してから、最初に 次の条件でテーブルをデザインします。

テーブル名を「品目リスト」とし、次のようにレコードを入力します。

出費にはこれ以外のものも当然ありますが、 これはただの試しです。 次に、テーブル「家計簿」を次のようにデザインします。

入力を簡単にするために「品目」のフィールドプロパティーを変更します。 「ルックアップ」(look up) を見てみると、表示コントロールが 次のように「テキストボックス」となっています。

「表示コントロール」の欄の内部をクリックすると、右端に下向きの矢印が 現れます。矢印をクリックすると、次のように選択肢が表示されますから、 コンボボックスを選択します。

すると次のようになります。

「値集合ソース」の欄をクリックすると、 右端にボタンが 2 つ付きます。 最初に下向きの矢印をクリックして、 「品目リスト」テーブルを選択し、 そのあとで ... が付いているボタンを選択し、 クエリビルダを起動します。「品目リスト」テーブルの 「品目」フィールドのみをフィールドに移動します。 これでクエリが作成されたのでクエリ作成を終了します。 その結果、「値集合ソース」の欄には次のような SQL ステートメントが記入されることに なりました。

SELECT 品目リスト.品目 FROM 品目リスト;

この結果、「家計簿」テーブルの「品目」フィールドはコンボボックスになって、 直接書き込む必要がなくなりました。 上の SQL ステートメントは比較的簡単ですから、あえてクエリビルダを 使用せずに直接入力してもかまいません。

以上で、テーブルのデザインは終了です。テーブル名を「家計簿」にして、 次のようにレコードを打ち込んでみます。

さて以上のようにして作成した家計簿で、品目ごとの費用の総計を表示させてみましょう。 そのために、「デザイン ビューでクエリを作成する」を選択して、 「家計簿」テーブルから次のようにクエリをデザインします。

そのあとでメニューの「表示」/集計」を選択します。

するとデザインに次のように「集計」の行が表示されます。

「集計」の行の「費用」の欄をクリックすると、この欄がコンボボックスになっていることが わかります。下向きの矢印をクリックして「合計」を選択します。 次のようになります。

これでデータシートビューに移れば、ちゃんと合計が表示されることがわかります。

「SQL ビュー」でどのような SQL のコマンドが生成されているかも見ておこう。

SELECT 家計簿.品目, Sum(家計簿.費用) AS 費用の合計
FROM 家計簿
GROUP BY 家計簿.品目;

「費用の合計」フィールドの「並べ替え」をクリックすると、 ここがコンボボックスになっていて 「昇順」, 「降順」、「並べ替えなし」 の いずれかを選択できることが分かる。

「並べ替え」を「昇順」、「降順」にして、結果がどうなるか見てみよう。 「並べ替え」を「降順」にした場合の SQL コードは次のようになっている。 (DESC は descend の意味である。)

SELECT 家計簿.品目, Sum(家計簿.費用) AS 費用の合計
FROM 家計簿
GROUP BY 家計簿.品目
ORDER BY Sum(家計簿.費用) DESC;

「デザインビュー」に移動して、「品目」フィールドの検索条件に「雑誌」と 書いて、「データシートビュー」に移動してみると、「品目」の「雑誌」の 項目のみが検索されている。再度、「デザインビュー」に 移動してみると例によって「雑誌」が引用符 " で囲まれているのが分かる。

「SQL ビュー」でどのような SQL のコマンドが生成されているかも見ておこう。

SELECT 家計簿.品目, Sum(家計簿.費用) AS 費用の合計
FROM 家計簿
GROUP BY 家計簿.品目
HAVING (((家計簿.品目)="雑誌"));

「住所録の作成」の「クエリ」で扱った「検索条件」で生成される SQL コードでは WHERE の後ろに条件式が付いていたが、 上の場合には HAVING の後ろに条件式が付いていることに注意しよう。

1. 「家計簿」テーブルをフォームから入力するようにして、「年月日」を指定しない場合に 自動的に現在の「年月日」を設定するようにせよ。
   ヒント

   • 「品目」の更新直後のイベントプロシージャ (VBA) で処理
   • 「年月日」のフィールドが空であるかどうかは次の構文でわかる。

       If Nz([年月日]) = "" Then
     

     Nz をわざわざつけないといけないのは、テキストボックスからの入力値が バリアント型の変数となっているため Null 値である場合に補正して、空の文字列 "" に 変更する必要があるためである。 Visual Basic のバリアント型の変数は C 言語のポインターである。

     ポインターのみ宣言されているとき    ポインターが空の文字列を指定しているとき
     (ポインターは未定義状態)            （ポインターは "" と定義されている)
     
         ptr                                 ptr
                                              │
                                              └→""
     
     

   • Visual Basic では Now は現在の年月日と時刻を示す次のような文字列である。

     "2003/05/15 10:39:45"
     

   • Visual Basic では Year(Now) は現在の年をあらわす文字列である (例えば "2003")。 同様に Month(Now), Day(Now) は現在の月 (例えば "5")、現在の日 (例えば "15") を あらわす文字列である。
   • また

     DateSerial("2003", "5", "15")
     

     とすると、"2003/05/15" のような日付型の文字列を返す。
2. 更に「品目」、「費用」のいずれかの項目が空のときに先に進めないようにせよ。 (レコード更新前のイベントを利用する。)

3. 集計のクエリを変更して、次のような結果になるようにしてみよ。

   

これまでに作成したクエリは選択クエリと呼ばれます。これ以外に 特別なクエリがあります。

アクションクエリは選択クエリによく似ています。 テーブル、フィールド、検索条件を指定する点は選択クエリと同じですが、 レコードを更新する点が選択クエリと違っています。

リレーションシップで作成した成績のテーブル「科目Aテーブル」を 少し変えましょう。試験をしたところ、全員の成績が悪かったので、 1 割下駄を履かせることにしました。 次のようにします。

1. 「クエリ」/「デザインビューでクエリを作成する」を選択。
2. 「科目Aテーブル」を選択し、このテーブルの全フィールドを選択。 (この段階は選択クエリと同じであるから、適当にレコードを検索することも できるが、今はこれはしない。)
3. メニューの「クエリ」/「更新」を選択。
   
4. すると図のように「レコードの更新」の行が追加される。
   
5. 「レーコードの更新」の行の「成績」の欄に

   [成績] * 1.1

   を書き込む。

以上のようにして、「データシートビュー」に移ると、更新されるフィールドが 表示されるが、まだ更新されてはいない。ついでに「SQL ビュー」に移って、 どのような SQL ステートメントが生成されているかも見ておこう。

UPDATE 科目Aテーブル SET 科目Aテーブル.成績 = [成績]*1.1;

以上の作業がすんだら、「科目A更新クエリ」と名前をつけて保存し、いったん終了する。 更新クエリには次のような特別なアイコンがついていることに注意しよう。

ダブルクリックして起動すると、

「更新クエリを実行すると、テーブルのデータが変更されます」

との警告が出る。「はい」を選ぶと、さらに更新されるレコードの件数が 表示され、更新してもよろしいですかと促してくる。 そのまま「はい」を選ぶ。

「科目Aテーブル」を開いて成績が 10 % 増えていることを確認しよう。

既存のテーブルに別のテーブルを付け加える場合には 他のデータベースから取り込むことが多いと思われます。 そこで、今「seiseki.mdb」に「名簿テーブル」が

の条件で次のように作成されているとします。

一方で、「seiseki2.mdb」に「名簿テーブル2」が同じ条件で次のように作成されているとします。

「名簿テーブル2」を「名簿テーブル」に追加することを考えます。

このために、まず「seiseki.mdb」を開いてから、メニューの 「ファイル」/「外部データの取り込み」/「インポート」を選択して、 「seiseki2.mdb」をインポートします。

すると次のような「オブジェクトのインポート」の画面になります。

「名簿テーブル2」のみを選択し、「OK」をクリックします。

さてインポートされた「名簿テーブル2」を「名簿テーブル」に追加するために、 「クエリ」/「デザインビューでクエリを作成する」を選択します。 「名簿テーブル2」を追加して、全てのフィールドを選びます。 以上のクエリを「追加クエリ」に変更するために、 「クエリ」/「追加」を選びます :

すると次のように、追加先の「テーブル名」が要求されます。

右端の下向き矢印をクリックすると、テーブルの選択肢が表示されますから、 「名簿テーブル」を選択し、「OK」をクリックします。

するとクエリのデザインに「レコードの追加」先が表示されることになりました。

アクセスがフィールド名からレコードの追加先を判断したわけですが、 フィールド名が同一でない場合には選びなおす必要があります。 (内部をクリックするとコンボボックスになっていることが分かります。)

どのような SQL ステートメントが生成されているかも見ておこう。

INSERT INTO 名簿テーブル ( 学生番号, 氏名 )
SELECT 名簿テーブル2.学生番号, 名簿テーブル2.氏名
FROM 名簿テーブル2;

この段階で「データシートビュー」に移っても、追加されるテーブルが 表示されるだけである。 「追加クエリ」とでも名前を付けて、一旦終了することにします。 追加クエリの先頭には特別なアイコンがついていることにも注意します。

今作成した追加クエリをダブルクリックして起動します。すると

「更新クエリを実行すると、テーブルのデータが変更されます」

との警告が出る。「はい」を選ぶと、さらに追加されるレコードの件数が 表示され、更新してもよろしいですかと促してくる。 そのまま「はい」を選ぶ。

「名簿テーブル」を開いて、ちゃんと追加されていることを確認しよう。

既存のテーブルから、与えられた条件のレコードを削除するのが 削除クエリですが、今までのクエリ同様に選択クエリの形で 作成を開始します。

前項の追加クエリで追加したレコードを削除してみましょう。 「クエリ」/「デザインビューでクエリを作成する」を選択します。 テーブルとして、「名簿テーブル」を選択し、すべてのフィールドを 追加します。 抽出条件として、学生番号が 0030 より大きいものを指定すれば よいのですが、さて困りました。学生番号は文字列型として設定してあります。 アクセスのヘルプはあまりよくできていないようで、 抽出条件の条件式としてどのようなものが設定できるかなどといった 説明が不足しているように思います。

アクセスはどうせ BASIC の構文を受け付けるであろうと考えて、 次のように「学生番号」フィールドの「抽出条件」に打ち込んでみましょう。

Val([学生番号]) >= 30

Val は 文字列を対応する数値に変換する BASIC の命令です。 (C 言語の atoi に相当します。) なお、「抽出条件」の欄は狭くて、うまく打ち込めないかもしれません。 「抽出条件」の欄に書き込み状態の時に Shift + F2 を押すと次のような大きな窓で書き込めるようになります。

これはズーム ウィンドウと呼ばれます。場合によっては 文字化けしていることがありますが、適当なフォントを選択すると ちゃんと読めるようになります。

出来上がったところで、データシートビューに移行するとちゃんと希望した条件で、 データが検索されています。 デザインビューに戻って、削除クエリに変更するために、 メニューの「クエリ」/「削除」を選択します。

「削除クエリ」と安易な名前をつけて保存し、いったん終了します。 その後、ダブルクリックをして「削除クエリ」を起動します。 例によって、アクセスの警告などがありますが、そのまま実行することにします。 実行後、「名簿テーブル」を開いて、データが削除されていることを確認します。

もう一度削除クエリのデザインを見てみると、次のようになっています。

Val で、新しいフィールドを構成して、そのフィールドに検索条件をつけています。 これが本来の書き方のようです。ついでに SQL ビューも見ておきましょう。 次のようになっています。

DELETE 名簿テーブル.学生番号, 名簿テーブル.氏名, Val([学生番号])
FROM 名簿テーブル
WHERE (((Val([学生番号]))>="30"));

Access の SQL コマンドには Visual Basic の関数 Val も混ぜて使えるようです。

テーブル作成クエリは新規のテーブルを作成するためのクエリです。 基本的には「削除クエリ」などと、同様にして、先に選択クエリを 作成し、それを変更するだけです。 最初から作成するのは面倒なので、「リレーションシップ」/「クエリ」で 作成した「科目Aクエリ」をテーブルにしてみましょう。 (本来重複する内容のテーブルは作らない方がよいのですが、 作ろうとすればいくらでも作ることができます。) その前に「科目Aクエリ」の複製を用意することにします。

データベースウィンドウで、「科目Aクエリ」を指定してから、 メニューの「編集」/「コピー」を選択し、更にメニューの「編集」/「貼り付け」 を選択します。(ファイルコピーの要領です。) すると、次のようにクエリ名を要求されます。

「テーブル作成クエリ」とでも名前をつけましょう。 これを「デザインビュー」で開き、メニューの「クエリ」/「テーブル作成」 を選択します。すると次のように「テーブル名」を要求されます。

「科目Aテーブル2」と安易に名前をつけて、いったん終了します。

作成したクエリをダブルクリックして起動します。 例によって、アクセスの警告などがありますが、そのまま実行することにします。 実際にテーブルが作成されていることを確認しましょう。

最後に作成された SQL コマンドを見てみると次のようになっています。

SELECT 科目Aテーブル.学生番号, 名簿テーブル.氏名, 科目Aテーブル.成績 INTO 科目Aテーブル2
FROM 名簿テーブル INNER JOIN 科目Aテーブル ON 名簿テーブル.学生番号 = 科目Aテーブル.学生番号;

「科目Aクエリ」の SQL コマンドは、次のようになっていましたから 「INTO 科目Aテーブル2」が追加されただけであることがわかります。

SELECT 科目Aテーブル.学生番号, 名簿テーブル.氏名, 科目Aテーブル.成績
FROM 名簿テーブル INNER JOIN 科目Aテーブル ON 名簿テーブル.学生番号 = 科目Aテーブル.学生番号;

次のようなテーブル(「名簿テーブル」と呼ぶ)を考える。

このとき次のようなクエリを順番に作成せよ。

1. 「名簿テーブル」の学生番号 0035 から 0038 までを別のテーブルに保存する「テーブル作成クエリ」の制作
2. 「名簿テーブル」の学生番号 0035 から 0038 までを削除するクエリの制作 (「名簿テーブル」のデータが減る)
3. 1 で作成したテーブルを 2 で出来た「名簿テーブル」に追加するクエリの制作 (「名簿テーブル」が元に戻る)

アクセスに付属の Northwind データベースの「商品別四半期売上高」は クロス集計クエリの実例ですが、大きなテーブルなのですぐにはピンとこないと思います。 そこでもう少し簡単な実例、-- 「集計」の箇所で作成した「家計簿」-- のクロス集計をしてみましょう。

「家計簿」テーブルの品目ごとの費用の総計と、月ごとの総計を求めるクエリを作成することにします。 このクエリが「クロス集計クエリ」です。

クロス集計の縦方向の見出しには「品目」を指定し、 横方向には「月」を指定することにします。但し、「家計簿」テーブルには 直接「月」のフィールドがないので、準備のためのクエリを作成しないといけません。

このためにデザインビューでクエリを作成します。フィールドは「家計簿」の フィールドの「品目」、「費用」のみを選択し、更に最後にもう一つフィールドを 次の条件で作成します。

月: Month([年月日])

コロン : より前の部分が、実際のフィールド名となり、 コロン : より後ろの部分が評価式です。 Month は年月日型の文字列から月を抽出する命令です。 (ほかにも、 Year, Day などがあります。) クエリ名を「家計簿クロス集計準備」として保存します。 データシートビューに移って、ちゃんと月が表示されることを確認します。

さてようやく、家計簿クロス集計のクエリを作成しましょう。 「データベースウィンドウ」の「クエリ」の「新規作成」を選び、「クロス集計クエリウィザード」 を選択します。次のステップでは、もとになるテーブルもしくは クエリの選択となりますから、クエリを選択し、 「家計簿クロス集計準備」を選択し、次に進みます。

これ以降は順番に次の作業となります。

• 行見出しのフィールドの指定。「品目」を選択
• 列見出しのフィールドの指定。「月」を選択
• 集計のフィールドの指定、「費用」。 更に集計方法は「合計」を指定。
• クエリ名として「家計簿クロス集計」として、完了する。

クエリの結果が次のようになっていれば、成功です。

上の実例では、月ごとの小計を出したが、 曜日ごとの小計を出して見よ。