この記事は PDF ダウンロードでもご利用いただけます。
小計、総計、その他データの集計表示は、レポートの一般的な機能です。
プロパティを設定するだけで、レポート
に累計を表示できます。累計
とは、前の行と現在の行の合計を合計したものです
。つまり、各行の累計は、その行自体の合計に前の合計を加えた値になります。残念ながら、すべてのオブジェクトが
レポートのように簡単に
累計を処理できるわけではありません。
表現
クエリで累計を生成するための組み込みプロパティや関数はありません。そのためには、
次のような複雑な式が必要になります。
SELECT fieldlist,
(SELECT Sum(valuefield) AS Total
FROM datasource
WHERE datasource.sortfield <= T1.sortfield) AS Total
FROM datasource AS T1
表 A は、
このステートメントの議論を説明しています。
表A
|
口論 |
説明 |
|
フィールドリスト |
クエリで返したいフィールド |
|
値フィールド |
合計するフィールド |
|
データソース |
|
|
ソートフィールド |
ユニークで上昇する値のフィールド |
この式はうまく機能しますが、どうしても避けられない落とし穴があります。
テーブルには昇順で一意の値を持つ列が必要です。それが上記の構文ステートメントのsortfield
です。
オートナンバー型フィールドを使用すればこの処理は可能ですが、昇順で一意の値を持つ数値型フィールド
も使用できます。合計したい実際の値で並べ替えるに
は、それらの値が一意で、かつ昇順になっている必要があります。
そのため、実際の値を使用することは避けるべきです。たとえ最初から昇順だったとしても、
その後も昇順が続く可能性は低いからです。
クエリの例
図 Aに
示すテーブル内の値の累計を生成するクエリを作成しましょう
(OrderID はオートナンバー型フィールドで、OriginalValue は
数値型フィールドです)。任意のテーブルを使用できますが、既に説明した 2 つの数値フィールド (
合計する値のフィールドと
昇順の一意の値のフィールド) が含まれている必要があります。
|
図A |
 |
デザインビューで選択クエリを開き、
「表示」ボタンから「SQLビュー」を選択してSQLウィンドウを開きます。このウィンドウには、クエリのSQL
文が表示されます。（このSQL文はQBEグリッドからは入力できません。）SQL
ウィンドウに、次のSQL文を入力します。
SELECT OrderID, OriginalValue,
(SELECT Sum(tblOriginalValues.OriginalValue) AS Total
FROM tblOriginalValues
WHERE tblOriginalValues.OrderID <= T1.OrderID) AS Total
FROM tblOriginalValues AS T1
クエリを実行すると、図 B
に示すように、Total フィールドには OriginalValue フィールドの値の累積合計が返されます。
|
図B |
 |
OrderID値（
オートナンバーフィールド）は昇順になっているため、WHERE句の条件は常に
Trueとなり、すべてのレコードの合計が強制的に計算されます。このSQL文は
集計ドメイン関数と同様に動作しますが、はるかに高速です。
注目すべき点
例のソートフィールド（OrderID）のオートナンバー値が値をスキップしていることにお気づきかもしれません。値の間に空白があっても結果には影響しません。実際には、ソートフィールドが昇順である
必要はありません。
唯一の絶対条件は、フィールドに一意の値が含まれていることです。ソートフィールドが昇順でない場合は、フォームにORDER BY句を追加します
。
ORDER BY T1.ソートフィールド
これを行うと、実行中の合計が変わる可能性が高いため、
順序が重要な場合は、自動番号フィールドを使用します。
最後に、このソリューションは大量のデータを評価する際には速度が低下します。
ただし、クエリ式、レポート、またはフォームコントロールで集計ドメインを使用する
よりも、パフォーマンスは向上するはずです
。
合計を続ける
クエリで累計を生成するのは直感的ではありませんが、
サブクエリを使えば可能です。
一意の昇順値を持つ数値フィールドを追加する必要がありますが、必要に応じてテーブルにオートナンバー型フィールドを追加するだけで実現できます
。クエリで累計を生成したら
、好きな形式でリストを表示できます。
スーザン・セールス・ハーキンスは独立コンサルタントであり、
データベース技術に関する数百の記事と数冊の書籍を執筆しています。彼女の
最新著書は『Mastering SQL Server
Express』です。
です。その他の最近の著書には、
『Automating Microsoft Access 2003 with VBA』と『Upgrader’s Guide to Microsoft office System 2003』があります。現在、スーザンは
データベースアドバイザーの出版ディレクターとしてボランティア活動を行っています。
連絡先は[email protected]です。
