よろしくお願いいたします。
Accessのファイルに3つのテーブルがあります。
(1)会員マスター(現在6千件程度)
[会員番号(数値・主)]、[名前]、[入会年月日]、[性別]、[費目1の業者名]、[費目2の業者名]…[費目15の業者名]
(2)課金記録(2から3万件程度、分割払いの人もいて、数年先の支払期限のレコードも多数)
[rid(オートナンバー・主)]、[会員番号]、[費目番号]、[請求金額]、[支払期限]、[金額確定日]、[支払済(bool)]
(3)支払い記録(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をどうやって書けばいいのかわからず苦闘しています。
どのように書くべきか、ご教示ください。
以上です。
よろしくお願いします。
回答2件
あなたの回答
tips
プレビュー