クエリを使用してデータを集計する

ここでは、集計関数という種類の関数を使用して、クエリの結果セットのデータを合計する方法について説明します。また、Count や Avg などの集計関数を使用して、結果セットの値をカウントしたり、平均を計算したりする方法についても簡単に説明します。さらに、クエリのデザインを変更せずにデータを集計する際に使用する、Microsoft Office Access 2007 の新機能である [集計] 行の使用方法について説明します。

目的に合ったトピックをクリックしてください


データを集計する方法を理解する

集計関数という種類の関数を使用すると、クエリで列の数値を集計できます。集計関数は、データ列で計算を実行し、値を 1 つ返します。Access には、Sum、Count、Avg (平均を計算)、Min、Max など、さまざまな集計関数が用意されています。クエリに Sum 関数を追加してデータを合計したり、Count 関数を使用してデータをカウントしたりします。

また、Office Access 2007 では、さまざまな方法で Sum や他の集計関数をクエリに追加できます。以下の操作を行うことができます。

  • クエリをデータシート ビューで開き、[集計] 行を追加します。Office Access 2007 の新機能である [集計] 行を使用すると、クエリのデザインを変更する必要なく、クエリの結果セットの 1 つ以上の列で集計関数を使用できます。
  • 集計クエリを作成します。集計クエリはレコードのグループの小計を計算し、[集計] 行は 1 つ以上のデータ列 (フィールド) の総計を計算します。たとえば、都道府県別または四半期別にすべての売上の小計を計算する場合は、集計クエリを使用して目的のカテゴリ別にレコードをグループ化し、売上高を合計します。
  • クロス集計クエリを作成します。クロス集計クエリとは、Microsoft Office Excel 2007 のワークシートに似たグリッドに結果を表示する特殊な種類のクエリです。クロス集計クエリは、値を集計し、集計結果を行見出し (左端) と列見出し (上部) の 2 種類の項目によってグループ化します。たとえば、次のテーブルに示すように、クロス集計クエリを使用して、この 3 年間の都道府県別の総売上高を表示できます。
都道府県 2003 2004 2005
東京都 254,556 372,455 467,892
神奈川県 478,021 372,987 276,399
千葉県 572,997 684,374 792,571
... ... ... ...

 メモ   このドキュメントの操作方法の各セクションでは、Sum 関数の使用に重点を置いていますが、[集計] 行とクエリで他の集計関数も使用できることに注意してください。その他の集計関数の使用方法の詳細については、後の「集計関数リファレンス」を参照してください。

また、他の集計関数の使用方法の詳細については、「データシートに列の合計を表示する」を参照してください。

次のセクションの各手順では、[集計] 行を追加し、集計クエリを使用して各グループのデータを合計する方法と、クロス集計クエリを使用して、各グループのデータを日付ごとに小計する方法について説明します。操作を行う際には、集計関数の多くは特定のデータ型に設定されたフィールドのデータに対してだけ機能することに注意してください。たとえば、SUM 関数が機能するのは、数値型、10 進型、または通貨型に設定されたフィールドに対してだけです。各関数で必要なデータ型の詳細については、後の「集計関数リファレンス」を参照してください。

データ型全般については、「フィールドのデータ型の設定を修正または変更する」を参照してください。

ページの先頭へ ページの先頭へ

サンプル データを用意する

この記事にある操作方法のセクションには、サンプル データのテーブルが含まれています。各操作手順では、集計関数の機能を理解しやすくするためにサンプル テーブルを使用します。必要に応じて、新規のデータベースまたは既存のデータベースにサンプル テーブルを追加することもできます。

Access では、複数の方法でこれらのサンプル テーブルをデータベースに追加できます。データを手動で入力し、各テーブルを Microsoft Office Excel 2007 などのワークシート プログラムにコピーした後、ワークシートを Access にインポートしたり、データをメモ帳などのテキスト エディタに貼り付け、生成されるテキスト ファイルからデータをインポートしたりできます。

このセクションの手順では、空白のデータシートにデータを手動で入力する方法、サンプル テーブルをワークシート プログラムにコピーする方法、およびそのサンプル テーブルを Access 2007 にインポートする方法について説明します。テキスト データの作成およびインポート方法の詳細については、「テキスト ファイルのデータのインポートとリンクの設定」を参照してください。

この記事の各操作手順では、次に示す各テーブルを使用します。これらのテーブルを使用してサンプル データを作成します。

[商品区分] テーブル

