Access クエリ内での計算式の作り方(考え方？)について

Question

<追記20171223>
クエリ1＝[契約日判定]を作成(回答参考)
クエリ2＝[契約　売上額]*[クエリ1]![契約日判定]
フォーム＝開いておいて契約日判定に必要な、開始日・終了日を入力済20171201～20171231
クエリ2は集計を設定しており、
集計を合計で実行すると、[クエリ1]![契約日判定]のパラメータボックスが表示されてしまい、
集計を演算で実行すると、[クエリ1]![契約日判定]指定された式が含まれていません　と表示されてしまいます。

なにが引っかかっているのかピンときません・・・

<追記>
頭が冷えてきて、結局自分のやりたい処理が絞れてきたので、追記します。
・他で計算したフィールドを、他の計算で使用したい。
・計算式を作る上で便利な機能を知りたい
出来るだろと思って作った式が全然ダメで、下のような頭おかしい文章になっちゃいました。どうかよろしくお願いします。

今回は、特定の処理を行いたいという内容ではなく、
作り方の手順？がおかしい気がして確認したいです。

どうしてもExcelでの計算式の作り方が頭から抜けず、クエリの中で下のような式を作ってしまいました。

①税: IIf([T_物件マスター]![税区分]="内",
②(([契約　税額]*IIf([forms]![F_売上締め]![開始日]<=[契約時請求日] And [forms]![F_売上締め]![終了日]>=[契約時請求日],1,0))+([引渡　税額]*IIf([forms]![F_売上締め]![開始日]<=[引渡時請求日] And [forms]![F_売上締め]![終了日]>=[引渡時請求日],1,0))),

③Int(((([契約　売上額]*IIf([forms]![F_売上締め]![開始日]<=[契約時請求日] And [forms]![F_売上締め]![終了日]>=[契約時請求日],1,0))+([引渡　売上額]*IIf([forms]![F_売上締め]![開始日]<=[引渡時請求日] And [forms]![F_売上締め]![終了日]>=[引渡時請求日],1,0)))+Nz(([事務手数料]*IIf([forms]![F_売上締め]![開始日]<=[引渡時請求日] And [forms]![F_売上締め]![終了日]>=[引渡時請求日],1,0)),"0"))*0.08))

長いので中身を説明すると、
①(最初のIIF)物件ごとに内税処理・外税処理があり、
②(正)内税なら開始日～終了日間で既に計算している消費税を合計する。
③(誤)外税なら開始日～終了日間の税抜き金額を合計して、それに0.08かける。

試した内容としては、
例えばクエリ内で先に合計するフィールドを作って、式内に埋め込む→サブクエリは使用できない？警告が出てクエリ実行できない
他のクエリで同じような計算をしているので、それを引っ張ってくる→パラメータクエリが出たり、式がクエリに表示されてない等で実行できない

結局、1つの式の中でそれぞれ計算させて、それをまた計算して・・・という形になっています。

同じ項目の繰り返し([forms]![F_売上締め]![開始日]<=[契約時請求日] And [forms]![F_売上締め]![終了日]>=[契約時請求日])とかは何か代入というか、名前を付けてそれを式に使用できる気もするし、
そもそも1つのクエリの中で計算してるのがおかしいのかもしれないし、もっと簡単にできる気がしています。

Excelと違って表計算ソフトだから、それでやるしかないならこれでやりますが、知らずに苦労しているのはきついので何かあれば教えていただきたいと思います・・・長々とすみませんが、何卒よろしくお願いいたします。

Answer 1

税区分	契約　売上額	契約　税額	契約時請求日	引渡　売上額	引渡　税額	引渡時請求日	事務手数料
内	1080	80	2017-12-10	2160	160	2017-12-20	100
外	3000	240	2017-12-15	4000	320	2017-12-25	100

こういう感じになっているという解釈でよろしいですか？

簡単にする方法ですが
契約日判定:IIf([forms]![F_売上締め]![開始日]<=[契約時請求日] And [forms]![F_売上締め]![終了日]>=[契約時請求日],1,0)
引渡日判定:IIf([forms]![F_売上締め]![開始日]<=[引渡時請求日] And [forms]![F_売上締め]![終了日]>=[引渡時請求日],1,0)
みたいな形で別名を与えて式を簡略化するくらいしかないかと思います。
丸２の式→(([契約　税額][契約日判定])+([引渡　税額][引渡日判定]))

あとは丸３の式後半は分配法則使って少しだけ簡単になります。
([引渡　売上額]+Nz([事務手数料],0))
*IIf([forms]![F_売上締め]![開始日]<=[引渡時請求日] And [forms]![F_売上締め]![終了日]>=[引渡時請求日],1,0)

日付に関するところは、本来データの検索条件に追いやって内容の計算を簡単にしたいのですが
日付の判定が２つ並んでいるので難しいものになってしまっていますね。

Comments

[morikawa0208]

ご回答ありがとうございます！
＞丸２の式→(([契約　税額]*[契約日判定])+([引渡　税額]*[引渡日判定]))
この部分盲点でした・・・検索条件？も名前を付けて使用できるんですね！
ちょっとまだ試していませんが、そうすると別で1個クエリ(計算式クエリ)を作って、その中で色んな計算をさせておく。他のクエリでは計算式クエリから引っ張ってくる・・・というのを妄想していますが、できるんでしょうか？ちょっと試してみます。ありがとうございました！

[sousuke]

可能な限りSQLビューの中身を見せてもらったほうがいいかと思います。
クエリ２の段階で「集計」をせずに思い通りの結果を表示できるかどうかから
始めたほうがよろしいかと思います。
別クエリを作ることでももちろん可能ですが、AccessのSQLはすこし特別なので
「その中で作った計算式であっても使える」ときがあります。

例えばですがクエリ１の中で内税外税の金額の計算できます。
select A.*
,契約日判定:IIf([forms]![F_売上締め]![開始日]<=[契約時請求日] And [forms]![F_売上締め]![終了日]>=[契約時請求日],1,0)
,引渡日判定:IIf([forms]![F_売上締め]![開始日]<=[引渡時請求日] And [forms]![F_売上締め]![終了日]>=[引渡時請求日],1,0)
,内税金額:[契約　税額]*[契約日判定]+[引渡　税額]*[引渡日判定]
from テーブル as A

あれこれを一度にやらずに、どこまでできたかを都度データシートビューで
見ていくほうがいいと思います。違っているところもわかります。
１、「契約日判定の式」ができているか
２、「金額計算の式」ができているか
３、「その列を集計する」
という感じです。
外税の金額に関して理解が違っていましたね、すみませんm(_ _;)m