【Access】毎日続ける！家計簿をAccessで作ってみた。③

家計簿データベースの作成　クエリ作成編

前回作成したテーブルは、あくまでもただ単にデータを入れておくだけのものなので、計算をすることはできません。　
では、支出合計や収入合計のフィールドを作ればと考えますが、それではいちいち計算して入力しなければなりません。
そのため、計算間違いなどのリスクがおこります。
クエリを作成すれば、数式を設定したフィールドを挿入して、自動的に計算をすることができます。

「Q_家計簿」を作成する

1．リボンタブの作成をクリックして、クエリデザインを選択します。

クエリウィザードで作成することもできますが、後で残高合計などのフィールドを追加しなければなりませんので、今回はクエリデザインを選択します。

２．tbl_家計簿を選択、追加をクリックして閉じます。

３．クエリデザインが開きますので、フィールドを追加します。

４．フィールドの追加

フィールドの追加は一つずつフィールドにドラッグやダブルクリックして追加してもよいのですが、今回はすべて追加しますので、簡単な方法としてデザインビューに表示されたフィールドリストのtbl_家計簿と表示されている部分をダブルクリックします。
IDから備考まで選択されますので、フィールドへドラッグ＆ドロップします。

５．フィールドが追加されました。

６．収入合計や支出合計、残高などのフィールドを追加します。

キーボードのF-12キーを押し、一旦「Q_家計簿」の名前で保存します。

クエリのデザイン画面

７．収入合計フィールドの追加

収入フィールドの次のフィールドに収入合計フィールドを表示したいので、空白列を挿入します。
支出フィールドの列セレクターにマウスの矢印を持っていくと下向きの矢印が現れますので、クリックして黒く反転させます。
次に、リボンの「列の挿入」ボタンをクリックして空白列を挿入します。

空白列ができます。

８．収入合計フィールドを設定します。

フィールドに数式を設定する方法は、フィールド名に半角の:で数式を結び付けます。
収入合計: CCur(Dsum(“収入金額”,”tbl_家計簿”,”ID<=” & [ID]))

クエリで合計を求めるには、Dsum関数を利用します。　
Dsum関数は指定したテーブルのフィールドの合計を求める関数です。

　構文　Dsum（ “フィールド名”,”テーブル名”,条件式）　
　　　　
半角の,で区切り、文字列は半角の””、数値は半角の[]で囲みます。

収入金額の合計累計を求めますので、Dsum(“収入金額”,”tbl_家計簿”,条件式)になります。
条件式の指定ですが、[ID]フィールドを昇順で並び替えたクエリの場合、現在のレコードの[ID]より一つ小さいレコードのデータをDsum関数で合計するように指定します。

　”ID<=” & [ID]となります。

例えば下の表を考えてみます。

ID	日付	費目ID	収入	収入合計	支出
1	2020/06/07	給料	300,000	300,000	0
2	2020/06/08	食費	0	300,000	500
3	2020/06/08	雑収入	10,000	310,000	0
4	2020/06/10	生活費	0	310,000	200

ID番号1の収入合計の欄は、”ID<=” & [ID]の数式によりID<=1になり、1より小さい番号はありませんので、ID番号1の収入欄 300,000を指定することになります。
Dsum関数によりID番号1の収入合計欄は、ID番号1の収入合計欄＋ID番号1の収入欄＝0+300,000の合計になります。

ちなみにID番号2の収入合計欄は300,000+0＝300,000、ID番号3の収入合計欄は 300,000+10,000＝310,000　になります。

ここで注意するのは「+」という演算子ではなく、「＆」という文字列を結合する結合演算子を使っていることで気が付くと思いますが、Dsum 関数は文字列を返す関数です。
文字列のままでは通貨型などの編集をすることができませんので、CCur関数を使って通貨型に変換します。

　書式
　収入合計: CCur(Dsum(“収入金額”,”tbl_家計簿”,”ID<=” & [ID]))

Dsum 関数
収入の合計などの累計を計算するには、「主キー」フィールドのように、値の重複のないフィールドを作って並び替えたクエリで、Dsum関数を使います。

　Dsum（”フィールド名”,”テーブル名”,”条件式”）

引数にフィールド名、テーブル名、条件式を指定して、条件に合うレコードの集計を行う「定義域集計関数」です。

CCur関数
CCur関数は、引数をCurrency型（通貨型）に変換します。
　
　CCur（引数）

引数には任意の文字列式または数式を指定します。
省略することはできません。　また少数以下４桁に四捨五入した結果を返します。

９．支出合計フィールドの追加

手順は収入合計フィールド作成時と同じです。

　書式
　支出合計: CCur(Dsum(“支出金額”,”tbl_家計簿”,”ID<=” & [ID]))
　　

10．繰越残高フィールドの追加

繰越残高は収入合計－支出合計で求められます。

　書式
　繰越残高: CCur([収入合計]-[支出合計])

11．獲得ポイント合計、使用ポイント合計、ポイント残高、資産合計フィールドの追加

獲得ポイント合計

　書式
　獲得ポイント合計: CCur(DSum(“獲得ポイント”,”tbl_家計簿”,”ID<=” & [ID]))

使用ポイント合計

　書式
　使用ポイント合計: CCur(DSum(“使用ポイント”,”tbl_家計簿”,”ID<=” & [ID]))

ポイント残高合計　

　書式
　ポイント残高: ([獲得ポイント合計]-[使用ポイント合計])

資産合計

　書式
　資産合計: ([繰越残高]+[ポイント残高])