商品区分
果汁飲料
冷凍食品
乳製品
パスタ
シリアル
スナック
アルコール飲料

[商品] テーブル

商品名 単価 商品区分
果汁 100% オレンジ \1,295 果汁飲料
冷凍枝豆 \1,585 冷凍食品
ロッキーマウンテンチーズ \2,250 乳製品
ストロベリーヨーグルト \3,265 冷凍食品
冷凍カニクリームコロッケ \2,295 冷凍食品
伝統スパゲッティ \7,850 パスタ
コーンフレークシュガー \1,488 シリアル
ナイトワイン \3,675 アルコール飲料
じゃがチップス \6,500 スナック
果汁 100% レモン \7,888 果汁飲料
極上パスタ \5,333 パスタ
四角せんべい \7,795 スナック

[受注] テーブル

受注日 出荷日 出荷地 運送料
2005/11/14 2005/11/15 千葉県 \5,500
2005/11/14 2005/11/15 神奈川県 \7,600
2005/11/16 2005/11/17 神奈川県 \8,700
2005/11/17 2005/11/18 千葉県 \4,300
2005/11/17 2005/11/18 東京都 \10,500
2005/11/17 2005/11/18 埼玉県 \11,200
2005/11/18 2005/11/19 山梨県 \21,500
2005/11/19 2005/11/20 静岡県 \52,500
2005/11/20 2005/11/21 山梨県 \19,800
2005/11/20 2005/11/21 東京都 \18,700
2005/11/21 2005/11/22 神奈川県 \8,100
2005/11/23 2005/11/24 千葉県 \9,200

[受注明細] テーブル

受注コード 商品名 商品コード 単価 数量 割引
1 四角せんべい 12 \7,795 9 5%
1 果汁 100% レモン 2 \7,888 4 7.5%
2 コーンフレークシュガー 7 \1,488 6 4%
2 ストロベリーヨーグルト 4 \3,265 8 0
2 伝統スパゲッティ 6 \7,850 4 0
3 冷凍カニクリームコロッケ 5 \2,295 5 15%
4 果汁 100% オレンジ 1 \1,295 2 6%
4 ナイトワイン 8 \3,675 8 4%
5 じゃがチップス 9 \6,500 4 10%
6 ロッキーマウンテンチーズ 3 \2,250 12 6.5%
7 極上パスタ 11 \5,333 6 8%
7 ロッキーマウンテンチーズ 3 \2,250 4 9%

 メモ   通常のデータベースでは、[受注明細] テーブルに含まれるのは "商品コード" フィールドだけであり、"商品名" フィールドは含まれません。このサンプル テーブルでは、データをわかりやすくするために "商品名" フィールドを使用しています。

サンプル データを手動で入力する

  1. [作成] タブの [テーブル] で [テーブル] をクリックします。

新しい空白のテーブルがデータベースに追加されます。

 メモ   新しい空白のデータベースを開く場合はこの手順を実行する必要はなく、データベースにテーブルを追加する必要がある場合にこの手順を実行します。

  1. 見出し行の最初のセルをダブルクリックし、サンプル テーブルのフィールドの名前を入力します。

既定では、この見出し行の空白のフィールドに、次のように文字列 [新しいフィールドの追加] が表示されます。

データシートの新規フィールド

  1. 方向キーを使用して次の空白の見出しセルに移動し、2 番目のフィールド名を入力します (Tab キーを押すか、新しいセルをダブルクリックして移動することもできます)。すべてのフィールド名を入力するまでこの手順を繰り返します。
  2. サンプル テーブルのデータを入力します。

データを入力すると、それぞれのフィールドのデータ型が自動的に設定されます。リレーショナル データベースに慣れていない場合でも、テーブルのフィールドごとに数値型、テキスト型、日付/時刻型など、特定のデータ型を設定する必要があります。データ型が設定されると正確なデータを入力できるようになり、電話番号を計算に使用するなどの間違いも防ぐことができます。このサンプル テーブルの場合、自動的に設定されるデータ型を使用する必要があります。

  1. データの入力が完了したら、[上書き保存] をクリックします。

ショートカット キーの使用  Ctrl キーを押しながら S キーを押します。

[名前を付けて保存] ダイアログ ボックスが表示されます。

  1. [テーブル名] ボックスにサンプル テーブルの名前を入力し、[OK] をクリックします。

