スキルアップ・問題解決なら、モーグにおまかせ！

●概要●
データベースによっては、特定のフィールドの累計値が必要な場合があります。しかし、Accessでは簡単に累計を求めるような関数は用意されていません。そこで、今回はDSum関数とサブクエリを使用して累計値を求める方法を説明します。

●準備●
以下の構成の[売上]テーブルを作成します。
    ----------------------------------------
    フィールド名        データ型
    売上ID              オートナンバー
    品名                テキスト型
    売上日              日付/時刻型
    売上金額            通貨型
    ----------------------------------------
売上テーブルに適当なデータを入力します。

    

売上テーブルをソースとした新規クエリを作成し、全フィール
    ドを選択します。
●サンプル●
DSum関数を使用する場合
  クエリの空白のフィールド欄に以下の式を記述します
--------------------------------------------------------------------
累計1: DSum("売上金額","売上テーブル","売上日 <= #" & [売上日] & "#")
--------------------------------------------------------------------

サブクエリを使用する場合
  クエリの空白のフィールド欄に以下の式を記述します
--------------------------------------------------------------------
累計2: (Select Sum(売上金額) From 売上テーブル As temp売上
       Where 売上テーブル.売上日 >= temp売上.売上日
       And 売上テーブル.品名 = temp売上.品名)
--------------------------------------------------------------------

[q売上累計]という名前を付けてクエリを保存します。
    このクエリ全体では以下のようなSQL文となります。
--------------------------------------------------------------------
SELECT *,
DSum("売上金額","売上テーブル","売上日 <= #" & [売上日] & "#") AS 累計1,
(Select Sum(売上金額) From 売上テーブル As temp売上
Where 売上テーブル.売上日 >= temp売上.売上日
And 売上テーブル.品名 = temp売上.品名)) AS 累計2
FROM 売上テーブル;
--------------------------------------------------------------------
●動作確認●
クエリをデータシートビューで開きます。
累計1では日付による累計計算がされており、累計2では日付と品名による累計計算がされていることを確認して下さい。

    

●詳細●
DSum関数は定義域集計関数と呼ばれるもので、以下の構文によりなります。
Dsum(式, 定義域, [範囲])
サンプルでは売上テーブル(=定義域)の売上金額(=式)を、売上日によって選択し(=範囲)集計しています。範囲はSQL文のWhere句と同じように文字列式で記述する必要があり、今回は比較対照がクエリのカレントフィールドですので、フィールドを変数のように指定しています。
後者はサブクエリを使用する方法です。売上テーブルに「temp売上」というエイリアス(別名)を付け、売上テーブルのカレントレコードと、売上テーブル全体の売上金額フィールドを比較しています。
またサンプルでは品名も比較していますので、品名が同じであり売上日がカレントレコード以下のものが集計されます。

ここで注意が必要な点は、必ず各フィールドを識別・比較するためのフィールドが必要であるということです。AccessのテーブルはExcelの表と違い、行(レコード)の上下位置を保存していません。インデックスによって並び替えを行っているため、一見レコードの並び順が保存されているように見えますが、それは単にテーブルを開く時に並び替えを行っているだけで、レコード毎の位置関係を保存している訳ではないのです。
ですから今回のような集計を行う場合、IDなど各レコードを一意に識別することが出来るフィールドや、日付のように各レコードの位置関係を明確にできるフィールドが必要になります。

●● 用語説明 ●●
○ 定義域集計関数
   特定のレコードセット(定義域。通常はテーブルやクエリ)に含まれるフィールドの値
   を計算する関数。
   SQLの集計関数と用途は似ているが、こちらはSQL文以外でも使用できる。
   計算速度が非常に遅いのがネックである。

○ サブクエリ
   クエリの中に埋め込んだ、もう一つの選択クエリ(Select文)のこと。
   これを使用すると再帰的な集計や、レコード毎の比較などが可能になる。