メインテーブルと連結しているサブテーブルの特定データを一緒にコピー(INSERT)したい

Question

登録されているマスターデータを受注データにコピーしたいです。

その際に連結している1:多のサブテーブルの情報も一緒にコピーしたいのですが

コピーしたいのですが、どうしたら良いでしょうか？

例）

メニューマスターテーブル　(コピー元)

メニューID　　品名　　 　基本単価

1          　　　クッキー　　 300

2          　　　パフェ　　　 850

3          　　　クレープ        500

工程サブマスターテーブル　(コピー元)

工程ID　　メニューID　　工程　　    　　標準時間

1     　       1           　　　混ぜる　　　　　5

2        　　 1           　　　焼く           　　　20

3            　1           　　　袋詰め       　　　10

4            　2           　　　アイス乗せ    　　5

5            　2           　　　クリーム乗せ　　3

6            　3           　　　生地焼く　　　　5

7　　　 　3           　　　クリーム乗せ    　3

受注テーブル　(マスターテーブルAからのコピー先)

入力されたメニューIDがマスターと一致する場合単価データをコピーしたい。

参照でなくコピーするのは販売単価はその時により変わるため。

ただし基本単価は一度コピーしたい。

オートNo

受注ID　 メニューID　　単価　　受注日

1         　　2　　　　　　850　   2020/9/30　 

2          　   3　　　　　　450　   2020/10/2

3                2                      800       2020/10/5

工程テーブル　（マスタテーブルBからのコピー先）

受注入力と連結されているテーブル。

メニューIDが入力され、マスターデータがコピーされた際に一緒にサブマスターテーブルBから

紐づく工程データをコピーしたい。（このテーブルも後で時間や工程をマスターと切り離して変更する

可能性があるため参照ではなくコピーしておきたい）

オートNo

実施工程ID　　　受注ID　　工程                  　　実施日　　　　時間

1　　　　　 　　1　  　   　アイス乗せ    　　

2                　　　1            　クリーム乗せ

3            　   　　 2          　　生地焼く　　　

4　　　 　            2             　クリーム乗せ   

5　　　　　         3　  　   　アイス乗せ    　　

6                　　　3            　クリーム乗せ 

例が分かりづらくすみません。

困っていることはInsertを使って新規受注を入れた際に、マスターの金額をコピーするところはできたのですが、

その時にInsertされた受注ID(オートナンバー)が分からず、受注IDに紐づいた工程データをマスターからInsertで

コピーする方法が分からず困っています。

ご教授のほどよろしくお願いします。

Answer 1

> そこでdaoを使って.AddNew　→　.Update　→　.Bookmark = .LastModified　→　受注IDを取得・格納

> このとき.Bookmark=.LastModifiedは複数人で使用している場合、挿入した新規レコードと異なる場合が

> ありますでしょうか。

ありません。

（他のユーザーによるレコードの追加/変更操作の影響を受けないし、

そもそもブックマーク自体がそういう性質の情報ではない）

> DoCmd.SetWarnings False

> DoCmd.RunSQL strSQL_MK

> DoCmd.SetWarnings True

この部分に関しては、DoCmd オブジェクトの RunSQL メソッドではなく

DAO.Database オブジェクト（コード上では daoDB ）の

Execute メソッドによって SQL を実行するようにされた方がよいと思います。

（トランザクション制御の必要があるのであれば）

Answer 2

> 教えていただいたクエリの場合は、工程テーブルにない受注IDを

> 受注テーブルから抽出し、そのIDを使って工程テーブルにInsertする

> という考え方で合っていますでしょうか？

1. [受注テーブル]に新規レコードを追加する INSERT INTO 文

   （既に作成されている処理）を実行する。

2. [受注テーブル]と[工程サブマスターテーブル]とを

   [メニューID]同士で内部結合する。

3. 2 の結果のうち、[受注ID]の値が同じであるレコードが

   [工程テーブル]に存在していないレコードを抽出する。

4. 3 の結果から、[受注テーブル]の[受注ID]と

   [工程サブマスターテーブル]の[工程]を取得する。

5. 4 の結果を（ INSERT INTO 文によって）[工程テーブル]に追加する。

というのが大まかな考え方です。

> もしそうであれば、実は工程テーブルに

> データが存在しない受注データがあるため

> このクエリでは難しそうです。

ここで肝心なのは、上記 1 の操作によって

[受注テーブル]に追加されたレコードと同じ値の

[メニューID]を持つレコードが**（[工程テーブル]ではなく）**

[工程サブマスターテーブル]に存在しているかどうかです。

同じ[メニューID]のレコードが[工程サブマスターテーブル]に

