ExcelとAccessの連携 最適な問い合わせ手順

Question

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

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

（１）会員マスター（現在6千件程度）
[会員番号（数値・主）]、[名前]、[入会年月日]、[性別]、[費目1の業者名]、[費目2の業者名]…[費目15の業者名]

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

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

というフィールド構成です。[会員番号]でのリレーション設定はしてないです。
会員毎に支払期限は「毎月◎日」と決まっています。同じ会員が、一ヶ月のうち複数の日付で課金されることはなく、また、同じ会員で同じ費目番号の課金は、月内で1つ以下です。
どの会員も、支払期限は「毎月10日」「毎月20日」「毎月末日」のいずれかです。

今回、EXCELのVBAから、このACCESSファイルに接続し、以下の処理をしたいです。
**（A）**会員毎の現在の預かり金額（[入金額の合計]-[これまですべての支払済の合計額]）が、今月が支払期限で、かつ未だ支払済みでない（支払済=False）の請求金額合計と同じかそれ以上の人のレコードに対して支払済=Trueに変える
**（B）**今回支払済みをTrueに変えた人が誰なのかわかるように、自エクセルファイルのシート上に
[会員番号][名前][費目1の業者名][費目1の金額]…[費目15の業者名][費目15の金額]
（支払期日がはやい順、会員番号が若い順）
というリストを作る

自分で考えた手順は以下のようなものです。
0. 処理対象となるレコードセットを取得し、会員番号だけを、作業用シートにリスト展開する
0. 作業用シートの会員番号リストを上から下まで順に、その会員番号で支払期限が該当月のレコードセットを取得し、支払済をtrueに変えるべくレコードをedit-updateしたたうえで、作業用シートの会員番号の右列に、そのレコードの情報（金額や業者名など）を該当列（費目番号に該当する列）転記する
0. 作業用シートを支払期日順、会員番号順に並べ替え
0. 作業用シートが、そのまま目的のリストになっている

以上です。

Q1
上記の手順はまどろっこしいでしょうか？何かもっといい考え方はありますか？
とくに(2)では、何千回もAccessと接続しレコードセットを取得することになります。これは、システムの負荷とかを考えると、利口なやり方ではないのではという気がするのですが、一般的に、何千回もDBと接続してレコードセットを取得し直すというのは、躊躇するほどのことでもないのでしょうか？

Q2
(1)のための、excelからAccessに問い合わせるSQL文を、以前にsazi様から教えていただいたものを元に手探りで書いてみました。

SELECT 会員マスター.会員番号,会員マスター.名前, Nz(入金額計)-支払済金額計 AS 預かり金額
FROM  (会員マスター INNER JOIN (
          SELECT 会員番号, Sum(請求金額) AS 支払済金額計
          FROM   課金記録
          WHERE  支払済=TRUE 
          GROUP BY 会員番号
       ) AS Q支払済金額計
       ON 会員マスター.会員番号 = Q支払済金額計.会員番号
      ) LEFT JOIN (
          SELECT 会員番号, Sum(入金金額) AS 入金額計
          FROM   支払い記録
          GROUP BY 会員番号
      ) AS Q入金額計 
       ON 会員マスター.会員番号 = Q入金額計.会員番号
WHERE (Nz(入金額計)-支払済金額計)>0
ORDER BY 会員マスター.会員番号

しかしこれでは、まだとりあえず「預かり金がゼロ以上の人とその預かり金額」の一覧しか出て来ません（それすら正しくできているか不安ですが）。
これをさらに内部に含んで、「今月請求金額合計がこの預かり金額と同額以下」の人を出さなくてはなならいのですが、SQLを包み込むようなSQLをどうやって書けばいいのかわからず苦闘しています。
どのように書くべきか、ご教示ください。

以上です。
よろしくお願いします。

Comments

[coco_bauer]

手順の２のところで、支払い済みかどうかを判断するためには"請求金額合計"を事前に算出しておく必要がありますが手順に書かれていません。課金は支払い前月までに確定しているという前提があるようですが、そういう仕組みになっているのでしょうか？　課金、預り金の預け入れ、支払いのタイミングが明記されていないことに不安を覚えます(質問に書かれているのが正しいのかどうか判断できかねます)

[neet_studier]

質問文が曖昧ですみませんでした。 課金額が確定するのは、この処理をするずっと前で、少なくとも前々月には確定してますので、この処理をした後に、その会員に別の費目でその月が支払期限の課金が発生することはありません。 ただ、入金は、毎日いつ発生するかわからないので、毎日この処理をして、「今日までの入金分で今月支払分が足りている人は支払済として処理をしリスト化しておく」という作業をしたいのです。

Answer 1

Q1
「課金記録」の支払済を変更するという処理をエクセルで行うということだと思いますが、「支払い記録」がどの「課金記録」に対する入金なのかがあれば、そもそも支払済とういう項目すら不要です。

