教えて下さい。
Accessのファイルに3つのテーブルがあります。
(1)会員マスター(現在6千件程度)
[会員番号(数値・主)]、[名前]、[入会年月日]、[性別]
(2)課金記録(2から3万件程度、分割払いの人もいて、数年先の支払期限のレコードも多数)
[rid(オートナンバー・主)]、[会員番号]、[請求金額]、[支払期限]、[金額確定日]、[支払済(bool)]
(3)支払い記録(1万件程度、入金の記録)
[rid(オートナンバー・主)]、[会員番号]、[入金金額]、[入金日]
というフィールド構成です。[会員番号]でのリレーション設定はしてないです。
EXCELのVBAで、ACCESSファイルからこの3つのテーブルのデータを取り込み、最終的には、エクセルのシート上に、
入会年が○年○月から○年○月までの人のうち、支払期限が×年×月×日までの請求金額の合計が、これまでの入金額の合計を上回っている人(このままでは×年×月×日の期限の日に支払い処理ができない人)だけを抽出して、
**[会員番号][名前][性別][不足金額]**という表を作りたいです。
最初は、とにかくまずテーブルのデータをぜんぶシートに取り込んで、何回もループを回して足し算引き算並べ替え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くらいの体感時間でした。 あとは、 - 取り込んだ会員マスターの表の各行に、取り込んだ2つの集計表を会員番号で照合して並べ、 - 会員番号行ごとに、金額を引き算して、 - 引き算結果がマイナスになる行だけ残す これで目的の表が完成となります。 しかし、これでもまだ時間がかかります。 せっかくだから会員番号で紐つけして、すべてのテーブルをまとめて取得できないかと調べたのですが、**JOIN**とか**HAVING**という句を使えばできるかもしれないというところで能力の限界を迎えました。3つのテーブルはムリでも2つのテーブルの紐つけ一括抽出はできるようです。 どなたか、適切な strQ="**この部分**" をご教示いただけないでしょうか? 少しだけでもよりエクセルの手間が省ければ時間が短縮できます。 よろしくお願いいたします。
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/03/26 15:46
2018/03/26 15:53
2018/03/26 16:30