アクセス クエリでの残高計算について

質問

アクセスクエリでの残高計算について
返金日を入力した場合、伝票日を基準とした残高がマイナスとなる式を教えてください。

現在のクエリ（ID～返金日）の状態です。
ID......伝票日....会社....銀行......出金額......返金日..........残高
1..........4/1.............A..........あ..........100円..........5/10.............100円
2..........4/15..........B..........あ..........400円..........空白.............500円
3..........4/30..........C..........い........1000円..........5/15..........1500円
4..........5/10..........B..........い...........300円..........5/20..........1200円
5..........5/14..........A..........い..............50円..........空白..........1250円
6..........5/15..........A..........あ..............50円..........空白............300円
【補足説明】
ID4：ID1でAが100円返金済みなので1500+300-100＝1200円となる
ID6：ID3でCが1000円返金済みなので1250+50-1000＝300円となる

(1)返金日に日付けを入力するとマイナスとなる残高計算をしたいと考えていますが可能でしょうか？
・可能な場合、数式？を教えていただきたいです。
・不可能な場合、どういった解決策があるか教えていただきたいです。

(2)また、A会社を抽出後に、銀行別で(1)と同じように残高を表示させたいと考えていますが、
どのように、(1)の表よりどのように加工すれば、いいのでしょうか？
（簡単にでもいいので是非お願いします）

今回アクセスを初めて使い、苦戦しております。
どうぞ、ご回答のほう、よろしくお願いいたします。

回答No.4

ksd_hiro · ベストアンサー

>教えて頂いた下記の式を使用しても、希望する形の残高が得られませんでした。
>残高が抽出前の残高を引っ張ってきているようです。

>(3)銀行別にした上で会社別
>●現在の残高表示
>ID......伝票日....会社....銀行......出金額......返金日..........残高
>1..........4/1.............A..........あ..........100円..........5/10.............100円
>6..........5/15..........A..........あ..............50円..........空白............800円

>●希望する残高表示（銀行の中だけで、足し引きをしたい）
>ID......伝票日....会社....銀行......出金額......返金日..........残高
>1..........4/1.............A..........あ..........100円..........5/10.............100円
>6..........5/15..........A..........あ..............50円..........空白............50円

現在のID6の残高が800円になっているのは、レコード全体で残高を計算した結果を、
「銀行あ」かつ「会社A」で絞り込んだ状態ではないでしょうか？

それでは、残高計算の式を、
DSum("出金額","QU_伝票","伝票日 <= #" & [伝票日] & "# AND 会社 = '" & [会社] & "' AND 銀行 = '" & [銀行] & "'")+0
のようにしてみてください。（シングルクォートも忘れずに入力します）
これで、会社毎かつ銀行毎に残高を計算します。

お礼

前月にベストアンサーを選んだつもりでいましたが、ボタンを押せてなかったようです。
大変遅くなり、申し訳ありません。
また、何度もご回答いただき、また親切に色々と教えて頂きありがとうございました。

回答No.3

ksd_hiro

>ユニオンクエリでの元データはテーブルのデータではなく、
>クエリから作成したデータを使うことに変更したので時間がかかり、
>本日やっと、残高のみ表記でき(2)も挑戦したのですが、できませんでした。

>そこで、質問(2)の部分なのですが、下記の3点の残高を表示させる方法を
>是非教えていただけませんか？

>(1)銀行別（下記表）
>ID...伝票日....会社....銀行......出金額.......返金日.....残高
>1.....4/1...........A..........あ........100円..........5/10.......100円
>2.....4/15........B..........あ........400円..........空白........500円
>6.....5/15........A..........あ........50円...........空白........450円

>(2)会社別

>(3)銀行別にした上で会社別
>ID....伝票日...会社....銀行....出金額....返金日.....残高
>1......4/1..........A.........あ.......100円........5/10.......100円
>6......5/15.........A.........あ........50円........空白........50円

ユニオンクエリの前段階が、どのようなクエリになっているかわからないのですが、
「銀行あ」での絞り込みができれば、前回回答した、
DSum("出金額","QU_伝票","伝票日<= #" & [伝票日] & "#")+0
を使用して、（１）銀行別の結果が得られるはずです。

また、「会社A」かつ「銀行あ」で絞り込みができれば、（３）銀行別会社別の結果が得られます。

（２）会社別の結果を得るためには、残高の計算を
DSum("出金額","QU_伝票","伝票日 <= #" & [伝票日] & "# AND 会社 = '" & [会社] & "' AND 銀行 = '" & [銀行] & "'")+0
とすれば可能です。

適切な並べ替えを指定しておけば、希望の結果が得られると思います。

お礼

ありがとうございました。

補足