操作方法のセクションのクエリでは各サンプル テーブルの名前を使用するため、その名前を使用します。

  1. この手順を繰り返し、このセクションの先頭に表示されている各サンプル テーブルを作成します。

データを手動で入力しない場合は、次の手順に従ってデータをワークシート ファイルにコピーし、ワークシート ファイルから Access にデータをインポートします。

サンプル ワークシートを作成する

  1. ワークシート プログラムを起動し、新しい空白のファイルを作成します。Excel を使用する場合は、新しい空白のブックが既定で作成されます。
  2. 前述の最初のサンプル テーブルをコピーし、1 枚目のワークシートに最初のセルから貼り付けます。
  3. ワークシート プログラムに用意されている方法を使用して、ワークシートの名前を変更します。ワークシートには、サンプル テーブルと同じ名前を付けます。たとえば、サンプル テーブルが「商品区分」という名前の場合、ワークシートにも同じ名前を付けます。
  4. 手順 2. と 3. を繰り返し、各サンプル テーブルを空白のワークシートにコピーした後、ワークシートの名前を変更します。

 メモ   必要に応じて、ワークシートをワークシート ファイルに追加します。追加方法の詳細については、ワークシート プログラムのヘルプを参照してください。

  1. 使用しているコンピュータまたはネットワーク上の適切な場所にブックを保存し、次の一連の手順に進みます。

ワークシートからデータベース テーブルを作成する

  1. [外部データ] タブの [インポート] で [Excel] をクリックします。

または

[その他] をクリックし、一覧からワークシート プログラムを選択します。

[外部データの取り込み - <プログラム名> ワークシート] ダイアログ ボックスが表示されます。

  1. [参照] をクリックし、前の手順で作成したワークシート ファイルを開き、[OK] をクリックします。

ワークシート インポート ウィザードが起動されます。

  1. ウィザードでは、ブック内の最初のワークシートが既定で選択され (前のセクションに示す手順どおりに操作している場合は [商品区分])、ワークシートのデータがウィザードのページの下のセクションに表示されます。[次へ] をクリックします。
  2. ウィザードの次のページで、[先頭行をフィールド名として使う] をクリックし、[次へ] をクリックします。
  3. 必要に応じて、次のページで [フィールドのオプション] のテキスト ボックスと一覧を使用して、フィールド名やデータ型を変更したり、インポート操作からフィールドを除外したりできます。特に必要がない場合は、[次へ] をクリックします。
  4. [主キーを自動的に設定する] をオンの状態のままにし、[次へ] をクリックします。
  5. 既定では、ワークシートの名前が新しいテーブルに適用されます。この名前をそのまま使用するか、別の名前を入力し、[完了] をクリックします。
  6. 手順 1. ~ 7. を繰り返して、ブックの各ワークシートからテーブルを作成します。

主キー フィールドの名前を変更する

 メモ   ワークシートをインポートすると、各テーブルに主キー列が自動的に追加され、この列に既定で "ID" という名前が付けられ、オートナンバー型に設定されます。ここでは、各主キー フィールドの名前を変更する方法について説明します。名前を変更することにより、クエリですべてのフィールドを明確に区別できるようになります。

  1. ナビゲーション ウィンドウで、前の手順で作成した各テーブルを右クリックし、[デザイン ビュー] をクリックします。
  2. 各テーブルで、主キー フィールドを見つけます。各フィールドには、既定で "ID" という名前が付けられています。
  3. 各主キー フィールドの [フィールド名] 列にテーブルの名前を追加します。

たとえば、[商品区分] テーブルの "ID" フィールドの名前を "商品区分コード" に変更し、[受注] テーブルのこのフィールドの名前を "受注コード" に変更します。[受注明細] テーブルでは、"明細コード" に変更します。[商品] テーブルでは "商品コード" に変更します。

  1. 変更内容を保存します。

この記事で使用するサンプル テーブルには、主キー フィールドが必ず含まれており、フィールド名は前の手順に従って変更されています。

ページの先頭へ ページの先頭へ

[集計] 行を使用してデータを合計する

[集計] 行をクエリに追加するには、クエリをデータシート ビューで開き、行を追加してから、使用する集計関数 (Sum、Min、Max、Avg など) を選択します。以下の手順では、基本的な選択クエリを作成し、[集計] 行を追加する方法について説明します。前のセクションで説明したサンプル テーブルを使用する必要はありません。

基本的な選択クエリを作成する

  1. [作成] タブの [その他] で [クエリ デザイン] をクリックします。
  1. [テーブルの表示] ダイアログ ボックスで、クエリで使用する 1 つ以上のテーブルをダブルクリックし、[閉じる] をクリックします。

