質問: "SELECT 会員番号,SUM(請求金額) FROM 課金記録 GROUP BY 管理番号 WHERE 支払期限<#2018/5/1# ORDER BY 会員番号" ``` という感じです。しかも、これがスゴく速く取得できました。生データを受け取ってEXCELで集計するのにくらべて50分の1くらいの体感時間でした。 あとは、 * 取り込んだ会員マスターの表の各行に、取り込んだ2つの集計表を会員番号で照合して並べ、 * 会員番号行ごとに、金額を引き算して、 * 引き算結果がマイナスになる行だけ残す これで目的の表が完成となります。 しかし、これでもまだ時間がかかります。 せっかくだから会員番号で紐つけして、すべてのテーブルをまとめて取得できないかと調べたのですが、**JOIN**とか**HAVING**という句を使えばできるかもしれないというところで能力の限界を迎えました。3つのテーブルはムリでも2つのテーブルの紐つけ一括抽出はできるようです。 どなたか、適切な strQ="**この部分**" をご教示いただけないでしょうか? 少しだけでもよりエクセルの手間が省ければ時間が短縮できます。 よろしくお願いいたします。
回答: クエリーを3つ作成して下さい。 ※一つに纏めることもできますが、分かり易くするために別々にします。 [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、文字なら""(空文字)