現状それはないので、入金の累計を使用して、「課金記録」の請求額が充当されるところまでを支払済にするという処理を行えば、支払済を設定する処理は自動化は可能かと思います。

但し、一部入金などのイレギュラーに対応するためには、どうしても補正的な入力は必要でしょうから変更分だけの更新処理は考慮したほうが良いかもしれません。

Q2
※ちょっと読み違えていたので修正
「今月請求金額合計」を求めたものを結合すればよいかと思います。
「今月請求金額合計」については支払済でない今月以前の「課金記録」としています。

SELECT 会員マスター.会員番号,会員マスター.名前, Nz(入金額計)-Nz(支払済金額計) AS 預かり金額
     , Nz(今月請求金額合計) as 今月請求金額
FROM  ((会員マスター LEFT JOIN (
          SELECT 会員番号, Sum(請求金額) AS 支払済金額計
          FROM   課金記録
          WHERE  支払済=TRUE 
          GROUP BY 会員番号
       ) AS Q支払済金額計
       ON 会員マスター.会員番号 = Q支払済金額計.会員番号
      ) LEFT JOIN (
          SELECT 会員番号, Sum(請求金額) AS 今月請求金額合計
          FROM   課金記録
          WHERE  支払済=False and 支払期限<#2018/6/1#
          GROUP BY 会員番号
       ) AS Q今月請求金額合計
       ON 会員マスター.会員番号 = Q今月請求金額合計.会員番号
      ) LEFT JOIN (
          SELECT 会員番号, Sum(入金金額) AS 入金額計
          FROM   支払い記録
          GROUP BY 会員番号
      ) AS Q入金額計 
       ON 会員マスター.会員番号 = Q入金額計.会員番号
WHERE (Nz(入金額計)-Nz(支払済金額計))>0
ORDER BY 会員マスター.会員番号

Comments

[neet_studier]

このたびもご解答いただきありがとうございます。

ひとつ教えていただきたいのですが、お答えの最後から2行目
WHERE (Nz(入金額計)-Nz(支払済金額計))>=Nz(今月請求金額合計)　とすると
支払済をFalse→Trueに変えるべき会員番号一覧が取得できませんでしょうか？

[sazi]

抽出したいのは、「未収金＞今月請求定額」という条件だと思いますが、
支払い記録.入金金額に請求額を含んでいるという前提であれば、「Nz(入金額計)-Nz(支払済金額計)」は未収金として扱えると思います。
尚、回答の今月請求金額合計は「当月定額請求＋過去未収金」の意味合になっていますので、注意して下さい。

[sazi]

あ、質問の意図が違いますね。
単に支払済のフラグが不足しているものは、
Nz(入金額計)>Nz(支払済金額計)　つまり、Nz(入金額計)-Nz(支払済金額計)>0
でいいんじゃないでしょうか。

[neet_studier]

質問文がわかりにくくてすみません。
私がやりたいのは、「今の時点で今月分の支払処理をできる人（すでに充分なお金を入金している人）の請求レコードを抽出してその人の今月分の支払を済にしてしまう」ということです。請求金額は先月以前に確定しているものの、入金タイミングも金額もざっくりしていて入金データは常時更新されています。なので、随時この処理をしたいのです。

よって、
支払済FalseをTrueにするために欲しい情報は、
（これまでの入金額合計）－（これまでの支払済額合計）＝＞（今月期限かつ未支払の請求金額合計）の人の会員番号
です。こうやって取得した会員番号を元に、改めて、その会員番号かつ今月支払課金レコードを別SQLで取得して支払済をTrueに変更します。

教えていただいたのは、
（これまでの入金額合計）－（これまでの支払済額合計）＞0　つまり今のところ預かり金がある人の会員番号
かと思いますが、出てくるフィールドに、預かり金額と今月請求金額があるので、エクセルシート上で（預かり金額）＝＞（今月請求金額）を計算して支払済True化の対象たりうるか調べることはできます。

ただ、素人考えながら、最後から2行目を
WHERE (Nz(入金額計)-Nz(支払済金額計))>=Nz(今月請求金額合計)　
とするとその手間が省けるのでは、と考えた次第です。

[sazi]

債権管理の方に意識が合ったので、未収を管理するものとして請求と入金実績をごっちゃにして考えていました。
入金実績と消込の関係で考えると、言われている条件式で問題ないと思います。

[neet_studier]

ありがとうございました。
何度も教えていただき、本当に助かります。

Answer 2

Q1 更新が必要なら何回だろうとSQLを投げる必要があるでしょうね。
ただ、何回も接続＋切断をするかと言われると、１回接続⇒SQLｘ更新回数⇒切断という流れで処理できたような気がします。
※自分で確かめるなり確認してください。

Q2 サブクエリ、という単語で調べてください。

Comments

[neet_studier]

ありがとうございました。
DBとの接続を維持したまま、レコードセットの取得だけを何度もやるということですね。