MS Access Tips/Sample and VBA and Blog customize etc...

累計値をテーブルに自動入力する関数

累計値を必要とする場合は結構ありますね。例えば出納帳の残高とか。

本来はクエリで、DSum関数やサブクエリで演算で求めるべきものです。しかし、この方法は処理が重くなりがちですし、式やSQLが複雑になりがちです。

そこでクエリを使わずに累計値をテーブルに簡単に入力する関数を紹介します。

累計値をテーブルに自動入力する関数

難易度:

仕様

下記の仕様を例に解説します。

T出納帳
フィールド名データ型
出納IDオートナンバー型
日付日付/時刻型
科目CD数値型
入金額通貨型
出金額通貨型

日付順、同じ日付の場合は、出納IDの昇順で累計額(残高)を求める。

クエリで DSum または サブクエリで

クエリで演算する方法も先に紹介します。

T出納帳からクエリを作成して、フィールドに下記の式を設定します。

これで残高が表示されます。件数が多いとかなり重くなると思います。
サブクエリを使うと少しは改善されます。ただしこのクエリでは入力できません。

式が複雑なので書くのが面倒、遅い、サブクエリだと入力できない、、、そこで、下記の関数の登場です。

累計を入力する関数

実は、これは下記で紹介している連番入力関数を少し手直ししただけでできます。

グループ毎連番を自動入力する関数 - hatena chips

この関数のページはアクセス数が多く拍手数も多いので結構需要が高いのだと思われます。累計も同様に需要があるはずだということで作成してみました。

このコードをコピーして標準モジュールに貼り付ければ使用可能です。

使用法

上記のクエリと同じ仕様で残高を入力する場合は、まず、テーブルに 残高(通貨型)フィールドを追加してから、下記のコードを実行します。

これで残高が入力されます。

グループ毎の累計を出力したい場合は、GroupBy引数を設定します。例えば、科目ID毎の累計なら、

WhereCondition引数で抽出条件を設定できます。抽出されたレコードの累計のみ更新されます。
例えば、GroupBy引数で[科目ID]でグループ化した累計を格納しているとき、ある科目の途中のレコードを修正した場合、その科目のみの累計を更新したいというときなどに使用するといいでしょう。

KeepInitialValue引数はちょっと分かりにくいかもしれません。今回この仕様をどうするか結構悩みました。

レコード件数が非常に多い場合、全レコードの累計を更新すると時間がかかる可能性があります。
例えば過去10年分の出納データが格納されていて過去の累計はもう確定していて、累計は今年度分だけでいい場合、10年分もの更新をするのは無駄ということで、WhereCondition引数で今年だけ抽出して更新したとすると、抽出した先頭レコードからの累計値になります。

例えば、

日付入金額出金額累計
3/30100005000
3/3105004500
4/01010000
4/0230000
4/2308000

というデータがあり、これを下記のようにWhereCondition引数で 4/01以降を抽出してこの関数を実行すると、

日付入金額出金額累計
3/30100005000
3/3105004500
4/0101000-1000
4/023000-700
4/230800-1500

こういう場合に、KeepInitialValue引数をTrueにして実行すると、

日付入金額出金額累計
3/30100005000
3/3105004500
4/01010003500
4/0230003800
4/2308003000

と先頭レコードの累計を引き継ぐことができます。注意事項として、前回実行して正しい累計値が入力されているレコードからの抽出条件にする必要があります。

使用条件、免責事項

個人利用、商用利用に関わらず自由に使用できます。改変するのも自由です。ただし、この関数を引用する場合は引用元を明記してください。

自分の環境でバクがないか動作確認はしてますが、見落としがあるかもしれません。この関数を利用して生じたいかなる損害も責任は負いかねます。

バグを発見した場合は連絡していただけるとありがたいです。

拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/443-aad6ddb6
該当の記事は見つかりませんでした。