ブログを報告する. Access の集計データを読みやすく、理解しやすくするには、クロス集計クエリの使用を検討してください。 クロス集計クエリは、合計や平均などの集計関数を計算し、集計結果を 2 セットの値に分類してデータシートの行と列の両方向に表示します。  ところで画像は省略しますが、単に「"個人"」と指定すると、列見出しはその1つしか表示されません。つまり、個人に係る(売上)金額の合計しか表示されず、ほかの分類の集計結果は表示されません。単に並べ替えというだけでなく、指定した列見出し(分類)だけを表示させることもできる、ということです。, ちなみにSQLビューで見るとこうなります。 こちら側の 1 列、2 列、または 3 列には行見出しが格納されます。 行見出しとして使用するフィールドの名前が、これらの列の 1 行目に表示されます。, 2. これらの行の設定により、フィールドが行見出し、列見出し、または集計値のいずれになるかが決まります。, [作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。, [テーブルの表示] ダイアログ ボックスで、レコード ソースとして使用するそれぞれのテーブルまたはクエリをダブルクリックします。, 複数のレコード ソースを使用する場合は、テーブルまたはクエリが共通するフィールドで結合されていることを確認します。 テーブルとクエリを結合する方法の詳細については、[参照] セクションを参照してください。, クエリ デザイン ウィンドウで、行見出しのソースとして使用する各フィールドをダブルクリックします。 行見出しにはフィールドを 3 つまで選択できます。, クエリ デザイン グリッドの、それぞれの行見出しフィールドに対する [クロス集計] 行で [行見出し] をクリックします。, [抽出条件] 行に式を入力して、フィールドの結果を制限することができます。 [並べ替え] 行を使用して、フィールドの並べ替え順序を指定することもできます。, クエリ デザイン ウィンドウで、列見出しのソースとして使用するフィールドをダブルクリックします。 列見出しには、1 つのフィールドのみを選択できます。, クエリ デザイン グリッドの、列見出しフィールドに対する [クロス集計] 行で [列見出し] をクリックします。, [抽出条件] 行に式を入力して、列見出しフィールドの結果を制限することができます。 ただし、列見出しフィールドに条件式を使用しても、クロス集計クエリから返される列の数は制限されません。 代わりに、データが含まれる列が制限されます。 たとえば、赤、緑、および青の 3 つの値を使用できる列見出しフィールドがあるとします。 列見出しフィールドに条件 ='青' を適用した場合、クロス集計に赤の列と緑の列は表示されますが、データは青の列にのみ格納されます。, 列見出しとして表示する値を制限する場合は、クエリの [列見出し] プロパティを使用して、固定値のリストを指定することができます。 詳細については、次のセクションを参照してください。, クエリ デザイン ウィンドウで、集計値の計算に使用するフィールドをダブルクリックします。 集計値に使用するフィールドを 1 つだけ選択することができます。, クエリ デザイン グリッドの、集計値に対する [集計] 行で、値の計算に使用する集計関数を選択します。, 列見出しに使用する固定値を指定する場合は、クエリの "列見出し" プロパティを設定します。, プロパティ シートで、[標準] タブのすぐ上の [選択の種類] が [クエリ プロパティ] になっていることを確認します。 そうでない場合は、クエリ デザイン グリッドの上のスペースで空白の場所をクリックします。, プロパティ シートの [標準] タブの "列見出し" プロパティで、列見出しとして使用するカンマ区切りの値のリストを入力します。, 一部の文字 (ほとんどの句読点など) は、列見出しに使用できません。 これらの文字を値のリストに使用すると、該当する文字がそれぞれアンダースコア (_) に置き換えられます。, クロス集計クエリは、SQL では TRANSFORM ステートメントとして表されます。 TRANSFORM ステートメントの構文は、次のとおりです。, 変換aggfunctionselect 文ピボット テーブルのピボット [IN (値 1[、値 2[,...])], [SQL ビュー] では、テーブルまたはクロス集計クエリのレコード ソースとして使用できるクエリの数が制限されません。 ただし、すべてのクロス集計クエリを使用するデータを返す選択クエリを作成して、デザインはシンプルすることができます、レコード ソースとしてクエリを選択し、使用しています。 選択クエリの作成方法については、「関連項目」セクションを参照してください。, [作成] タブの [その他] グループで、[クエリ デザイン] をクリックします。, [デザイン] タブの [ビュー] グループで、[ビュー] をクリックし、[SQL ビュー] をクリックします。, 1 行目の TRANSFORM の後に、集計値の計算に使用する式を入力します (例: Sum([Amount]))。, 複数のテーブルまたはクエリをレコード ソースとして使用している場合は、各フィールド名にテーブル名またはクエリ名を含めます (例: Sum([Expense]. クエリ. Microsoft Access のクロス集計クエリでは、値としてフィールドまたは計算結果を 1 つだけ指定することができますが、クエリで複数の値を表示することが必要な場合もあります。 以下の例では、会社ごとの受注数および受注総額を示す 2 列の情報を、年ごとに表示しています。 Company Name … [Expense_Class])。, 既定では、ORDER BY 句による値の並べ替え順は昇順です。 降順で並べ替える場合は、フィールド名または式の後に「DESC」と入力します。, 並べ替えるフィールドまたは式を追加する場合は、カンマを入力してから、追加のフィールド名または式を入力します。 並べ替えの順序は、ORDER BY 句でのフィールドまたは式の並び順になります。, クロス集計クエリの SQL ビューで PIVOT 句の最後に「IN」と入力し、その後に列見出しとして使用する (かっこで囲まれた) 値リストをコンマで区切って入力します。 たとえば、「IN (2007, 2008, 2009, 2010)」と入力すると、2007、2008、2009、2010 という 4 つの列見出しが生成されます。, pivot フィールドのフィールド値と一致しない固定値を指定すると、その固定値は、空の列の列見出しになります。, 追加の条件を使用する場合は、AND 演算子と OR 演算子を使って、WHERE 句を拡張できます。 また、かっこを使って、条件を論理セットにグループ化することもできます。, 行見出しまたは列見出しのそれぞれの値を使う代わりに、フィールド値を範囲でグループ化して、その範囲を行見出しまたは列見出しで使用する場合があります。 たとえば、列見出しに "年齢" フィールドを使用するとします。 そのような場合は、それぞれの年齢の列を使用するよりも、年齢層を表す列を使用する方が適しています。, 式で IIf 関数を使用することにより、行または列の見出しに使用する範囲を作成できます。, ヒント: 日付/時刻フィールドで期間を作成する場合は、クロス集計クエリ ウィザードの使用を検討してください。 ウィザードを使用すると、日付を [年]、[四半期]、[月]、[日]、[日付/時刻] の期間にグループ化できるようになります。 これ以外の期間が必要な場合は、デザイン ビューでクロス集計クエリを作成し、このセクションで説明する手法を使用して、必要な期間を作成する必要があります。, クエリ デザイン グリッドの [フィールド] 行で、空白の列を右クリックし、ショートカット メニューの [ズーム] をクリックします。, [ズーム] ボックスに、フィールドの別名と、その後にコロン (:) を入力します。, IIf の後に続くかっこの中に、フィールド値の 1 つ目の範囲を定義する比較式を入力します。, たとえば、"年齢" フィールドの範囲を作成していて、各範囲を 20 歳にするとします。 最初の範囲の比較式は [年齢]<21 です。, 比較式の後にカンマを入力し、範囲の名前を引用符で囲んで入力します。 指定する名前は、その範囲にある値のクロス集計の見出しになります。, たとえば、[年齢]<21 の後にカンマを入力し、次に「"0 ~ 20 歳"」と入力します。, 別の範囲を作成するには、「IIf()」と入力して、手順 5、6、7 を繰り返します。, たとえば、"年齢" フィールドを 20 歳の範囲に区切る、完全に入れ子になった IIf 式は、次のようになります (読みやすいように改行が追加されています)。, 注: 式の評価は、IIf ステートメントのいずれかが True と評価されると、すぐに中止されます。 それぞれの範囲の下限を指定する必要はありません、指定された範囲を下回る値はすべて True と評価されるためです。, [クロス集計] 行で、範囲を行見出しまたは列見出しとして使用するかどうかを指定します。 行見出しは 1 つから 3 つ、列見出しは 1 つ指定することができます。, クロス集計クエリの実行時に入力を求めるようにしたい場合があります。 たとえば、複数の行見出しを使用していて、その 1 つが [国/地域] であるとします。 その場合、すべての国または地域のデータを毎回表示するよりも、クエリで名前の入力を求め、ユーザーの入力に従ってデータを表示することができます。, 注: 列見出しフィールドにパラメーター プロンプトを追加することもできますが、これによって表示される列を制限することはできません。, ユーザーの入力を求める行見出しフィールドの [抽出条件] 行に、質問文を角かっこで囲んで入力します。 質問文はクエリの実行時にプロンプトとして表示されます。, たとえば、[抽出条件] 行に、「[国または地域を入力してください。]」と入力すると、クエリが実行されたときにダイアログ ボックスが表示され、その中に "国または地域を入力してください。" という質問、入力ボックス、および [OK] ボタンが表示されます。, ヒント: 柔軟なパラメーターを設定する場合は、 Like演算子を使用して、式でワイルドカードを連結します。 使用してではなく、たとえば、 [国または region?] クロス集計クエリの列見出しの順序は昇順か降順しか指定できないように見えますが、実は「クエリ列見出し」プロパティにより任意の順序を指定することができます。  お気づきの通り、末尾のIn句が「クエリ列見出し」プロパティに対応しており、これを直接記入する(書き換える)ことで指定することもできます。, 最後にですが、列名の指定を間違えてしまうと、指定したとおりの列見出しは表示されるものの、その列には何の値も表示されません。不自然なので普通は気付くとは思いますけども、注意が必要です。, accs2014さんは、はてなブログを使っています。あなたもはてなブログをはじめてみませんか?, Powered by Hatena Blog [, valueifnull ] ), オプション (クエリで使用されている場合を除く)。 variant 引数が NULL の場合に返す値を提供する Variant です。 この引数を使用すると、0 または長さが 0 の文字列以外の値を返すことができます。, 注: 引数 valueifnull を使用せずに、クエリの式で Nz 関数を使用すると、NULL 値を含むフィールドは長さが 0 の文字列になります。, デザイン ビューでクエリを開いた状態で、クエリ デザイン グリッドの "値" フィールドを右クリックします。, [ズーム] ボックスで、フィールド名または式をかっこで囲み、かっこの前に「Nz」と入力します。, たとえば、Nz と "失われた時間" という名前のフィールドを使用して NULL 値をゼロに変換する場合、入力する式は次のようになります。, 簡潔に記述する    クロス集計クエリは、行の組み合わせ数が増えると読みにくくなる可能性があります。 必要以上に行見出しを使用しないでください。, 手順を追ってクロス集計を作成することを検討する    テーブルのみを使おうとして考えを限定しないでください。 集計クエリを作成し、そのクエリをクロス集計クエリのレコード ソースとして使用して開始することもできます。, 列見出しフィールドは慎重に選択する    クロス集計データシートは、列見出しの数が比較的少ない方が読みやすくなります。 見出しとして使用するフィールドを確認したら、列見出しとして作成される値が最も少ないフィールドを選択します。 たとえば、クエリで年齢と性別による値を計算する場合、通常、性別の方が年齢よりも値が少ないため、年齢ではなく性別を列見出しに使用します。, サブクエリを WHERE 句で使用します。    サブクエリを使用すると、クロス集計クエリを WHERE 句の一部としてことができます。. variant [Year])。, SQL ビューでクロス集計クエリに並べ替え順序を追加するには、ORDER BY 句を使います。, 新しい行で、「ORDER BY」と入力し、その後に 1 文字分の空白を入力します。, 並べ替えるフィールド名または式を入力します (例: ORDER BY [Expense]. | のような &"*"にして、パラメーターの入力と一致します。 ようなを使用しても、パラメーター プロンプトの外観は変更されません。, [クエリ パラメーター] ダイアログ ボックスの [パラメーター] 列に、[抽出条件] 行で使用したのと同じパラメーター プロンプトを入力します。 角かっこも含めますが、連結したワイルドカードや Like 演算子は含めないでください。, [データ型] 列で、各パラメーターのデータ型を選択します。 このデータ型は、行見出しフィールドのデータ型と一致する必要があります。, クロス集計クエリの集計値を計算するために使用するフィールドに NULL 値が含まれている場合、その値は使用するすべての集計関数で無視されます。 一部の集計関数では、結果に影響が出る可能性があります。 たとえば、平均を計算するには、すべての値を加算し、その結果を値の数で除算します。 ただし、フィールドに NULL 値が含まれている場合、その NULL 値は、値の数にカウントされません。, 場合によっては、集計計算で値がカウントされるように、すべての NULL 値をゼロに置き換える方が望ましいこともあります。 NULL 値をゼロに置き換えるには、Nz 関数を使用します。, Nz 売り上げのデータ等、蓄積したデータを月毎や四半期毎、年毎に集計して結果を確認したい場合に、クロス集計が有効です。今回はクエリウィザードを用いた、クロス集計方法について紹介します。, 今回は、売り上げのデータを月次展開する方法を見るために、表1に示すサンプルテーブルを作成し、「売上実績」という名称をつけます。このテーブルでは、開始日から終了日までの期間におけるある品目の売上データを登録したものとします。また、サンプルデータはレコード数が多いため、ダウンロードできるようにファイルを添付しています。参考として一部データを表2に示します。, サンプルデータの場合、レコード単位である期間・ある品目での売り上げを確認することはできますが、品目別の月次売上を確認することが煩雑となります。そこで、行見出しに品目、列見出しに月、各値に売上を示したクロス集計を行ってみましょう。, まずは、「作成」タブの「クエリウィザード」アイコンをクリックします。続いて、「クロス集計クエリウィザード」を選択して「OK」をクリックします。, クロス集計クエリウィザードが開くため、クロス集計をするテーブルとして「売上実績」テーブルを選択し、「次へ」をクリックします。, テーブル内フィールドの内、行見出しとして「品目」フィールドを設定します。これにより品目毎の結果を出力することができます。, 続いて、売り上げの月次展開をするため列見出しとして「開始日」フィールドを選択し、「次へ」をクリックします。, 「開始日」フィールドは日付/時刻型であり、期間集計する際に「年」、「四半期」、「月」単位のように設定をすることができます。今回は、月次展開のため「月」を選択し、「次へ」をクリックします。(サンプルデータは12か月分でデータを作成しているため、各月での列が作成されることになります。), 月次売上の合計額を出力するため、各値のフィールド設定には「売上」フィールドを選択し、集計方法には「合計」を選択し、「次へ」をクリックします。, 最後に、作成したクエリの名称を決めます。今回は「売上実績のクロス集計」という名称にしました。この後、「完了」をクリックするとクロス集計クエリが起動します。, これにより出力されるクロス集計結果は図8に示すとおりですが、行に品目、列に各月、各値に当該月の合計売上が表示されます。, 今回、売上実績について月次展開するためにクロス集計クエリを作成しました。蓄積された一連のレコードを月単位・品目毎に集計をするためにクロス集計は非常に有効であり、わざわざ必要なデータを再集計することなく、目的の結果を得ることができます。, サンプルテーブルでは「開始日」と「終了日」フィールドの内、「開始日」フィールドを選択しましたが、例えば「売上日」のようなフィールドを作成しておけば、余計なフィールドを作成せず不要なデータを保持する必要もなくなります。これらのテーブル設計は、最終的な出力をイメージして実施することが重要となりますが、まずは作成する中で慣れていただければと思います。, 次回のコメントで使用するためブラウザーに自分の名前、メールアドレス、サイトを保存する。, Accessを初めて使う方必見!!Accessの使い方、作成方法を細かく紹介します。データベースを活用し業務効率の改善を図りましょう!, テーブルをキー項目で結合する際に、起点とするテーブルの全レコードを表示するか、またはキー項目で結合できるレコードを表示するかで、その結合方法が変わります。今回は、テーブル結合に際して、内部結合と外部結合について紹介をします。, Accessクエリの文字サイズ等のフォント設定を変更して文字を見やすくすることで開発効率を飛躍的に向上させる方法を紹介します。, データベースを作成してレコードの蓄積が増えてくる中で、不要なデータを削除する機会が出てきます。特に、条件に応じたレコードを一括で削除する場合に、削除クエリを活用することがお勧めです。今回は、Accessの削除クエリについてSQL文と併せて見ていきましょう。.