存在していないのであれば、上記 2 の段階で

[工程テーブル]への追加対象から除外されます。

Answer 3

> 困っていることはInsertを使って新規受注を入れた際に、

> マスターの金額をコピーするところはできたのですが、

> その時にInsertされた受注ID(オートナンバー)が分からず、

> 受注IDに紐づいた工程データをマスターからInsertで

> コピーする方法が分からず困っています。

（SQL ビュー）

---

INSERT INTO [工程テーブル]

      ([受注ID],

       [工程])

SELECT [受注テーブル].[受注ID],

       [工程サブマスターテーブル].[工程]

FROM [受注テーブル]

INNER JOIN [工程サブマスターテーブル]

ON [受注テーブル].[メニューID] = [工程サブマスターテーブル].[メニューID]

WHERE NOT EXISTS (SELECT tmp.*

                  FROM [工程テーブル] tmp

                  WHERE tmp.[受注ID] = [受注テーブル].[受注ID])

ORDER BY [受注テーブル].[受注ID],

         [工程サブマスターテーブル].[工程ID];

---

以上のような追加クエリ（ INSERT INTO 文）を実行したい、

ということでしょうか。

Answer 4

sk.exe様

ご丁寧な説明、ありがとうございます。

ご教授いただいたクエリで解決しそうな気がします。

開発中のシステムに組み込んでみます。

クエリを読み解く力をレベルアップしていきたいです。

本日、自分なりに色々と調べてdaoを使った下記方法で解決できそうになったのですが、

気がかりな部分があります。

当方のバックデータはSQLServer上にあります。

そのためオートナンバーIDは保存後まで取得できません。

そこでdaoを使って.AddNew　→　.Update　→　.Bookmark = .LastModified　→　受注IDを取得・格納

このとき.Bookmark=.LastModifiedは複数人で使用している場合、挿入した新規レコードと異なる場合が

ありますでしょうか。一人でテストしている場合には問題なく動いたのですが。

もしこういった使い方が問題ないようであれば、挿入直後に関連する別のテーブルの値を書き換えることが

できるようになり、便利だと思ったのですが。

具体的なコードは下記のとおりです。

　dim JID as Long '挿入した受注ID格納用変数

    Set daoRS_J = daoDB.OpenRecordset("受注テーブル", dbOpenDynaset, dbSeeChanges)

    Set daoRS_EDI = daoDB.OpenRecordset("T_cnvEDIdata", dbOpenSnapshot)　←CSVデータの格納先

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　(格納時にメニューマスターと照合して

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　基本単価に更新しています。)

    Do Until daoRS_EDI.EOF

        daoRS_J.AddNew

        daoRS_J!メニューID = daoRS_EDI!メニューID

        daoRS_J!単価 = daoRS_EDI!単価

        daoRS_J!受注日 = date

        daoRS_J.Update

        daoRS_J.Bookmark = daoRS_J.LastModified

        JID = daoRS_J!受注ID

        'マスタ工程が登録されている場合はマスタ工程をコピーする   

        strSQL_MK = "INSERT INTO 工程テーブル(受注ID, 工程) " _

                          & "SELECT  " & JID & ", 工程 " _

                          & "FROM 工程サブマスターテーブル " _

                          & "WHERE メニューID=" & daoRS_EDI!メニューID　’CSV取込み時に照合したマスターIDも事前に格納

        DoCmd.SetWarnings False

        DoCmd.RunSQL strSQL_MK

        DoCmd.SetWarnings True

        daoRS_EDI.MoveNext

    Loop

追加の質問で申し訳ありませんが、ご教授の程宜しくお願いいたします。

Answer 5

sk.exe様

いつもありがとうございます。

教えていただいたクエリの場合は、工程テーブルにない受注IDを受注テーブルから抽出し、

そのIDを使って工程テーブルにInsertするという考え方で合っていますでしょうか？

もしそうであれば、実は工程テーブルにデータが存在しない受注データがあるため

このクエリでは難しそうです。

(実際には送料など受注データのみにデータが存在するメニューがあります。）

前提条件が足りず申し訳ありません。

かえって話が複雑になってしまうかもしれませんが、実際には

①CSVで送られてきた注文一覧(複数データ)を受注テーブルに取り込む

②取り込む際にマスターテーブルとメニューIDの照合を行い、メニューIDが一致するものは

　メニューマスターテーブルから単価をコピーしてくる

( ↑ ここまではプログラムが完成できています。)

③さらに工程サブマスターテーブルが登録されている場合は、工程テーブルにデータを

　Insertする。

　③実行時に、新しくInsertされた②の受注IDが分からないため工程テーブルにデータを

　追加できない状態です。

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