Accessで残高計算をする！

こんにちは！　イマジネットＰＣサポートの橋崎です。

今回もご依頼頂いた内容をご紹介したいと思います。

現金出納帳をAccessで作成できないか？

お客様からこのようなお問い合わせを頂きました。

なるほど、これは初めてのパターンですね。

当店が最初に考えたのが、残高計算でした。

どうやってするんやろ？←マジか！

AccessはExcelのようにシート内またはブック内にあるセルを自由に参照して計算する事はできません。Accessも表形式はありますが、それはあくまで表示されているだけで、データとしては1件、1件のレコード（Excelで言うところの行）は独立しているのでExcelのような事はできません。

したがって残高のような計算をするのであればExcelで作成した方がはるかに簡単です。

但し、現金出納帳のようなデータは時系列順に表示する必要があります。勿論、入力を順番通りに行えばそれで問題はありませんが、現実はそう言う訳にはいきませんよね。

2～3日遅れで領収書を持ってくる場合もあります。もっと極端に言えば月末にまとめて清算する社員もいるかもしれません。

その時のために「行をこれぐらい空けとくか！」ちょっと現実的ではありませんね。

Excelでも並べ替えの機能はあるので、工夫をすればできるとは思いますが、当店はあまりExcelは詳しくありません。

残高計算の方法

今回は残高計算を始め表形式でデータ入力するデータベースを作成する事も初めてなので、色々調べてみました。

税理士さんのプログで参考になる記事がありましたので、ご紹介しておきます。

税理士が教えるAccessとExcelで経理会計の仕事を効率的にする方法→Accessでつくる現金出納帳

但し、こちらは現金出納帳にとって重要な並べ替えについても含まれて解説されているので、初心者の方には少し考える事が多くなります。

ここでは少し分けて考えましょう。

では、単純に明細ごとの計算をしてみましょう。
8月6日に入金が10000円、出金が500円で9500円の残高になっています。これで良さそうですが、7日に300円の出金時には-300円となっています。ここは9200円となってほしいところですね。

このようにAccessはレコードごとの計算は簡単に設定できますが、行を超えて6日の計算結果を計算対象にする事はできません。

どのようにすればよいのでしょうか。

8月6日はこれで問題ありません。
では、次の7日分300円の出金の時、どう計算すれば残高は9200円となるのでしょうか？

頭では簡単にわかりますよね。

そうです。
7日はそれまでの入金額の合計10000円と出金額の合計800円つまり入金額の合計と出金額の合計を持って差し引けば残高になりませんか？

ちょっと見えてきましたね。

ここでは、それぞれのフィールドに対し条件を指定して集計ができる「Dsum」関数を使用します。（Dsum関数の解説はお調べ下さい。Excelで調べた方が詳しく出てきます。）

テーブル名は「明細」

フィールドは以下です。

• ID･･･主キー・今回はオートナンバーを使用
• 日付･･･取引する日付を入力・今回は日付型
• 入金･･･入金額を入力・今回は数値（通貨型でもかまいません。）
• 出金･･･出金額を入力・今回は数値（通貨型でもかまいません。）

明細テーブルにはこのようにデータが入力されています。

では、明細テーブルを元にクエリを作成します。

その前にかるくDsum関数と残高を計算する式の解説をしましょう。

Dsumの書き方：DSum(合計したいフィールド, 対象となるテーブル又はクエリ, 条件式)

フィールドは「入金」と「出金」のそれぞれ、対象となるテーブルは「明細」なので、ここまでは問題ありませんね。

問題は条件です。

必要なのは入力しているレコードを含めた以前のデータの合計ですよね。

と、言う事は入力中も含めた以前のデータが条件となります。
判断するキーは時系列で番号が割り振られる「ID」が今回は適していそうですね。

では、条件式を含めた式はこのようになります。

入金の合計: DSum(“入金”,”明細”,”ID<=” & [ID])

出金の合計: DSum(“出金”,”明細”,”ID<=” & [ID])

[]のついているIDは現在入力しているIDとなり、ここでは現在入力しているIDを含めた以前のIDと条件提示しています。
もう少し詳しく言えば、「現在入力している入金額を含めた以前の明細テーブルの入金額を全て足してください。」そのようなイメージです。

これで、入・出金額の合計計算ができるようになりました。

では、クエリに設定してみましょう。
それぞれのフィールドに「入金額の合計」と「出金額の合計」を設定しました。

では、実行してみましょう。
合計を確認すると問題なく計算されていますね。

後は入金額の合計から出金額の合計を引けば残高が計算されます。

もうわかりますよね。
そうです。
式はこのようになります。（Ｎz関数は数値がない場合の対策です。くわしくはお調べ下さい。）

残高: Nz(DSum(“入金”,”明細”,”ID<=” & [ID]))-Nz(DSum(“出金”,”明細”,”ID<=” & [ID]))

これをクエリに追加します。

計算合ってますよね？

これで残高計算は完了です。

あとがき

現金出納帳のような会計関係で作成するなら税理士さんの記事にあるように表示順についてもこだわる必要があるので、そちらは記事をご覧ください。こちらはそれを含んだ内容になっています。

また、これも調べる時に色々な記事で書かれていますが、データ量によってはDsum関数を使用する方法では処理が遅いと言う事でした。

まぁ　それもそうですね。
毎レコードごとに、合計金額を集計して計算していますから、そりゃ件数が多くなれば重くなります。

これに対応した方法として残高を計算結果ではなくデータとして保存する方法があります。処理のコードも紹介されていますが、これは難しいですね。コピペで使用できるようになっているようですが、コピペではトラブルが発生した時や変更時に対応ができません。

今回は以前のデータも確認させて頂きヒアリングした内容では件数はそれほど多くはなさそうなので、今回ご紹介した内容で作成して様子を見ながら対応していこうと思っています。

これは少し話がそれますが、Accessは色々工夫すれば難しい事をしなくても比較的、簡単な処理ですむ場合があります。

その仕組みを考える事も意外と楽しかったりします。

それでは、お疲れ様でした。