質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.75%

  • SQL

    2223questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • VBA

    1635questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1397questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Access

    389questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

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

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 178

neet_studier

score 10

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

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の金額]
(支払期日がはやい順、会員番号が若い順)
というリストを作る

自分で考えた手順は以下のようなものです。

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

以上です。

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をどうやって書けばいいのかわからず苦闘しています。
どのように書くべきか、ご教示ください。

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • coco_bauer

    2018/05/28 09:21

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

    キャンセル

  • neet_studier

    2018/05/28 09:34

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

    キャンセル

回答 2

checkベストアンサー

+2

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 会員マスター.会員番号

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/05/28 15:30 編集

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

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

    キャンセル

  • 2018/05/28 16:11

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

    キャンセル

  • 2018/05/28 16:15 編集

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

    キャンセル

  • 2018/05/28 17:23 編集

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

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

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

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

    キャンセル

  • 2018/05/28 17:44

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

    キャンセル

  • 2018/05/28 22:04

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

    キャンセル

+1

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/05/28 22:06

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.75%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • SQL

    2223questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • VBA

    1635questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1397questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Access

    389questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。