早速のお返事ありがとうございます。
教えて頂いた下記の式を使用しても、希望する形の残高が得られませんでした。
残高が抽出前の残高を引っ張ってきているようです。
お手数ですが、教えていただけますか？

(3)銀行別にした上で会社別
●現在の残高表示
ID......伝票日....会社....銀行......出金額......返金日..........残高
1..........4/1.............A..........あ..........100円..........5/10.............100円
6..........5/15..........A..........あ..............50円..........空白............800円

● 希望する残高表示（銀行の中だけで、足し引きをしたい）
ID......伝票日....会社....銀行......出金額......返金日..........残高
1..........4/1.............A..........あ..........100円..........5/10.............100円
6..........5/15..........A..........あ..............50円..........空白............50円

回答No.2

ksd_hiro

回答の前に、確認ですが、補足説明のID4の計算違ってますよね？
ID4以降の残高は、1700、1750、800だとして考えてみます。

（１）については、ユニオンクエリを使用して、以下のような、返金日が記入してあるレコードを追加した一時テーブルを作成して、DSum関数を利用して出金額を累計すれば、できると思います。

ID 伝票日 会社 銀行 出金額 返金日
1 4/1　　　A　　 あ　　100　　 5/10
2 4/15　　 B　　 あ　　400
3 4/30　　 C　　 い　　1000　 5/15
4 5/10　　 B　　 い　　300　　 5/20
5 5/14　　 A　　 い　　50
6 5/15　　 A　　 あ　　50
1 5/10　　 A　　 あ　　-100　　5/10
3 5/15　　 C　　 い　　-1000　5/15
4 5/20　　 B　　 い　　-300　　5/20

ID、伝票日、会社、銀行、出金額、返金日の各フィールドデータが、伝票テーブルに保存されているとして、次のようなユニオンクエリ（例：QU_伝票）を作成します。

SELECT ID,伝票日,会社,銀行,出金額,返金日 FROM 伝票 UNION ALL SELECT ID,返金日 AS 伝票日,会社,銀行,-伝票.出金額 AS 出金額,返金日 FROM 伝票 WHERE 伝票.返金日 IS NOT NULL;

このユニオンクエリを開くと、上のようなデータが得られます。
ちなみに、ユニオンクエリは、クエリのデザインビューでは、作成できませんので、SQLビューに変更して、直接記述してください。

次に、このユニオンクエリを元に、選択クエリを作成します。
質問にある結果を得たい場合は、クエリのデザインビューで、QBEグリッドに、IDから返金日までのフィールドを追加し、その隣に、残高フィールドを以下のように記述して追加します。

残高: DSum("出金額","QU_伝票","伝票日<= #" & [伝票日] & "#")+0

最後に、出金額フィールドの抽出条件に、>0 を指定して、出金額がマイナスのレコードを、表示しないようにして保存すれば完了です。

（２）については、会社と銀行を同時に指定しての結果でよければ、会社と銀行で抽出したデータを元に、ユニオンクエリを作成することで、できますが、会社だけを指定した場合は、別の方法を考える必要があります。

お礼

ありがとうございました。

補足

ユニオンクエリでの元データはテーブルのデータではなく、
クエリから作成したデータを使うことに変更したので時間がかかり、
本日やっと、残高のみ表記でき(2)も挑戦したのですが、できませんでした。

そこで、質問(2)の部分なのですが、下記の3点の残高を表示させる方法を
是非教えていただけませんか？

(1)銀行別（下記表）
ID...伝票日....会社....銀行......出金額.......返金日.....残高
1.....4/1...........A..........あ........100円..........5/10.......100円
2.....4/15........B..........あ........400円..........空白........500円
6.....5/15........A..........あ........50円...........空白........450円

(2)会社別

(3)銀行別にした上で会社別
ID....伝票日...会社....銀行....出金額....返金日.....残高
1......4/1..........A.........あ.......100円........5/10.......100円
6......5/15.........A.........あ........50円........空白........50円

回答No.1

noname#192382

アクセスのほかにエクセルを使うことがよければ残高の計算が出来ます、出来ました。下記のサイトの残高計算というフォルダーにアクセスのファイル一つとエクセルのふたつのファイルが入っています。
作業の手順は　(1)　アクセスのテーブルからクエリーを使って、預かり金関係と入金関係の二つのテーブルにわけ、エクセルの二つのテーブルをつくります　(2)エクセルの入金関係のテーブルを預かり金関係のテーブルにコピー　(3)預かり金関係のテーブルを日付を基準に並べ替え　(4)　セルの中に計算式を入れる
これで残高の計算までは出来ます。
もしサイトを覗くことが出来たらごらんになってください。

参考URL：

http://www9.plala.or.jp/yoshihase/残高計算

お礼

ありがとうございました。