ページの最終校正年月日 :
6. 集計

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

家計簿の作成

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

フィールド名データ型その他
ID オートナンバー主キー
品目 テキスト型  

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

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

フィールド名データ型その他
ID オートナンバー主キー
年月日 日付/時刻型  
品目 テキスト型  
費用 数値型  
備考 テキスト型  

入力を簡単にするために「品目」のフィールドプロパティーを変更します。 「ルックアップ」(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
                                               │
                                               └→""
      
      
      メモリー上では文字列は、各文字に対応する文字コードが並び、その後ろに文字列の終わりを示すために 0 がセットされる。
    • Visual Basic では Now は現在の年月日と時刻を示す次のような文字列である。
      "2009/05/15 10:39:45"
      
    • Visual Basic では Year(Now) は現在の年をあらわす文字列である (例えば "2009")。 同様に Month(Now), Day(Now) は現在の月 (例えば "5")、現在の日 (例えば "15") を あらわす文字列である。
    • また
      DateSerial("2009", "5", "15")
      
      とすると、"2009/05/15" のような日付型の文字列を返す。
  2. 更に「品目」、「費用」のいずれかの項目が空のときに 「品目、費用のいずれかの項目が空白です」という警告メッセージを出して、 先に進めないようにせよ。 (レコード更新前のイベントを利用する。)
    ヒント
    次のどちらの構文も動作する。
    If Nz([品目]) = "" Or Nz([費用]) = "" Then
        ......
    
    If Nz([品目]) = "" Or Nz([費用]) = 0 Then
        ......
    
  3. 集計のクエリを変更して、次のような結果になるようにしてみよ。

7. 特別なクエリ

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

アクションクエリ (action query)

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

更新クエリ

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

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

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

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

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

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

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

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

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

追加クエリ

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

テーブル名フィールド名データ型サイズIME 入力モードその他
名簿テーブル 学生番号テキスト型4 オフ主キー
氏名 テキスト型10 オン  

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

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

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


このために、まず「成績.accdb」を開いてから、 「外部データ」タグの 「インポート」の「Access」] を選びます。

用語

次の画面になるので、「参照」ボタンを押して「成績2.accdb」を選択します。

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

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


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

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

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

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

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

注意
  1. 「追加クエリ」は「追加したいデータ」を表示しておいてから、「追加先」を選択するだけです。 順番を間違えないように。
  2. この実例では、テーブルの全てのデータを別のテーブルに追加しましたが、条件を付けてデータを表示して、 それを別のテーブルに「追加」することもできます。つまり、どのようなクエリも「追加クエリ」 とすることができる。

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

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

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

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

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

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

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

削除クエリ

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

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

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

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

Val は 文字列を対応する数値に変換する BASIC の命令です。 (C 言語の atoi に相当します。) なお、「抽出条件」の欄は狭くて、うまく打ち込めないかもしれません。 「抽出条件」の欄に書き込み状態の時に右クリックして表示されるメニューから 「ズーム」 を選ぶと次のような大きな窓で書き込めるようになります。

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

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

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

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 で出来た「名簿テーブル」に追加するクエリの制作 (「名簿テーブル」が元に戻る)
以上のクエリを順番に実行すると、最終的にもとのテーブルに復元すること。

クロス集計クエリ

「集計」の箇所で作成した「家計簿」 のクロス集計をしてみましょう。

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

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

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

月: Month([年月日])

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

さてようやく、家計簿クロス集計のクエリを作成しましょう。 「作成」タブの「クエリウィザード」を選びます。

すると次が表示されるので「クロス集計クエリウィザード」を選び、「OK」します。

すると次のように、もとになるテーブルもしくは クエリの選択となりますから、クエリを選択し、 「家計簿クロス集計準備」を選択し、次に進みます。

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

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

課題

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

ヒント