クエリ デザイナの上の部分に、選択したテーブルがウィンドウとして表示されます。

  1. クエリで使用するテーブル フィールドをダブルクリックします。

名前や説明などの記述データを格納するフィールドを含めてもかまいませんが、数値型または通貨型のデータを格納するフィールドは必ず含める必要があります。

デザイン グリッドのセルごとに 1 つのフィールドが表示されます。

  1. ボタン イメージ (実行) をクリックしてクエリを実行します。

クエリの結果セットがデータシート ビューに表示されます。

  1. 必要に応じて、デザイン ビューに切り替え、クエリを修正します。これを行うには、クエリのドキュメント タブを右クリックし、[デザイン ビュー] をクリックします。テーブル フィールドを必要に応じて追加または削除して、クエリを修正できます。フィールドを削除するには、デザイン グリッドの列を選択し、Del キーを押します。
  2. クエリを保存します。

集計行を追加する

  1. クエリをデータシート ビューで開いていることを確認します。クエリをデータシート ビューで開くには、クエリのドキュメント タブを右クリックし、[データシート ビュー] をクリックします。

または

ナビゲーション ウィンドウでクエリをダブルクリックします。これによりクエリが実行され、データシートに結果が読み込まれます。

  1. [ホーム] タブの [レコード] で [集計] をクリックします。

データシートに新しい [集計] 行が表示されます。

  1. [集計] 行で、合計するフィールドのセルをクリックし、一覧の [合計] をクリックします。

集計行を非表示にする

  • [ホーム] タブの [レコード] で [集計] をクリックします。

集計行の使用方法の詳細については、「データシートに列の合計を表示する」を参照してください。

ページの先頭へ ページの先頭へ

クエリを使用して総計を計算する

総計とは、1 つの列のすべての値の合計です。計算できる総計の種類は次のとおりです。

  • 1 列の値を合計する単純な総計。たとえば、出荷コストの合計を計算できます。
  • 複数の列の値を合計する集計された総計。たとえば、複数の項目の価格に項目の受注数を掛け合わせ、その計算結果を合計して、総売上高を計算できます。
  • 一部のレコードを除いた総計。たとえば、先週の金曜日の総売上高だけを計算できます。

次のセクションの各手順では、各種の総計を作成する方法について説明します。ここでの手順では、[受注] テーブルと [受注明細] テーブルを使用します。

[受注] テーブル

受注コード 受注日 出荷日 出荷地 運送料
1 2005/11/14 2005/11/15 千葉県 \5,500
2 2005/11/14 2005/11/15 神奈川県 \7,600
3 2005/11/16 2005/11/17 神奈川県 \8,700
4 2005/11/17 2005/11/18 千葉県 \4,300
5 2005/11/17 2005/11/18 東京都 \10,500
6 2005/11/17 2005/11/18 埼玉県 \11,200
7 2005/11/18 2005/11/19 山梨県 \21,500
8 2005/11/19 2005/11/20 静岡県 \52,500
9 2005/11/20 2005/11/21 山梨県 \19,800
10 2005/11/20 2005/11/21 東京都 \18,700
11 2005/11/21 2005/11/22 神奈川県 \8,100
12 2005/11/23 2005/11/24 千葉県 \9,200

[受注明細] テーブル

明細コード 受注コード 商品名 商品コード 単価 数量 割引
1 1 四角せんべい 12 \7,795 9 0.05
2 1 果汁 100% レモン 2 \7,888 4 0.075
3 2 コーンフレークシュガー 7 \1,488 6 0.04
4 2 ストロベリーヨーグルト 4 \3,265 8 0.00
5 2 伝統スパゲッティ 6 \7,850 4 0.00
6 3 冷凍カニクリームコロッケ 5 \2,295 5 0.15
7 4 果汁 100% オレンジ 1 \1,295 2 0.06
8 4 ナイトワイン 8 \3,675 8 0.04
9 5 じゃがチップス 9 \6,500 4 0.10
10 6 ロッキーマウンテンチーズ 3 \2,250 12 0.065
11 7 極上パスタ 11 \5,333 6 0.08
12 7 ロッキーマウンテンチーズ 3 \2,250 4 0.09

単純な総計を計算する

  1. [作成] タブの [その他] で [クエリ デザイン] をクリックします。
  1. [テーブルの表示] ダイアログ ボックスで、クエリで使用するテーブルをダブルクリックし、[閉じる] をクリックします。

