Accessから効率的にデータを取得するSQL文
解決済
回答 1
投稿
- 評価
- クリップ 0
- VIEW 2,640
教えて下さい。
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="この部分" をご教示いただけないでしょうか？
少しだけでもよりエクセルの手間が省ければ時間が短縮できます。
よろしくお願いいたします。
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 過去に投稿した質問と同じ内容の質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
checkベストアンサー
+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、文字なら""（空文字)
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう！
- ただいまの回答率 88.23%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
2018/03/27 00:46
ひとつ教えていただきたいのですが、VBAコードだと変数を介することで、抽出条件（支払期限の日付など）を都度変更することができるのですが、アクセスファイル内にクエリを作ってしまうと、それができないってことはないでしょうか？
2018/03/27 00:53
2018/03/27 01:30
ただ、両回答を見比べるだけでも勉強になります。
深夜にかかわらず、本当にありがとうございました。