２つのSQL文を統合したい

Question

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

Accessのファイルに２つのテーブルがあります。

（１）会員マスター
[会員番号（数値・主）]、[氏名]、[ふりがな]、[費目1総額]、[費目2の総額]…[費目15の総額]、[完済(bool)]

（２）課金記録(分割払い計画のようなもの）
[rid（オートナンバー・主）]、[会員番号]、[費目番号]、[金額]、[支払期限]、[支払済（bool）]
（費目番号は1から15だけでなく、支払方法によってかかる事務手数料などの16や30などいろいろある）

定期的に、すべての支払を終えてる人を抽出し一人一人について人が目で見ながら手動で「これで完済であると確認し決定し処理（＝[完済]フィールドをTRUEにする）」するための候補リストを作成したいです。
ExcelVBAから、このACCESSファイルにアクセスして、レコードセットを取得して、Excel上で確認作業をします。VBA上でACCESS

抽出の条件ですが、

(1)[名前]が「退会済」でない人（会員番号の重複をさけるため退会者のレコードも残っています）
(2)[完済]がFALSEである人
(3)会員マスターにおける[費目1の総額]から[費目15の総額]の合計金額が、課金記録における[支払済]=TRUEで、費目番号15以下の金額と同額以上である人（ジャスト同額であるはずで、以上というのはありえないはずだけど）
(4)費目番号にかかわらず[支払済]=FALSEのであるレコードの[請求金額]の合計値が0である人

以上の条件をすべて満たす人を、[会員番号]の昇順で取得したいです。
（支払方法や分割回数が決定してからはじめて課金記録テーブルにデータ（各費目ごと分割1回分で1レコード）が作られるので、「課金記録.支払済=Falseのレコードがない人」という単純な条件で抽出することができません。）

どのようにSQLを書いていいのかわからず、とりあえず、
自分なりに書いてみました。

条件(1)(2)(3)を表すものとして、

SELECT 会員マスター.会員番号, 会員マスター.氏名, 会員マスター.[ふりがな], Sum(会員マスター.費目1総額) AS 費目1の合計, Sum(会員マスター.費目2総額) AS 費目2の合計, Sum(会員マスター.費目3総額) AS 費目3の合計, …… ,Sum(会員マスター.費目15総額) AS 費目15の合計, Sum(課金記録.金額) AS 金額の合計, ([費目1の合計]+[費目2の合計]…+[費目15の合計])-[金額の合計] AS 残債
FROM 会員マスター INNER JOIN 課金記録 ON 会員マスター.会員番号 = 課金記録.会員番号
WHERE (((会員マスター.完済)=False) AND ((課金記録.返済済)=True) AND ((課金記録.費目番号)<=15))
GROUP BY 会員マスター.会員番号, 会員マスター.氏名, 会員マスター.[ふりがな]
HAVING (((会員マスター.氏名)<>"削除済") AND ([残債]<=0)
ORDER BY 会員マスター.会員番号

条件(1)(2)(4)を表すものとして、

SELECT 会員マスター.会員番号, 会員マスター.氏名, 会員マスター.[ふりがな], Sum(課金記録.金額) AS 金額の合計, 課金記録.返済済
FROM 会員マスター INNER JOIN 課金記録 ON 会員マスター.会員番号 = 課金記録.会員番号
WHERE (((会員マスター.完済)=False))
GROUP BY 会員マスター.会員番号, 会員マスター.氏名, 会員マスター.[ふりがな], 課金記録.返済済
HAVING (((会員マスター.氏名)<>"削除済") AND ((Sum(課金記録.金額))=0) AND ((課金記録.返済済)=False))

という2つのSQLができました（これでも、それぞれの条件をしっかり指定できてるかまだ自信がありませんが）。
この2つを合体させるような形で、なんとか1つにまとめらないかと思うのですが、どう書けばよいでしょうか？
あるいは、このように、まずは複数のSQLを作ってから頑張ってまとめようとする方法は間違ってるのでしょうか？

ここまで複雑な条件でも、ひとつのSQL文にまとめられるかどうかもわかりません。
よろしくご教授ください。

Answer 1

こんな漢字で、

SELECT
    会員番号
    ,氏名
    ,ふりがな
    ,費目総額合計
    ,支払金額合計
    ,請求金額合計
FROM
    (SELECT
        m.会員番号
        ,m.氏名
        ,m.ふりがな
        ,(m.費目1総額 + m.費目2総額 + m.費目3総額 + m.費目4総額 + m.費目5総額
        + m.費目6総額 + m.費目7総額 + m.費目8総額 + m.費目9総額 + m.費目10総額
        + m.費目11総額 + m.費目12総額 + m.費目13総額 + m.費目14総額 + m.費目15総額) AS 費目総額合計
        ,SUM(k.金額) AS 支払金額合計
        ,SUM(k2.金額) AS 請求金額合計
    FROM
        (会員マスター AS m INNER JOIN 課金記録 AS k ON
        m.会員番号 = k.会員番号)
        INNER JOIN 課金記録 AS k2 ON
        m.会員番号 = k2.会員番号
    WHERE
        m.氏名 <> '削除済'
        AND m.完済 = False
        AND k.費目番号 <= 15
        AND k2.支払済 = False
    GROUP BY
        m.会員番号
        ,m.氏名
        ,m.ふりがな
        ,(m.費目1総額 + m.費目2総額 + m.費目3総額 + m.費目4総額 + m.費目5総額
        + m.費目6総額 + m.費目7総額 + m.費目8総額 + m.費目9総額 + m.費目10総額
        + m.費目11総額 + m.費目12総額 + m.費目13総額 + m.費目14総額 + m.費目15総額)
    )
WHERE
    費目総額合計 <= 支払金額合計
    AND 請求金額合計 = 0

あるいは、このように、まずは複数のSQLを作ってから頑張ってまとめようとする方法は間違ってるのでしょうか？

SQLは集合演算なので、まずは全部入りのテーブルを作って条件で絞り込んでいくのが良いと思います。

Comments

[neet_studier]

ご回答ありがとうございます。
ただいま、ご回答を解読しつつ試しているのですが、

m.会員番号 = k.会員番号 INNER JOIN 課金記録 k2 ON m.会員番号 = k2.会員番号

のあたりで、「演算子がありません」とエラーがでます。
... INNER JOIN 課金記録 AS k2 ON ...
と書き直しても同じです。
改行を消して一行にしたのがまずいのでしょうか。

[sazi]

せめて、エラーにならないクエリーでないと・・・

[nullpon]

訂正しました。また、費目総額合計と支払金額合計の大小を逆にしました。

[neet_studier]

お手数おかけしました。
勉強になりました。
ありがとうございました。

Answer 2

費目の1～15は正規化して別テーブルにするか、どの道、課金記録で判断できるのでしょうから、項目として定義しないという選択肢もありだと思います。

SELECT mst.*, pay.*
FROM  会員マスター as mst inner join (
         select 会員番号, sum(iif(費目番号<=15, 金額, 0)) as 費目返済済額 , sum(金額) as 返済済総額 
         from   課金記録
         where  返済済=True
         group by  会員番号
      ) as pay
      on mst.会員番号=pay.会員番号
WHERE  完済=False and 氏名<>"削除済" 
  And (Nz(費目1総額)+Nz(費目2総額)+・・・+Nz(費目15総額))<=費目返済済額
  And 返済済総額=0

※payの部分は別途クエリーで作成しておいて、結合する方が分かり易いかも

Comments

[neet_studier]

ありがとうございます。
いただいたご回答を解読しているところです。
お言葉を返すようですが、２点よろしいでしょうか？

最後のAnd ( )内なのですが、
(Nz(費目1総額)+Nz(費目2総額)+・・・+Nz(費目15総額))>=費目返済済額　は、
(Nz(費目1総額)+Nz(費目2総額)+・・・+Nz(費目15総額))<=費目返済済額　と修正していいでしょか？
払うべき金額の合計が、支払済の合計より同じか小さいとなりますから。

また、
Or 返済済総額=0　の部分ですが、
条件にしたいのは「費目16以上の事務手数料等も含めて、支払済=Falseの金額合計が１円以上のもの」ということですので、何らかの形で未返済総額のようなものを出してそれが=0としなくてはならないのではないでしょうか？

私が誤解誤読しているかもしれませんが、いかがでしょうか？

[sazi]

不等号の向きは逆でしたね。
返済済総額は会員の金額の総計ですので、費目による条件は含んでいません。
payの部分だけのクエリーにして確認してみて下さい。

[neet_studier]

夜分に申し訳ありません。
もういちど熟読いたします。

[sazi]

質問では「費目番号にかかわらず[支払済]=FALSEのであるレコードの[請求金額]の合計値が0である人」
とありますが、
「費目16以上の事務手数料等も含めて、支払済=Falseの金額合計が１円以上のもの」なのですか？
合計値の条件が違っていますけど。
それから(4)はAND条件のように書かれていますけど、0円の方だと矛盾するのでor条件と捉えています。

[neet_studier]

いまいちど、私の言葉で整理させてください。

会員マスターの方は、会員登録したときに、各費目1-15の金額が決定します。
しかし、その時点では、課金記録テーブルには何も登録されません。
要支払い金額を費目ごとそれぞれ何回にわけて払うのかを決定してはじめて、課金記録に登録されます。ある費目については分割計画が確定して登録されても別の費目については未登録という状態もあります。
また、課金記録に登録されるときに、手数料などが別費目番号として加えられます。
つまり、会員登録をしたけどまだ分割支払予定が定まってない人などは、課金記録テーブル上では未払い金のレコードがないという状態になってしまいます。
また、課金記録テーブルの費目番号15以下のものについての支払総額がマスターの総額と同じになったとしても、課金記録テーブル内だけに存在している手数料分だけが未払いとして残っているという可能性もあるのです。

複雑なのですが、

・マスターには品目1から15の要支払金額しか登録されていない
・課金記録には、品目1から15以外の要支払金額も登録されている（登録タイミングは別）
・課金記録の支払済＝Falseのレコードがなくても、未登録なだけで、要支払額がすべて支払われたとは限らない
・課金記録の支払済=Trueの総額と、マスターの全費目総額が同じだとしても、課金記録内で登録されている手数料分が支払済=Falseの可能性もある

ということなのです。

今回欲しいリストは、
マスター上の総額がすべて支払われており（課金テーブルの品目15以下の支払済総額と同額）、
かつ
課金テーブルに未払い項目がない

というものなのです。
課金テーブルの支払済フィールドがTrueの場合とFalseの場合のそれぞれに条件をつけなくてはならないところが私にとって混乱の元です。
すみません。

[sazi]

私の質問に対して明確な回答にして下さい。
回答に対して補足的に説明されれば理解できますが、全体的なところを説明されてもどこが回答なのか分かりません。

[neet_studier]

すみません

Q)「費目16以上の事務手数料等も含めて、支払済=Falseの金額合計が１円以上のもの」なのですか？
A)はい