サンプル データを使用する場合は、[受注] テーブルをダブルクリックします。

クエリ デザイナの上の部分のウィンドウに、テーブルが表示されます。

  1. 合計を計算するフィールドをダブルクリックします。対象のフィールドが数値型または通貨型に設定されていることを確認します。テキスト フィールドなど、数値型以外のフィールドの値を合計しようとすると、クエリの実行時に "抽出条件でデータ型が一致しません。" というエラー メッセージが表示されます。

サンプル データを使用する場合は、[運送料] 列をダブルクリックします。

 メモ   その他の数値型フィールドの総計を計算する場合は、それらのフィールドも追加できます。集計クエリでは、複数の列の総計を計算できます。

  1. [デザイン] タブの [表示/非表示] で [集計] をクリックします。ボタン イメージ

デザイン グリッドに [集計] 行が表示され、[運送料] 列のセルに [グループ化] と表示されます。

  1. [集計] 行のセルの値を [合計] に変更します。
  2. ボタン イメージ (実行) をクリックしてクエリを実行し、結果をデータシート ビューに表示します。

 ヒント   合計を計算したフィールドの名前には、名前の前に "の合計" と自動的に付けられます。列見出しを "運送料総額" のようなわかりやすい名前に変更するには、デザイン ビューに戻り、デザイン グリッドの [運送料] 列の [フィールド] 行内をクリックします。"運送料" の左にカーソルを置いて「運送料総額」と入力し、次にコロン (:) を入力します (「運送料総額: 運送料」)。

  1. 必要に応じて、クエリを保存して閉じます。

一部のレコードを除いた総計を計算する

  1. [作成] タブの [その他] で [クエリ デザイン] をクリックします。
  1. [テーブルの表示] ダイアログ ボックスで、[受注] テーブルと [受注明細] テーブルをダブルクリックし、[閉じる] をクリックしてダイアログ ボックスを閉じます。
  2. クエリ デザイン グリッドの 1 列目に、[受注] テーブルの "受注日" フィールドを追加します。
  3. 1 列目の [抽出条件] 行に、「Date() -1」と入力します。この式により、今日の日付のレコードが集計対象から除外されます。
  4. 次に、各取引の売上高を計算する列を作成します。グリッドの 2 列目の [フィールド] 行に、次の式を入力します。

売上総額: (1-[受注明細].[割引]/100)*([受注明細].[単価]*[受注明細].[数量])

式で参照しているフィールドが数値型または通貨型に設定されていることを確認します。他のデータ型に設定されているフィールドを参照していると、クエリの実行時に "抽出条件でデータ型が一致しません。" というメッセージが表示されます。

  1. [デザイン] タブの [表示/非表示] で [集計] をクリックします。

デザイン グリッドに [集計] 行が表示され、1 列目と 2 列目に [グループ化] と表示されます。

  1. 2 列目の [集計] 行のセルの値を [合計] に変更します。Sum 関数によって各売上高が加算されます。
  2. ボタン イメージ (実行) をクリックしてクエリを実行し、結果をデータシート ビューに表示します。
  3. 「1 日の売上高」という名前を付けてクエリを保存します。

 メモ   このクエリをデザイン ビューで次回開いたときに、[売上総額] 列の [フィールド] 行と [集計] 行に指定した値が若干変更されていることがわかります。式が Sum 関数に含まれた状態で表示され、[集計] 行に [合計] ではなく [式] と表示されます。

たとえば、サンプル データを使用し、(前の手順に従って) クエリを作成した場合、次のように表示されます。

売上総額: Sum((1-[受注明細].割引/100)*([受注明細].単価*[受注明細].数量))

ページの先頭へ ページの先頭へ

集計クエリを使用してグループの合計を計算する

以下の手順では、各グループ データの小計を計算する集計クエリを作成する方法について説明します。操作を行う際には、集計クエリに含めることができるのは、既定で "商品区分"フィールドのようなグループ データを格納する 1 つ以上のフィールドと、"売上高" フィールドのような合計するデータを格納するフィールドだけであることに注意してください。集計クエリに、商品区分の各項目を説明する他のフィールドを含めることはできません。説明データを表示する場合は、集計クエリのフィールドを追加のデータ フィールドに結合する 2 つ目の選択クエリを作成します。

