概要
| この記事の結論 Accessのクロス集計クエリは、行見出しと列見出しの交差位置に合計や件数を表示するクエリです。初めてならウィザードで作成し、条件や列の固定はデザインビューで調整すると進めやすくなります。重要なのは、元データの1行の意味と集計方法を先に決めることです。この記事では商品別・月別の売上を例に、作成手順、SQL、数値が合わないときの確認まで説明します。 |
この記事でわかること
- クロス集計の行見出し 列見出し 値を理解する
- 集計に使う元データを準備する
- ウィザードで商品別 月別の表を作る
- デザインビューとSQLで調整する
- 期間条件 空欄 件数集計を設定する
この記事の主な内容
- クロス集計の行見出し 列見出し 値を理解する
- 集計に使う元データを準備する
- ウィザードで商品別 月別の表を作る
- デザインビューとSQLで調整する
- 期間条件 空欄 件数集計を設定する
- 集計結果が合わないときの対処と運用
- よくある質問
想定読了時間：約12分～16分
クロス集計の行見出し 列見出し 値を理解する
クロス集計クエリは、縦と横の二つの切り口を使って明細を集計するクエリです。例えば、縦に商品、横に月を並べ、その交差位置に売上金額の合計を表示します。明細を一件ずつ読むより、どの商品がどの月に売れたかを比較しやすくなります。作成前に、何を比較して何を判断したいのかを一文で決めましょう。
| 要素 | この例で使う項目 | 決める内容 |
|---|---|---|
| 行見出し | 商品コード・商品名 | 縦に何を一行ずつ並べるか |
| 列見出し | 売上日の月番号 | 横に何を並べるか |
| 値 | 売上金額の合計 | 交差する位置で何を計算するか |
行に置く項目を増やしすぎない
行見出しが「商品コード・商品名」なら、基本的に商品単位の集計になります。ここへ担当者を加えると、同じ商品でも担当者ごとに別行になります。担当者は単なる補足表示ではなく、集計を分ける条件になる点が重要です。見せたい表の一行が何を表すのかを、利用者と先に合わせてください。
列見出しに使う式は一つです。「年月」のように複数の意味をまとめた値も使えますが、値の種類が増えるほど横長になります。毎月増える商品を列へ置くより、商品を行、固定した12か月を列にした方が定型帳票に向くことがあります。画面での分析と、用紙へ印刷する帳票を区別して設計します。
集計に使う元データを準備する
今回は、売上明細テーブル[T_売上]から商品別・月別の金額を集計します。一行は「一つの売上明細」で、伝票全体ではありません。[明細ID]は一意のキー、[商品コード]と[商品名]は短いテキスト、[売上日]は日付/時刻、[売上金額]は通貨型を想定します。以下は操作を理解するための架空データです。
| 明細ID | 商品コード | 商品名 | 売上日 | 売上金額 |
|---|---|---|---|---|
| 1 | A01 | 商品A | 2026/01/10 | 1000 |
| 2 | A01 | 商品A | 2026/01/20 | 500 |
| 3 | A01 | 商品A | 2026/02/05 | 2000 |
| 4 | B01 | 商品B | 2026/01/15 | 800 |
この例で商品Aの1月は1,500、2月は2,000、商品Bの1月は800になります。全明細の合計は4,300です。最初から大量の実データで作るより、このように手計算できる少量のデータで結果を確かめると、設定の意味を理解しやすくなります。確認後に、本番データのコピーへ対象を広げます。
月番号を作る選択クエリを保存する
ウィザードで使う項目を分かりやすくするため、先に選択クエリを作ります。［作成］から［クエリデザイン］を開き、T_売上を追加して必要な項目を配置します。空いたフィールド欄へ「月番号: Month([売上日])」と入力し、[Q_売上明細_集計用]として保存してください。SQLビューでは次のように書けます。
SELECT [明細ID], [商品コード], [商品名],
[売上日], Month([売上日]) AS [月番号], [売上金額]
FROM [T_売上]
WHERE [売上日] >= DateSerial(2026,1,1)
AND [売上日] < DateSerial(2027,1,1);
対象期間を2026年に限定しているのは、月番号だけでは2025年1月と2026年1月を区別できないからです。終了条件は翌年1月1日「未満」とし、売上日に時刻が含まれる場合も年末の明細を取り込める形にしています。日付が文字列なら、変換を重ねる前に元フィールドの型と入力方法を確認しましょう。
ウィザードで商品別 月別の表を作る
初めて作る場合は、クロス集計クエリウィザードで基本形を作り、その後にデザインビューで調整すると進めやすくなります。ウィザードは一つのテーブルまたはクエリを元にします。複数テーブルを使う場合は、必要な結合を済ませた選択クエリを先に用意し、そのクエリを選びます。
| 手順 | 操作 | この例での指定 |
|---|---|---|
| 1 | ［作成］→［クエリウィザード］を開く | クロス集計クエリウィザード |
| 2 | 集計元を選択する | Q_売上明細_集計用 |
| 3 | 行見出しを選ぶ | 商品コード、商品名 |
| 4 | 列見出しを選ぶ | 月番号 |
| 5 | 値と集計方法を選ぶ | 売上金額、合計 |
| 6 | 名前を付けて完成する | Q_商品月別クロス |
ウィザードで行ごとの合計を求める選択肢が表示されたら、必要に応じて指定します。これは商品ごとの全対象月合計です。月別のセルに表示する合計と、行全体の合計は別なので、見出しを確認してください。完成後は結果を開き、商品Aの1月が1,500になっているかを最初に見ます。
期待した項目が選択肢に出ない場合は、集計元としてテーブルではなく保存した選択クエリを選んでいるかを確認します。また、金額が文字列になっていると想定した数値集計ができません。ウィザードの操作をやり直す前に、集計元クエリだけを開いて項目名と値の型を確認するのが近道です。
デザインビューとSQLで調整する
作成したクロス集計をデザインビューで開くと、グリッドに［集計］と［クロス集計］の行が表示されます。［集計］は計算方法、［クロス集計］は表のどこへ配置するかを決めます。同じ「合計」という言葉でも役割を混同しないよう、行見出し・列見出し・値の三つを一項目ずつ確認します。
| フィールド | 集計 | クロス集計 |
|---|---|---|
| 商品コード | グループ化 | 行見出し |
| 商品名 | グループ化 | 行見出し |
| 月番号 | グループ化 | 列見出し |
| 売上金額 | 合計 | 値 |
列を1月から12月に固定する
デザインビューの空白部分を選び、クエリ全体のプロパティにある［列見出し］へ「1,2,3,4,5,6,7,8,9,10,11,12」を設定します。月番号は数値なので、数値の一覧を指定します。これで売上のない月も列として用意でき、帳票やCSVの列数をそろえやすくなります。個別フィールドのプロパティと取り違えないでください。
同じ設定をSQLビューで表すと、次のようになります。TRANSFORMが計算、SELECTとGROUP BYが行見出し、PIVOTが列見出しです。IN句に並べた値が固定列になります。[Q_売上明細_集計用]で期間を絞っているので、ここでは月番号だけを使っています。自分のデータベースでは名称を実際のものへ置き換えます。
TRANSFORM Sum([売上金額]) AS [金額合計]
SELECT [商品コード], [商品名]
FROM [Q_売上明細_集計用]
GROUP BY [商品コード], [商品名]
PIVOT [月番号] IN (1,2,3,4,5,6,7,8,9,10,11,12);
| 商品コード | 商品名 | 1 | 2 | 3～12 |
|---|---|---|---|---|
| A01 | 商品A | 1500 | 2000 | 空欄 |
| B01 | 商品B | 800 | 空欄 | 空欄 |
上表の「3～12」は説明のためにまとめた表記で、実際の結果には3、4、5という独立した列が並びます。空欄はこの例では該当する売上がないことを表します。列が固定されていても、元明細に一件も出てこない商品の行は自動では作られません。全商品を必ず表示したい場合は、商品マスターとの結合を別に設計します。
集計クエリの設計と数値照合で迷ったときは
まずは記事の手順で対象と結果を確認しましょう。既存の処理との関係が分からない、変更の影響を判断できない場合は、現在のテーブル・クエリ構成と困っている点を整理して、Accessの教科書へご相談ください。
期間条件 空欄 件数集計を設定する
完成した表を使い回すときは、対象期間と計算方法を明示します。特に「件数」という表現は、明細数、伝票数、顧客数のどれなのかで結果が変わります。売上明細を一行ずつ数えるだけで伝票数になるとは限りません。集計値の名称を「売上明細数」のように具体化すると、利用者の誤解を減らせます。
| 計算 | 使う場面 | 注意点 |
|---|---|---|
| Sum([売上金額]) | 金額の合計 | Nullの金額は計算対象から外れる |
| Count(*) | 元の行数 | 同一伝票の複数明細も別々に数える |
| Count([売上金額]) | 金額が入った行数 | Nullの行は数えない |
| Avg([売上金額]) | 明細金額の平均 | 顧客平均や伝票平均とは異なる |
売上伝票数を求めたいなら、先に商品・月・伝票IDなど必要な単位で重複を除いた選択クエリを作り、それを数える方法を検討します。何を一件とするかを変えると元クエリも変わります。合計と件数の数字だけを切り替えるのではなく、集計する一行の意味から確認してください。
入力した期間で集計する場合は型を宣言する
期間を毎回入力する構成では、パラメーター名と型を明示します。特にクロス集計では、入力値の型をAccessに推測させるだけにしないことが大切です。次のSQLはT_売上を直接参照する独立した例です。[p開始日]に2026/1/1、[p終了翌日]に2027/1/1を入力すると、前の例と同じ一年になります。
PARAMETERS [p開始日] DateTime, [p終了翌日] DateTime;
TRANSFORM Sum([売上金額]) AS [金額合計]
SELECT [商品コード], [商品名]
FROM [T_売上]
WHERE [売上日] >= [p開始日]
AND [売上日] < [p終了翌日]
GROUP BY [商品コード], [商品名]
PIVOT Month([売上日]) IN (1,2,3,4,5,6,7,8,9,10,11,12);
デザインビューでは［パラメーター］で同じ名前を登録し、日付/時刻型を選びます。終了翌日は対象最終日の翌日を指します。開始日より終了翌日が後であること、月番号で集計するこの例では複数年の同じ月を混ぜない期間であることも確認してください。複数年を比較するなら、年月を区別する列に設計し直します。
空欄を0にする前に理由を確認する
該当売上がなく空欄になったセルは、帳票用の選択クエリで「1月売上: Nz([1],0)」などとして0表示にできます。一方、入力すべき金額が未入力のため集計から外れた場合まで0へ置き換えると、不備に気づけません。元データのNullを調べる確認クエリと、見た目を整える処理は分けて用意しましょう。
集計結果が合わないときの対処と運用
合計が想定より大きい場合は、まずクロス集計の前にある選択クエリを調べます。例えば、売上明細一件に対して商品マスターの同じ商品コードが二件一致すると、結合後の明細が二行になります。金額1,000の売上が二回集計されれば2,000になり、クロス集計の式を変えても原因は解消しません。
商品マスターの商品コードが一意か、結合に必要な条件が足りているかを確認します。結合前後で明細IDごとの行数を比較すると、増えた明細を探しやすくなります。安易に結果へDISTINCTを付けると、正当に存在する同額の明細まで扱いを誤る可能性があります。重複の発生源を直すことが先です。
| 症状 | 最初に確認する箇所 | 対処の方向 |
|---|---|---|
| 合計が増えた | 結合後の明細IDの重複 | キーと結合条件を修正 |
| 一部の商品がない | 内部結合・抽出条件 | 元明細から段階ごとに照合 |
| 別の年が混ざる | 月番号だけの集計 | 期間を限定するか年月で区別 |
| 入力欄が突然出る | フィールド名の誤記 | 正しい名称と宣言済みパラメーターを区別 |
| 金額が少ない | Null・対象外分類・期間 | 元データと条件を確認 |
完成後は三段階で照合する
まず、商品A・1月のような一つのセルを元明細と照合します。次に商品単位の合計、最後に全体の合計を同じ期間・同じ条件で比べます。固定列に含まれない分類があると、元明細の合計と表示列の合計がずれる場合があります。差額が出たら、計算式だけでなく除外された明細も調べてください。
運用開始前には、売上のない月、新しい商品、金額未入力、年末と年始の明細も確認します。クエリ名、集計単位、対象期間、金額の税込・税抜、返品の扱いをメモに残すと、後任が数字を解釈しやすくなります。毎月の総額照合を作業手順に含め、表が開いたことだけを成功条件にしないようにしましょう。
まとめ
クロス集計クエリは、行見出し・列見出し・値を決めると作成の見通しが立ちます。ウィザードで基本形を作り、デザインビューやSQLで列固定と期間条件を整えましょう。正しい表にするには、元データの一行の意味、結合の重複、Null、集計方法の違いが重要です。小さな明細から手計算で照合し、業務で使う数字の定義まで明示してください。
よくある質問
通常のクロス集計クエリは、元データを集計して結果を表示するもので、明細を更新・削除する処理ではありません。集計結果を直接編集して元の売上金額を直す用途にも向きません。入力内容を修正するときは、元テーブルや入力フォームで対象明細を確認します。ただし、同じボタンに更新クエリなどを組み合わせている場合は別です。クエリ単体の働きと、ボタン全体が実行する処理を分けて確認してください。
作れます。初心者には、必要なテーブルを結合した選択クエリを先に保存し、それをクロス集計の元にする方法が分かりやすいでしょう。ウィザードで直接複数テーブルを扱おうとするより、結合結果を途中で確認できます。特に、商品マスターのキーが重複すると売上明細が増えて合計が膨らみます。元の明細IDが結合後も想定どおりの件数になっているか、クロス集計を作る前に検証することが大切です。
一つのクロス集計の交差セルには、基本的に一つの集計値を指定します。金額合計と件数を同じ月の下へ並べたい場合は、それぞれのクロス集計を作って商品コードなどで結合する方法や、帳票側で配置する方法を検討します。ウィザードの行合計は、各月に二種類の値を表示する機能とは異なります。結合する場合は、両方のクエリの行単位と列固定をそろえ、片方にデータがない商品の扱いまで決めてください。
表の一行を識別するために必要な項目を入れます。商品別なら商品コードと商品名が候補ですが、商品名だけでは同名商品をまとめてしまうことがあります。逆に、担当者や日付を追加すると、その項目ごとに行が分かれます。見やすさのために足したつもりでも集計単位が変わるので注意してください。コードを基準に集計し、名称を後から結合する設計も選べます。名称変更の履歴をどう扱うかも、業務に合わせて決めます。
クロス集計では、パラメーター名と型を明示していないことが原因になる場合があります。デザインビューのパラメーター設定、またはSQL先頭のPARAMETERSで、使用する名前と日付/時刻などの型を宣言してください。ただし、誤記したフィールド名も入力要求の原因になります。表示された名前が意図したパラメーターなのかを先に確認し、単に入力して先へ進まないことが大切です。参照元クエリとフォーム名の変更も点検します。
0は数値として値が存在し、その値がゼロである状態です。空欄は、対応する売上がない場合や、集計対象の値がNullの場合などに生じます。見た目が似ていても業務上の意味は同じとは限りません。取引なしを0と見せたいなら、列を固定した後の表示用クエリなどでNzを使えます。ただし、金額未入力という不備まで隠さないよう、元明細のNull確認を別に行ってください。0表示だけで欠けた商品の行を作ることはできません。
どちらもデータを行と列で集計できますが、Accessでは保存したクエリをフォームやレポート、別のクエリの元として利用できます。定期的に同じ条件で集計し、帳票へつなぐ用途に向いています。一方、利用者が項目を動かしながら分析したい場合はExcelのピボットテーブルが扱いやすいこともあります。Accessで明細や集計条件を管理し、Excelへ渡して分析する組み合わせも可能です。更新時点と条件を両方でそろえて比較しましょう。
関連記事
集計クエリの設計と数値照合で迷ったときは
まずは記事の手順で対象と結果を確認しましょう。既存の処理との関係が分からない、変更の影響を判断できない場合は、現在のテーブル・クエリ構成と困っている点を整理して、Accessの教科書へご相談ください。
参考情報
クエリによる合計
https://support.microsoft.com/en-us/access/sum-data-by-using-a-query
TRANSFORMステートメント
https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/transform-statement-microsoft-access-sql