Accessから効率的にデータを取得するSQL文

Question

教えて下さい。

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

（１）会員マスター（現在6千件程度）
[会員番号（数値・主）]、[名前]、[入会年月日]、[性別]

（２）課金記録（2から3万件程度、分割払いの人もいて、数年先の支払期限のレコードも多数）
[rid（オートナンバー・主）]、[会員番号]、[請求金額]、[支払期限]、[金額確定日]、[支払済（bool）]

（３）支払い記録（1万件程度、入金の記録）
[rid（オートナンバー・主）]、[会員番号]、[入金金額]、[入金日]

というフィールド構成です。[会員番号]でのリレーション設定はしてないです。

EXCELのVBAで、ACCESSファイルからこの３つのテーブルのデータを取り込み、最終的には、エクセルのシート上に、
入会年が○年○月から○年○月までの人のうち、支払期限が×年×月×日までの請求金額の合計が、これまでの入金額の合計を上回っている人（このままでは×年×月×日の期限の日に支払い処理ができない人）だけを抽出して、

**[会員番号][名前][性別][不足金額]**という表を作りたいです。

最初は、とにかくまずテーブルのデータをぜんぶシートに取り込んで、何回もループを回して足し算引き算並べ替えVLOOKUPなどを繰り返して作ろうとしていました。

    conStr = "Provider =Microsoft.ACE.OLEDB.12.0;Data Source = C:/abc.accdb" 
    con.Open (conStr)
    strQ = "SELECT * FROM 会員マスター"
    rsRs.Open strQ, con, adopenkeyset, adlockoptimistic

という感じです。
しかし、これではとても時間がかかるし、エクセルがフリーズしたみたいになりました。
いろいろ調べて勉強したのですが、SQL文では、テーブル名だけじゃなく、その中のフィールド名も指定できること（*じゃなく、そこにフィールド名を書けばよい）を知りました。
また、WHERE句を使えば、必要なレコードだけを抽出できるとも知りました。
そして、なんと、GROUPという句やSUM句を使えば、あらかじめ会員番号ごとに金額が合計されたデータを受け取ることができるのだと知りました。
```ここに言語を入力
strQ = "SELECT 会員番号,SUM(請求金額) FROM 課金記録 GROUP BY 管理番号 WHERE 支払期限<#2018/5/1# ORDER BY 会員番号"

という感じです。しかも、これがスゴく速く取得できました。生データを受け取ってEXCELで集計するのにくらべて50分の1くらいの体感時間でした。 

あとは、
- 取り込んだ会員マスターの表の各行に、取り込んだ２つの集計表を会員番号で照合して並べ、
- 会員番号行ごとに、金額を引き算して、
- 引き算結果がマイナスになる行だけ残す 

これで目的の表が完成となります。

しかし、これでもまだ時間がかかります。
せっかくだから会員番号で紐つけして、すべてのテーブルをまとめて取得できないかと調べたのですが、**JOIN**とか**HAVING**という句を使えばできるかもしれないというところで能力の限界を迎えました。３つのテーブルはムリでも２つのテーブルの紐つけ一括抽出はできるようです。

どなたか、適切な　strQ="**この部分**"　をご教示いただけないでしょうか？
少しだけでもよりエクセルの手間が省ければ時間が短縮できます。

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

Answer 1

クエリーを３つ作成して下さい。
※一つに纏めることもできますが、分かり易くするために別々にします。

[Q_請求額計]

SELECT 会員番号, Sum(請求金額) AS 請求金額計
FROM   課金記録
WHERE  支払期限<#2018/5/1#
GROUP BY 会員番号;

[Q_入金額計]

SELECT 会員番号, Sum(入金金額) AS 入金額計
FROM   支払い記録
GROUP BY 会員番号;

[Q_入金不足者]

SELECT 会員マスター.会員番号, 会員マスター.名前, 会員マスター.性別, 請求金額計-NZ(入金額計) AS 不足額
FROM  (会員マスター INNER JOIN Q_請求額計
       ON 会員マスター.会員番号 = Q_請求額計.会員番号
      ) LEFT JOIN Q_入金額計 
       ON 会員マスター.会員番号 = Q_入金額計.会員番号
WHERE (請求金額計-NZ(入金額計))>0
ORDER BY 会員マスター.会員番号
;

※差し引きが逆だったので、修正
※入金の一切ない人の考慮が漏れていたので、修正

それぞれ、新規のクエリーデザインを開き、右クリックで「SQLビュー」にして、内容を貼り付け、
名前を付けて保存して下さい。

求める結果は「Q_入金不足者」になります。
尚、「課金記録」と「支払い記録」については、会員番号のインデックスが無ければ作成した方が早くなるでしょう。

追記

集計した後にJoinすべきだったので修正

SELECT 会員マスター.会員番号, 会員マスター.名前, 会員マスター.性別, 請求金額計-Nz(入金額計) AS 不足額
FROM  (会員マスター INNER JOIN (
          SELECT 会員番号, Sum(請求金額) AS 請求金額計
          FROM   課金記録
          WHERE  支払期限<#2018/5/1#
          GROUP BY 会員番号
       ) AS Q請求額計
       ON 会員マスター.会員番号 = Q請求額計.会員番号
      ) LEFT JOIN (
          SELECT 会員番号, Sum(入金金額) AS 入金額計
          FROM   支払い記録
          GROUP BY 会員番号
      ) AS Q入金額計 
       ON 会員マスター.会員番号 = Q入金額計.会員番号
WHERE (請求金額計-Nz(入金額計))>0
ORDER BY 会員マスター.会員番号

※Nz(item)はitemがNullの場合、Itemの属性に合わせた値を返却します。
属性が数値なら0、文字なら""（空文字)

Comments

[neet_studier]

さっそく教えていただきありがとうございます！！！とても複雑ですが、なんとなく意味を感じとれます。（自分ではとても書けないですが）

ひとつ教えていただきたいのですが、VBAコードだと変数を介することで、抽出条件（支払期限の日付など）を都度変更することができるのですが、アクセスファイル内にクエリを作ってしまうと、それができないってことはないでしょうか？

[sazi]

パラメータクエリーとかユーザー定義関数するなどで、クエリー内に条件を持つこともできますが、VBAでSQL記述するということなら、一つに纏めたものを追記しますので、しばしお待ちを。

[neet_studier]

教えていただいたうえにケチをつけるような恰好になってしまい申し訳ありませんでした。
ただ、両回答を見比べるだけでも勉強になります。

深夜にかかわらず、本当にありがとうございました。