以下の手順では、各商品の総売上高を特定するために必要な集計クエリと選択クエリを作成する方法について説明します。ここでの手順は、次のサンプル テーブルを使用することを前提としています。

[商品] テーブル

商品コード 商品名 単価 商品区分
1 果汁 100% オレンジ \1,295 果汁飲料
2 冷凍枝豆 \1,585 冷凍食品
3 ロッキーマウンテンチーズ \2,250 乳製品
4 ストロベリーヨーグルト \3,265 乳製品
5 冷凍カニクリームコロッケ \2,295 冷凍食品
6 伝統スパゲッティ \7,850 パスタ
7 コーンフレークシュガー \1,488 シリアル
8 ナイトワイン \3,675 アルコール飲料
9 じゃがチップス \6,500 スナック
10 果汁 100% レモン \7,888 果汁飲料
11 極上パスタ \5,333 パスタ
12 四角せんべい \7,795 スナック

[受注明細] テーブル

明細コード 受注コード 商品名 商品コード 単価 数量 割引
1 1 四角せんべい 12 \7,795 9 5%
2 1 果汁 100% レモン 2 \7,888 4 7.5%
3 2 コーンフレークシュガー 7 \1,488 6 4%
4 2 ストロベリーヨーグルト 4 \3,265 8 0
5 2 伝統スパゲッティ 6 \7,850 4 0
6 3 冷凍カニクリームコロッケ 5 \2,295 5 15%
7 4 果汁 100% オレンジ 1 \1,295 2 6%
8 4 ナイトワイン 8 \3,675 8 4%
9 5 じゃがチップス 9 \6,500 4 10%
10 6 ロッキーマウンテンチーズ 3 \2,250 12 6.5%
11 7 極上パスタ 11 \5,333 6 8%
12 7 ロッキーマウンテンチーズ 3 \2,250 4 9%

以下の手順では、[商品] テーブルと [受注明細] テーブルの "商品コード" フィールド間に一対多リレーションシップが定義されており、[商品] テーブルがリレーションシップの "一" 側であることを前提としています。

集計クエリを作成する

  1. [作成] タブの [その他] で [クエリ デザイン] をクリックします。
  1. [テーブルの表示] ダイアログ ボックスで、使用するテーブルを選択し、[追加] をクリックします。テーブルの追加が完了したら、[閉じる] をクリックします。

または

使用するテーブルをダブルクリックし、[閉じる] をクリックします。クエリ デザイナの上の部分に、各テーブルがウィンドウとして表示されます。

上のサンプル テーブルを使用する場合は、[商品] テーブルと [受注明細] テーブルを追加します。

  1. クエリで使用するテーブル フィールドをダブルクリックします。

原則として、グループ フィールドと値フィールドだけをクエリに追加します。ただし、次の手順で説明するように、値フィールドの代わりに計算フィールドを使用できます。

  1. デザイン グリッドに [商品] テーブルの "商品区分" フィールドを追加します。
  2. グリッドの 2 列目に次の式を入力して、各取引の売上高を計算する列を作成します。

売上総額: (1-[受注明細].[割引]/100)*([受注明細].[単価]*[受注明細].[数量])

式で参照しているフィールドが数値型または通貨型であることを確認します。他のデータ型のフィールドを参照していると、データシート ビューに切り替えようとしたときに、"抽出条件でデータ型が一致しません。" というエラー メッセージが表示されます。

  1. [デザイン] タブの [表示/非表示] で [集計] をクリックします。

デザイン グリッドに [集計] 行が表示され、1 列目と 2 列目に [グループ化] と表示されます。

  1. 2 列目の [集計] 行の値を [Sum] に変更します。Sum 関数によって、各売上高が加算されます。
  2. ボタン イメージ (実行) をクリックしてクエリを実行し、結果をデータシート ビューに表示します。
  3. 次のセクションで使用するため、クエリは開いたままにしておきます。

集計クエリで抽出条件を使用する

前のセクションで作成したクエリには、基になるテーブルのすべてのレコードが含まれます。集計時に除外される受注レコードはなく、すべての商品区分の集計が表示されます。

一部のレコードを除外する必要がある場合は、抽出条件をクエリに追加できます。たとえば、10,000 円未満の取引を無視したり、一部の商品区分だけを集計したりできます。以下の説明では、次の 3 種類の抽出条件を使用する方法について説明します。

  • 集計時に特定のグループを無視する抽出条件     たとえば、パスタ、乳製品、アルコール飲料の各商品区分の集計だけを行います。
  • 集計後に特定の集計結果を非表示にする抽出条件     たとえば、15,000,000 円を上回る集計結果だけを表示できます。
  • 個々のレコードを集計に含めないようにする抽出条件     たとえば、(単価 * 数量) の値が 10,000 円を下回る個々の販売取引を除外できます。

