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

現在のクエリ(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)の表よりどのように加工すれば、いいのでしょうか?
(簡単にでもいいので是非お願いします)

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

このQ&Aに関連する最新のQ&A

A 回答 (4件)

>教えて頂いた下記の式を使用しても、希望する形の残高が得られませんでした。


>残高が抽出前の残高を引っ張ってきているようです。

>(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
のようにしてみてください。(シングルクォートも忘れずに入力します)
これで、会社毎かつ銀行毎に残高を計算します。
    • good
    • 0
この回答へのお礼

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

お礼日時:2011/05/10 16:49

>ユニオンクエリでの元データはテーブルのデータではなく、


>クエリから作成したデータを使うことに変更したので時間がかかり、
>本日やっと、残高のみ表記でき(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
を使用して、(1)銀行別の結果が得られるはずです。

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

(2)会社別の結果を得るためには、残高の計算を
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円

補足日時:2011/04/22 11:21
    • good
    • 0
この回答へのお礼

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

お礼日時:2011/05/10 16:50

回答の前に、確認ですが、補足説明のID4の計算違ってますよね?


ID4以降の残高は、1700、1750、800だとして考えてみます。

(1)については、ユニオンクエリを使用して、以下のような、返金日が記入してあるレコードを追加した一時テーブルを作成して、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)も挑戦したのですが、できませんでした。

そこで、質問(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円

補足日時:2011/04/19 13:14
    • good
    • 0
この回答へのお礼

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

お礼日時:2011/05/10 16:50

アクセスのほかにエクセルを使うことがよければ残高の計算が出来ます、出来ました。

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

参考URL:http://www9.plala.or.jp/yoshihase/残高計算
    • good
    • 0
この回答へのお礼

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

お礼日時:2011/05/10 16:51

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q居酒屋で。謎。ニヤニヤ(・∀・)ニヤニヤの意味

先日。友達と近所の居酒屋に行きました。
すると。カウンターで飲んでた団塊世代の男性達数人。
(居酒屋の常連のようです)
その中の2人が私の顔を見るなり…。
満面の笑み (`・∀(`・∀(`・∀(`・∀(`・∀(`・∀(`・∀・´*) ニパッ!!
2人してずっと見つめて…たと思います。ヾ(;´▽`A`アセアセ
そして。帰り際、まだカウンターで飲んでた男性達。
同じ人が。私を再び見るなり。
ニヤニヤ(・∀・)ニヤニヤ ニヤニヤ(・∀・)ニヤニヤ
ニヤニヤ(・∀・)ニヤニヤニヤニヤニヤニΣ(゜Д゜)

なんで私の顔を見てニヤニヤ(・∀・)ニヤニヤ するのか?したのか全くわかりません。
私の顔になんかついてたかな?il||li _| ̄|○ il||li

ニヤニヤ(・∀・)ニヤニヤ 怖いです。
今思えば。過去、何度となく、色んな場所で。
いました。そういう人。ニヤニヤ(・∀・)ニヤニヤ
決まって。団塊世代の男性です。
私の父と同じ世代の人ばかりです。
なぜ?ニヤニヤ(・∀・)ニヤニヤ してたんでしょう?
ニヤニヤ(・∀・)ニヤニヤされたら。私 どうしたらいいんでしょうか?
いつもヾ(;´▽`A`アセアセ しちゃいます。
私もニヤニヤ(・∀・)ニヤニヤにはニヤニヤ(・∀・)ニヤニヤ仕返せばいいんですか?
団塊世代の男性の方 回答しただければ幸いです。
m(´-ω-`)m よろしくお願い致します。

先日。友達と近所の居酒屋に行きました。
すると。カウンターで飲んでた団塊世代の男性達数人。
(居酒屋の常連のようです)
その中の2人が私の顔を見るなり…。
満面の笑み (`・∀(`・∀(`・∀(`・∀(`・∀(`・∀(`・∀・´*) ニパッ!!
2人してずっと見つめて…たと思います。ヾ(;´▽`A`アセアセ
そして。帰り際、まだカウンターで飲んでた男性達。
同じ人が。私を再び見るなり。
ニヤニヤ(・∀・)ニヤニヤ ニヤニヤ(・∀・)ニヤニヤ
ニヤニヤ(・∀・)ニヤニヤニヤニヤニヤニΣ(゜Д゜)

なんで私の顔を見てニヤ...続きを読む

Aベストアンサー

自分の娘や息子の事を思い出して、酒も手伝って ニヤニヤ(・∀・)ニヤニヤ していたのでは?

Qアクセスのクエリ日付が変わるごとに*を自動入力

1)アクセスのクエリで、日付が変わるごとに"*"が自動的に入る式を教えてください。
 (A列マークを参照)

2)1)に下記の条件を追加した場合の式も、教えてください。
  伝票IDをひとつの区切りとして、同日であっても(図A)5行を超えた場合は"*"を入れたいです。


iifの式からできるかと思い、色々しているのですが出来ずに困っております。
よろしくご教授頂きます様お願い致します。
(図の赤と青のラインは見やすいかと思いラインを入れています。)

Aベストアンサー

なかなか回答がつかないようですので、老婆心から。
直接の回答とは言えませんし、すでにご存知かもしれませんが、
一つの参考となさってくださいませ。


実際、勘違いされている方も多く見えるのですが、
エクセルのような表計算ソフトは
「どのセルにどんなデータが入力されている」と言うものをファイル単位で持ちます。
つまり、ファイル内のセル同士の比較ですので、前後の行との比較が容易に出来ます。
これが100行後ろのセルでも、1000行前の違う列のセルでも、
セル番地を直接指定できますから、基本的には常に正しいセルを参照します。

対し、アクセスのようなデータベースソフトはレコード単位でデータを保持します。
同じテーブルに保存されていても、実はレコード同士の相関関係は一切無いのです。
表示上、一見整然と並んでいるように見えますが、
ランダムに保存されているレコードを拾い集めて、ソートして表示しているだけです。
このソートの条件によって、前後の行が入れ替わる可能性は大いにあるのです。
ソート条件も主キーも指定していないテーブルで
「おいおい、入力した順番と違うじゃん」と言った経験はありませんか?
簡単に言うと、これはアクセスが「見つけた順」に表示してしまうからです。
なので、通常の考え方では「前の行」と比較して・・と言うのは通用しません。



さて、アクセスでお望みの処理をクエリで実現させるには、
「このレコードは上から何番目」である、とアクセスが識別できるフィールド、
要するに「連番」フィールドを付加し、ある「連番」を振られたレコードと、
「一つ大きい(あるいは小さい)連番」を振られたレコードとを
比較する方法がまず考えられます。


一つ一つ説明しているとかなりの文字数を要しますし、
図で頂いた条件からでは正確な回答を出すのは不可能ですから、とりあえずヒントだけ。

連番を振るのにはDCount関数が便利です。
見てお察しいただけると思いますが、データの件数を(条件付きで)数える関数です。
これを使って「あるフィールドが自分より若いレコードを数えて+1」してやれば、
これが「連番」として機能してくれます。

レコードを比較するにはDLookup関数を使うと比較的楽です。
これもお察しいただけると思いますが、
ある条件に合致するレコードのフィールドを見に行ってくれる関数です。
これで「自分の連番より一つ大きい(小さい)連番を付加されたレコード」を見に行き、
それを以て、自分のフィールドと比較してやれば良いです。
この比較にはIIf関数が有効かもしれませんね。

各関数については、別途お調べいただけたらよろしいかと思います。
Webで検索をかけると相当の数がヒットしてきます。

今回はそんな時間は・・とおっしゃるのであれば、
素直に、添付された図のようにエクセルに吐き出して処理した方が早いかもしれません。




レポート上で・・と言うのであれば、比較的楽ではあるんですけどね。
普段は不可視である非連結であるラベルを用意しておいて、
条件が有ったら(日付が変わったらなど)可視にする、
と言うのを詳細のフォーマット時にでも書いておけば良いので。


以上、参考までに。

なかなか回答がつかないようですので、老婆心から。
直接の回答とは言えませんし、すでにご存知かもしれませんが、
一つの参考となさってくださいませ。


実際、勘違いされている方も多く見えるのですが、
エクセルのような表計算ソフトは
「どのセルにどんなデータが入力されている」と言うものをファイル単位で持ちます。
つまり、ファイル内のセル同士の比較ですので、前後の行との比較が容易に出来ます。
これが100行後ろのセルでも、1000行前の違う列のセルでも、
セル番地を直接指定できますから、...続きを読む

Qニヤニヤしない方法

ニヤニヤしない方法 会社の上司にニヤニヤして気持ち悪いといわれ、 今度ニヤニヤしたら罰ゲームだからな!と言われました。 すぐにでも対処できることはありますでしょうか?

Aベストアンサー

机の上に鏡を置いておきましょう。
それでの自分がどんな顔をしているか判るでしょう。

Qアクセスの追加クエリについて(1対1リレーションシップの場合)

お世話になります。
似たような質問はあるのですが、どれもしっくりこないので質問させて下さい。

アクセスで検査結果のデータベースを作ろうと考えています。
アクセスでデータベースを作製するのはこれが初めてです。

1製品毎に製造番号をつけ、各テーブルの主キーにしています。
テーブルは検査のタイミング毎に分け、それぞれの検査項目をフィールドに設定しています。
この方法だと各テーブルのリレーションは1対1になります(よね?)

それで、新たに製品の詳細(製造日や数量など)を一括して登録したいと考え、製造番号を含むテーブルを作成しました。
このテーブルで入力した製造番号を他のテーブルにも自動で登録したいので、追加クエリでやろうとするのですが、
「0件のレコードを追加します」と出てしまいます。
データは適当にですが、入力はしています。

どうすれば実現できるのでしょうか?

また、テーブルを分割しなければ製造番号を追加する必要もない訳ですが、
1つのテーブルに統合するべきなのでしょうか?
その場合はフィールドは50個ぐらいになってしまいます。

大変お手数ですが、ご教示頂けると幸いです。
よろしくお願いします。

お世話になります。
似たような質問はあるのですが、どれもしっくりこないので質問させて下さい。

アクセスで検査結果のデータベースを作ろうと考えています。
アクセスでデータベースを作製するのはこれが初めてです。

1製品毎に製造番号をつけ、各テーブルの主キーにしています。
テーブルは検査のタイミング毎に分け、それぞれの検査項目をフィールドに設定しています。
この方法だと各テーブルのリレーションは1対1になります(よね?)

それで、新たに製品の詳細(製造日や数量など)を一括して...続きを読む

Aベストアンサー

<1製品毎に製造番号をつけ、各テーブルの主キーにしています。
テーブルは検査のタイミング毎に分け、それぞれの検査項目をフィールドに設定しています。
検査のセクションごとにテーブル1、テーブル2・・・を作ります。勿論各テーブルに製造番号のフィールドをいれてあります。

<それで、新たに製品の詳細(製造日や数量など)を一括して登録したいと考え、製造番号を含むテーブルを作成しました。
これをメインテーブルとし、この中にフィールドとして製造番号をいれます。
製造番号はどのテーブルもテキスト型に統一します。
メインテーブルとセクションのテーブルの間にリレーションを設定しますが、結合の種類をメインテーブルの全部とセクションのテーブルの同じ結合データにします。こうすればセクションごとのテーブルにメインテーブルの製造番号を入れることが出来ます。

Q笑顔で会話 と ニヤニヤ話す の違いってなんでしょうね?

笑顔で会話 と ニヤニヤ話す の違いってなんでしょうね?


私は笑顔で人と接しようと頑張ってるんですが
いつも「ニヤニヤして気持ち悪い」って言われるんです。
笑顔とニヤニヤの違いってなんですかね?

Aベストアンサー

まず、身なり、身なりがきちんとしてないとニヤニヤと思われ気味、俺はそうだった。あと会話と笑いの比率、なにも喋らないで笑ってるとニヤニヤと思われる。結構しゃべらないで笑われると意味してることがわからないから。馬鹿にしてるのか、面白いのか、幸せをわけたいのか?あと受けて側に固定概念みたいに嫌がられるとその笑いがいやな風にとられる。

Qアクセス・クエリ内のフィールド上での計算式について

アクセス上には、顧客IDテーブル、売上テーブルと入金テーブルがあります。

(1) 売上テーブル及び入金テーブル上でのIDは、顧客IDテーブルと紐付くようにしています。
(結合プロパティは、顧客IDテーブルの全レコードと売上(入金)テーブルの同じ結合フィールドのレコードだけを含める設定にしています。)

(2) 売上(入金)テーブルからクエリでID毎の合計売上額と合計入金額を算出しました。

(3) (2)を基にフィールドに"ビルダー"にて合計売上額-合計入金額として、ID毎の残高を算出しました。

(4) 問題は、(2)のID毎に合計売上額があっても、IDによっては未入金の場合もあり、
その場合、合計入金額を算出するクエリ上ではそのIDは空の状態で"0"表記もされないために、
(3)は、何も表示されません。(即ち、合計売上額100 - 空 = 空)。空ではなくて、ちゃんと空の場合は、0認識出来る方法ってありますでしょうか?

Aベストアンサー

未入力(Null値)を 0 に変換する関数 Nz を使用します。


Nz([合計売上額],0)-Nz([合計入金額],0)

Q上戸彩のことを考えるとき、ニヤニヤするのをやめる方法はありませんか

僕は毎日ひまさえあれば上戸彩さんのことを考えているのですが、
上戸彩さんのことを考えているとかならず無意識に顔がニヤニヤしてしまうので困っています。
去年からいつもニヤニヤしているという悪い評判がたってしまいました。
友達に、「何がおかしいんだ?」、「何を笑っているんだ?」と、まるで頭がおかしいように言われます。
いつもニヤニヤしていると、バカみたいにみえるようです。
笑うまいとしても、いつのまにかニヤニヤしてしまいます。

上戸彩のことを考えるとき、ニヤニヤするのをやめる方法はありませんか。

Aベストアンサー

それは、無理でしょう。彼女のこと好きなんでしょう。
彼女のことを考えているときに
顔を見られないようにするくらいしか思いつかないですね。

Qクエリで月をまたぐ場合の合計を出す場合

こんにちは。

下記のような状態で4月の滞在日数の合計を求めたいです。

ただし、到着日が前月にまたがっている場合や出発日が翌月にまたがっている場合があり
4月以外の分の滞在日数を除いて合計を出す必要があります。

例)到着:20110331出発:20110402 滞在日数2 ⇒ 3/31分を除き、滞在日数1とする

到着日   出発日  滞在日数  

20110412  20110413    1      
20110331  20110402   2       ★2⇒1
20110428  20110503    5      ★5⇒3
20110416  20110418    2      


アドバイスいただければ幸いです。

Aベストアンサー

添付図参照

C1: 2011
  書式「G/標準"年"」

C2; 4
  書式「G/標準"月度滞在日数"」

C3: =MIN(B3,DATE(C$1,C$2+1,0))-MAX(A3,DATE(C$1,C$2,1))
  書式「G/標準」

Q最近、一人でニヤニヤしたこと

最近、一人でニヤニヤしたこと
 何かありましたか?

もしあったら、一人だけで楽しまないで、そのニヤニヤをおすそ分けして下さい。

Aベストアンサー

おへその掃除をサボってたら
ゴッソリいっきにとれたこと。

そして激しく臭かったこと。

Qエクセル2007 月/日 時/分の書式で日付、時間を入力 & 経過時間を計算

いつもお世話になっております。
よろしくお願いいたします。

エクセル2007を使用して以下は可能でしょうか?

・セルA1 と セルB1に MM/DD HH/MM(月/日 時/分) のフォーマットで日付&時間を入れB1からA1を引き、経過時間を割り出す。

セルの書式設定で、年 を省いて MM/DD HH/MM とする書式が見つかりませんがこれは不可なのでしょうか。
もし、上記不可でしたら、代替え案とその具体的な方法(式など)をおしえていただけますでしょうか?

よろしくお願いいたします。

Aベストアンサー

》 エクセル2007を使用して以下は可能でしょうか?

私の Excel 2002 で可能なので、大丈夫だと推測します。


》MM/DD HH/MM とする書式が見つかりませんが…

(Excel 2002 の場合ですが)それは“日付”の場所にないということでしょう?“ユーザー定義”の場所で貴方自身で作れば好い。


人気Q&Aランキング

おすすめ情報