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

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

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

   Access により、データベースに空のテーブルが新規に追加されます。

   注: 空の新規データベースが開かれている場合は、この手順は必要ありませんが、データベースにテーブルを追加する必要があるときは必ずこの手順どおりに行います。

2. 見出し行の先頭セルをダブルクリックして、サンプル テーブルのフィールド名を入力します。

   Access の既定では、見出し行の空のフィールドには、次のように、[新しいフィールドの追加] というテキストが表示されています。

   

3. 矢印キーを使って (Tab キーを押すか、新しいセルをダブルクリックしてもかまいません)、次の空の見出しセルに移動し、2 つ目のフィールド名を入力します。 すべてのフィールド名を入力するまでこの手順を繰り返します。

4. サンプル テーブルにデータを入力します。

   データを入力する際、各フィールドのデータ型を Access が予測します。 リレーショナル データベースを初めて使用する場合は、テーブルのフィールドごとに、数値、文字列、日付/時刻などのデータ型を設定する必要があります。 データ型を設定すると、データを正確に入力できるだけでなく、計算で電話番号を使用する場合など、誤入力を防ぐことができます。 今回のサンプル テーブルでは、Access にデータ型を予測させます。

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

   (キーボード ショートカット: Ctrl+S)

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

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

   説明セクションのクエリで使用しているため、各サンプル テーブルの名前を使用します。

7. このセクションの最初に記載されている各サンプル テーブルを作成するまで、上記の手順を繰り返します。

データを手動で入力したくない場合は、次の手順に従ってデータをスプレッドシート ファイルにコピーして、スプレッドシート ファイルからデータを Access にインポートしてください。

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

1. スプレッドシート プログラムを開始して、空のファイルを新規に作成します。 Excel を使用する場合は、既定で空のブックが作成されます。

2. 上記の 1 つ目のサンプル テーブルをコピーして、1 つ目のワークシートの先頭のセルから貼り付けます。

3. スプレッドシート プログラムの機能を使って、ワークシートの名前を変更します。 ワークシートにはサンプル テーブルと同じ名前を付けます。 たとえば、サンプル テーブルの名前が Categories の場合は、ワークシートにも同じ名前を付けます。

4. 手順 2 と 3 を繰り返して、各サンプル テーブルすべてを空のワークシートにコピーして、ワークシートの名前を変更します。

   注: ワークシートをスプレッドシート ファイルに追加しなければならないことがあります。 ワークシートの追加については、スプレッドシート プログラムのヘルプを参照してください。

5. ワークシートをコンピューター上またはネットワーク上の便利な場所に保存して、次の手順に進みます。

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

1. [ 外部データ ] タブの [ & リンクのインポート ] グループで、[ 新しいデータ ソース ] > [ファイルから ] > Excel をクリックします。

   [ 外部データの取得 - Excel スプレッドシート ] ダイアログ ボックスが表示されます。

2. [参照] をクリックして、上の手順で作成したスプレッドシート ファイルを開いて、[OK] をクリックします。

   スプレッドシート インポート ウィザードが起動します。

3. ウィザードは既定でワークブックの 1 つ目のワークシート (上のセクションの手順に従った場合は Customers ワークシート) を選択し、ワークシートのデータがウィザード ページの下側に表示されます。 [次へ] をクリックします。

4. ウィザードの次のページで、[先頭行をフィールド名として使う] をクリックして、[次へ] をクリックします。

5. 必要に応じて、次のページの [フィールドのオプション] のテキスト ボックスおよびリストを使って、フィールド名とデータ型を変更するあるいはインポート操作で入力されたフィールドを省略することもできます。 それ以外の場合は、[次へ] をクリックします。

6. [主キーを自動的に設定する] オプションが選択された状態で、[次へ] をクリックします。

7. Access の既定では、ワークシートの名前を新しいテーブルに使用します。 名前をそのまま受け入れるか、別の名前を入力して、[完了] をクリックします。

8. 手順 1 から 7 を繰り返して、ワークブックのすてべのワークシートからテーブルを作成します。

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

1. ナビゲーション ウィンドウで、上の手順で作成したテーブルをひとつひとつ右クリックして、[デザイン ビュー] をクリックします。

2. 各テーブルで、主キー フィールドを見つけます。 Access は既定で、各フィールドに ID という名前を付けます。