以下の手順では、抽出条件を 1 つずつ追加し、クエリの結果への影響を確認します。

抽出条件をクエリに追加する

  1. 前のセクションのクエリをデザイン ビューで開きます。これを行うには、クエリのドキュメント タブを右クリックし、[デザイン ビュー] をクリックします。

または

ナビゲーション ウィンドウで、クエリを右クリックし、[デザイン ビュー] をクリックします。

  1. [商品区分] 列の [抽出条件] 行に、「=果汁飲料 Or アルコール飲料 Or 乳製品」と入力します。
  2. ボタン イメージ (実行) をクリックしてクエリを実行し、結果をデータシート ビューに表示します。
  3. デザイン ビューに切り替え、[売上総額] 列の [抽出条件] 行に、「>15000000」と入力します。
  4. クエリを実行して結果を確認したら、デザイン ビューに切り替えます。
  5. 今度は、10,000 円未満の各販売取引を除外する抽出条件を追加します。これを行うには、もう 1 列追加する必要があります。

 メモ   [売上総額] 列にこの 3 つ目の抽出条件を指定することはできません。この列に指定した抽出条件は、個々の値ではなく、合計値に適用されます。

  1. 2 列目の式を 3 列目にコピーします。
  2. 新しい列の [集計] 行で [Where] をクリックし、[抽出条件] 行に「>10000」と入力します。
  3. クエリを実行して結果を確認したら、クエリを保存します。

 メモ   このクエリをデザイン ビューで次回開いたときに、デザイン グリッドの内容が若干変更されていることがわかります。2 列目の [フィールド] 行の式が Sum 関数に含まれた状態で表示され、[集計] 行に [合計] ではなく、[式] と表示されます。

売上総額: Sum((1-[受注明細].割引/100)*([受注明細].単価*[受注明細].数量))

また、4 列目が表示されます。この列は 2 列目のコピーですが、2 列目に指定した抽出条件は、実際には新しい列の一部として表示されます。

ページの先頭へ ページの先頭へ

クロス集計クエリを使用して複数のグループ間のデータを合計する

クロス集計クエリとは、Excel ワークシートに似たグリッドに結果を表示する特殊な種類のクエリです。クロス集計クエリでは、値を集計し、その結果を行見出し (左端) と列見出し (上部) の 2 種類の項目によってグループ化します。次の図は、サンプル クロス集計クエリの結果セットの一部です。

サンプル クロス集計クエリ

操作を行う際には、クロス集計クエリで使用するテーブルにすべてのデータ ポイントの値が必ずしも含まれているわけではないため、結果セットにすべてのフィールドが常に取り込まれるわけではないことに注意してください。

クロス集計クエリを作成するときには、通常、複数のテーブルのデータを含め、必ず 3 種類のデータ (行見出しとして使用するデータ、列見出しとして使用するデータ、合計やその他の計算の対象となる値) を含めます。

以下の手順は、次のテーブルを前提としています。

[受注] テーブル

受注日 出荷日 出荷地 運送料
2005/11/14 2005/11/15 千葉県 \5,500
2005/11/14 2005/11/15 神奈川県 \7,600
2005/11/16 2005/11/17 神奈川県 \8,700
2005/11/17 2005/11/18 千葉県 \4,300
2005/11/17 2005/11/18 東京都 \10,500
2005/11/17 2005/11/18 埼玉県 \11,200
2005/11/18 2005/11/19 山梨県 \21,500
2005/11/19 2005/11/20 静岡県 \52,500
2005/11/20 2005/11/21 山梨県 \19,800
2005/11/20 2005/11/21 東京都 \18,700
2005/11/21 2005/11/22 神奈川県 \8,100
2005/11/23 2005/11/24 千葉県 \9,200

[受注明細] テーブル