マスターで確定している金額と課金テーブルでの金額が必ずしも一致しないのです。

マスターに登録されても、すぐに課金テーブルに分割請求が登録されるわけではない
→　マスターの総額と、課金テーブルの支払済総額が同額か調べる必要

マスターで確定している分が課金テーブル上でも全て登録され既に支払われていても、課金テーブルにしか登録されていない手数料が未払になっている可能性がある
→課金テーブルに未払がないか調べる必要

紛らわしくてすみません

[sazi]

説明内容からすると、完済している人の確認用リストの条件として、
「費目番号にかかわらず[支払済]=FALSEのであるレコードの[請求金額]の合計値が0である人」
が正しい気がしますが、
「費目16以上の事務手数料等も含めて、支払済=Falseの金額合計が１円以上のもの」
が条件なんですか？

[sazi]

何れにせよ条件はANDのようですから回答のSQLは修正しておきます。
金額条件部分は調整なさって下さい。

[sazi]

SQLのポイントとしては会員マスタと結合して比較するので、「課金記録を会員番号でサマリーしたものと結合する」です。
サマリーする過程で比較に必要な項目を演算にて求めるようにすると簡潔になります。

[neet_studier]

たびたびすみません。
たしかに、「費目にかかわらず[支払済]=Falseの合計が0である」という条件で問題ありません。
ありがとうございました。
いただいたご回答とコメントをもういちど熟読します。勉強になります。

[sazi]

条件については質問にある内容を展開しただけなので、もう一度精査してくださいね。
pay中での「where 返済済=True」は条件として怪しい気がします。
※返済済みはwhereの条件ではなく、sum()による集計時に判定して、返済済みでない金額を集計して判断する必要がありそうに思える。

[sazi]

サンプルデータとそこから得たい結果を具体的に示されると、紛れはなくなります。

[neet_studier]

ありがとうございました。
たしかに、サンプルデータを提示した方が理解してもらいやすい質問になると思います。
ありがとうございました。