3. 各主キー フィールドの [フィールド名] 列で、テーブル名を追加します。

   たとえば、Categories テーブルの ID フィールドの名前を "カテゴリ ID" に、Orders テーブルのフィールドの名前を "Order ID" に変更します。 Order Details テーブルの場合は、フィールドの名前を "詳細 ID" に変更します。 Products テーブルの場合は、フィールドの名前を "Product ID" に変更します。

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

この記事で使用するサンプル テーブルには、必ず主キー フィールドが使用されており、そのフィールド名は上の手順で説明したように名前が変更されています。

ページの先頭へ

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

1. [作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。

2. クエリで使用するテーブルをダブルクリックします。

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

3. クエリで使用したいテーブルのフィールドをダブルクリックします。

   フィールドは、名前や説明など説明的データを含むフィールドも選択できますが、数値または通貨データを含むフィールドは必ず選択しなければいけません。

   各フィールドは、デザイン グリッドのセルに表示されます。

4. [ の実行 ] をクリックしてクエリを実行します。

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

5. 必要に応じて、デザイン ビューに切り替えて、クエリを調整できます。 クエリを調整するには、クエリのドキュメント タブを右クリックして、[デザイン ビュー] をクリックします。 テーブルのフィールドを追加または削除するなどして、適宜クエリを調整します。 フィールドを削除するには、デザイン グリッドで列を選択して、Delete キーを押します。

6. クエリを保存します。

集計行を追加する

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

   または

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

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

   データシートに [集計] 行が新しく追加されます。

3. [集計] 行で、合計を出したいフィールドのセルをクリックして、リストから [合計] を選択します。

集計行を非表示にする

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

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

ページの先頭へ

シンプルな総計を計算する

1. [作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。

2. クエリで使用するテーブルをダブルクリックします。

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

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

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

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

   このようなフィールドで総計を出したい場合は、グリッドに数値フィールドを追加できます。 集計クエリでは、複数の列の総計を計算できます。

4. [ クエリ デザイン ] タブの [ 表示/非表示 ] グループで、[ 合計] をクリックします。

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

5. [集計] 行のセルの値を [合計] に変更します。

6. [ の実行 ] をクリックしてクエリを実行し、結果をデータシート ビューに表示します。

   ヒント: 集計したフィールドの名前の先頭に、Access によって "SumOf" が追加されます。 列の見出しを [送料の総額] のような分かりやすい名前に変更するには、デスクトップ ビューに切り替えて、デザイン グリッドの配送料列の [フィールド] 行でクリックします。 カーソルを [配送料] の隣に移動して、[送料の総額]、続いてコロンを入力して、送料の総額: 配送料] とします。

7. 必要に応じてクエリを保存して、終了します。

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

1. [作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。

2. [注文] テーブルと [注文の詳細] テーブルをダブルクリックします。

3. 受注テーブルの受注日フィールドをクエリのデザイン グリッドの 1 列目に追加します。

4. 1 つ目の列の [抽出条件] 行で、[Date() -1] と入力します。 この式は、計算後の合計から当日のレコードを除外します。

5. 次に、各取引の売上額を計算する列を作成します。 グリッドの 2 つ目の列の [フィールド] 行に次の式を入力します。

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

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

6. [ クエリ デザイン ] タブの [ 表示/非表示 ] グループで、[ 合計] をクリックします。

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

7. 2 つ目の列で、[集計] 行のセルの値を [合計] に変更します。 Sum 関数が、個々の売上高を追加します。

8. [ の実行 ] をクリックしてクエリを実行し、結果をデータシート ビューに表示します。

9. クエリを日次売上として保存します。

   注: 次にクエリをデザイン ビューで開いたときに、売上高値列の [フィールド] と [集計] 行に指定した値が変わっていることがあるかもしれません。 式が Sum 関数の中に入り、[集計] 行は、合計の代わりに式を表示します。

   たとえば、サンプル データを使用して、クエリを作成している場合は (上の手順に従って)、次のようになります。

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

ページの先頭へ

集計クエリを作成する

1. [作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。

2. 操作するテーブルを選択し、[ 追加] をクリックします。

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

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

3. クエリで使用したいテーブルのフィールドをダブルクリックします。

   原則として、クエリにはグループ フィールドと値フィールドのみを追加します。 ただし、値フィールドの代わりに計算を使用できます。次の手順では、その方法について説明します。

   1. 商品テーブルのカテゴリ フィールドをデザイン グリッドに追加します。

   2. グリッドの 2 つ目の列に次の式を入力して、各取引の売上高を計算する列を作成します。

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

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

   3. [ クエリ デザイン ] タブの [ 表示/非表示 ] グループで、[ 合計] をクリックします。

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

   4. 2 つ目の列で、[集計] 行の値を [合計] に変更します。 Sum 関数が、個々の売上高を追加します。

   5. [ の実行 ] をクリックしてクエリを実行し、結果をデータシート ビューに表示します。

   6. 次のセクションのためにクエリを開いたままにしておきます。

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

      今回作成したクエリでは、ベースとして使用したテーブルのすべてのレコードを含めています。 合計を計算するときに除外した注文はなく、すべてのカテゴリの合計を表示しています。

      レコードを部分的に除外する必要がある場合は、クエリに抽出条件を追加できます。 たとえば、$100 未満の取引を無視したり、製品カテゴリの一部のみの合計を計算したりできます。 このセクションの手順では、3 種類の抽出条件の使用方法を説明します。

   7. 合計を計算するときに一部のグループを無視する抽出条件。    たとえば、ビデオ ゲームやアート、フレーミング、スポーツ カテゴリのみの合計を計算します。

   8. 合計の計算後に特定の合計を非表示にする抽出条件。    たとえば、$150,000 を超える合計のみを表示できます。

   9. 個々のレコードを合計から除外する抽出条件。    たとえば、値 (単価 * 数量) が $100 を下回る場合に、個々の売上取引を除外できます。

      次の手順では、抽出条件をひとつずつ追加して、クエリ結果への影響を見ていきます。

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

   10. 上のセクションで使用したクエリをデザイン ビューで開きます。 クエリを調整するには、クエリのドキュメント タブを右クリックして、[デザイン ビュー] をクリックします。

       または

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

   11. [カテゴリ ID] 列の [抽出条件] 行に、=Dolls Or Sports or Art and Framing と入力します。

   12. [ の実行 ] をクリックしてクエリを実行し、結果をデータシート ビューに表示します。

   13. デザイン ビューに戻り、売上高値列の [抽出条件] 行に >100 と入力します。

   14. クエリを実行して結果を確認したら、デザイン ビューに戻ります。

   15. 次に、$100 未満の個々の売上取引を除外する抽出条件を追加します。 そのためには、列をもう 1 つ追加する必要があります。

       注: 売上高値列には 3 つ目の抽出条件を指定できません。 この列に指定する条件はすべて、個々の値ではなく、合計に適用されます。

   16. 式を 2 つ目の列から 3 つ目の列にコピーします。

   17. 新しい列の [集計] 行で、[Where 条件] を選択して、[条件抽出] 行で >20. と入力します。

   18. クエリを実行して結果を確認したら、クエリを保存します。

       注: 次にクエリをデザイン ビューで開いたときに、デザイン グリッドが少し変わっていることに気付くかもしれません。 2 つ目の列で、[フィールド] 行の式が、Sum 関数の中に入り、[集計] 行は、合計の代わりに式を表示します。

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

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

ページの先頭へ

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

1. [作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。

2. クエリで使用するテーブルをダブルクリックします。

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

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

3. クエリ結果で使用したいフィールドをダブルクリックします。

   フィールド名は、それぞれ、クエリ デザイン グリッドの [フィールド] 行にある空白セルに表示されます。

   サンプル テーブルを使用している場合は、受注テーブルから出荷先都市フィールドと出荷日フィールドを追加します。

4. [フィールド] 行の次の空白セルで、次の式をコピーして貼り付けるか、入力します: 売上高: Sum(CCur([受注明細].[単価]*[数量]*(1-[割引])/100)*100)

5. [ クエリ デザイン ] タブの [ クエリの種類 ] グループで、[ クロス集計] をクリックします。

   デザイン グリッドに、[集計] 行と [クロス集計] 行が表示されます。

6. 都市フィールドの [集計] 行のセルをクリックして、[グループ化] を選択します。 出荷日フィールドでも同じ操作を行います。 売上合計フィールドの [集計] セルの値を [式] に変更します。

7. [クロス集計] 行で、都市フィールドのセルを [行見出し] に、出荷日フィールドを [列見出し] に、売上高フィールドに [値] を設定します。

8. [ クエリ デザイン ] タブの [ 結果 ] グループで、[ 実行] をクリックします。

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

ページの先頭へ