受注コード 商品名 商品コード 単価 数量 割引
1 四角せんべい 12 \7,795 9 5%
1 果汁 100% レモン 2 \7,888 4 7.5%
2 コーンフレークシュガー 7 \1,488 6 4%
2 ストロベリーヨーグルト 4 \3,265 8 0
2 伝統スパゲッティ 6 \7,850 4 0
3 冷凍カニクリームコロッケ 5 \2,295 5 15%
4 果汁 100% オレンジ 1 \1,295 2 6%
4 ナイトワイン 8 \3,675 8 4%
5 じゃがチップス 9 \6,500 4 10%
6 ロッキーマウンテンチーズ 3 \2,250 12 6.5%
7 極上パスタ 11 \5,333 6 8%
7 ロッキーマウンテンチーズ 3 \2,250 4 9%

以下の手順では、総売上高を都道府県別にグループ化するクロス集計クエリを作成する方法について説明します。このクエリでは、2 つの式を使用して書式設定された日付と売上高を返します。

クロス集計クエリを作成する

  1. [作成] タブの [その他] で [クエリ デザイン] をクリックします。
  1. [テーブルの表示] ダイアログ ボックスで、クエリで使用するテーブルをダブルクリックし、[閉じる] をクリックします。

クエリ デザイナの上の部分に、各テーブルがウィンドウとして表示されます。

サンプル テーブルを使用する場合は、[受注] テーブルと [受注明細] テーブルをダブルクリックします。

  1. クエリで使用するフィールドをダブルクリックします。

デザイン グリッドの [フィールド] 行の空白のセルに各フィールド名が表示されます。

サンプル テーブルを使用する場合は、[受注] テーブルの "出荷地" フィールドと "出荷日" フィールドを追加します。

  1. [フィールド] 行の次の空白のセルに、次の式をコピーして貼り付けるか、この式を入力します。総売上高: Sum(CCur([受注明細].[単価]*[]*(1-[割引])/100)*100)
  2. [デザイン] タブの [クエリの種類] で [クロス集計] をクリックします。

デザイン グリッドに、[集計] 行と [行列の入れ替え] 行が表示されます。

  1. "出荷地" フィールドの [集計] 行のセルをクリックし、[グループ化] をクリックします。"出荷日" フィールドについても同じ操作を行います。"総売上高" フィールドの [集計] セルの値を [] に変更します。
  2. [行列の入れ替え] 行で、"出荷地" フィールドのセルを [行見出し] に、"出荷日" フィールドのセルを [列見出し] に、"総売上高" フィールドのセルを [] にそれぞれ設定します。
  3. [デザイン] タブの [結果] で [実行] をクリックします。

クエリの結果がデータシート ビューに表示されます。

ページの先頭へ ページの先頭へ

集計関数リファレンス

次の表は、[集計] 行とクエリで使用できる集計関数の一覧と説明です。Access では、[集計] 行で使用できる集計関数よりも、クエリで使用できる集計関数の方が多いことに注意してください。また、Access プロジェクト (Microsoft SQL Server データベースに接続する Access フロント エンド) を使用する場合は、SQL Server に用意されたさらに多くの集計関数を使用できます。一連の関数の詳細については、Microsoft SQL Server Books Online を参照してください。

関数 説明 使用できるデータ型
Avg 列の平均値を計算します。列のデータは数値、通貨、日付/時刻です。Null 値は無視されます。 数値型、通貨型、日付/時刻型
Count 列内の項目をカウントします。

複数値リストの列など、複雑な繰り返しスカラー データを除くすべてのデータ型。

複数値リストの詳細については、記事「複数値を持つフィールドの概要」および「複数の値を格納するルックアップ フィールドを追加または変更する」を参照してください。

Max 値が最も大きい項目を返します。テキスト データの場合は、アルファベット順の最後の値が最大値になります。Access では、大文字と小文字は区別されません。Null 値は無視されます。  数値型、通貨型、日付/時刻型
Min 値が最も小さい項目を返します。テキスト データの場合は、アルファベット順の最初の値が最小値になります。Access では、大文字と小文字は区別されません。Null 値は無視されます。  数値型、通貨型、日付/時刻型
STDEV

値が平均値からどれくらい分散しているかを測定します。

この関数の使用方法の詳細については、「データシートに列の合計を表示する」を参照してください。

数値型、通貨型
Sum 列の項目を加算します。数値型および通貨型のデータだけで使用できます。 数値型、通貨型
Var

列のすべての値の統計的な分散を測定します。数値型および通貨型のデータだけで使用できます。テーブルの行が 1 行しかない場合は、Null 値を返します。

分散関数の詳細については、「データシートに列の合計を表示する」を参照してください。

数値型、通貨型

ページの先頭へ ページの先頭へ

 
 
適用対象:
Access 2007