[三流君] −−＞ [ＶＢＡで楽しく] −−＞ [バックナンバー一覧]

三流君VBA:Access お互いのテーブルの差を求める DAO使用

久しぶりにAccessのDAOを使用して、２つのテーブルをいじってみた、そんなお話です。
無駄な試行錯誤が多いなぁ、、、質問はもう少しサクっとしとめたいですね・・・

２つテーブルの差？的な質問を読者様よりいただきました

下記の質問を読者様よりいただきました。

------- ここから質問内容 ------
在庫管理をすることになり、手こずっています。例えば・・・

１つのテーブル(AAAテーブル)に
品番	ロット	数量
111	AAAA	1
111	ABAA	1
111	ABAA	1
111	AABA	1
111	AABA	1
222	CCCC	1
222	CCCC	1
111	ABAA	1

とし、もう一方のテーブル(bbbテーブル)に、

品番	ロット	数量
111	AAAA	1
111	AAAA	1
111	ABAA	1
111	AABA	1
222	CCCC	1
222	CCCD	1
333	DDDD	1
444	AEAA	1

が　あった場合、結果として

品番	ロット	数量	
111	ABAA	1	(aaa)
111	ABAA	1	(aaa)
111	AAAA	1	(bbb)
111	AABA	1	(aaa)
222	CCCD	1	(bbb)
333	DDDD	1	(bbb)
444	AEAA	1	(bbb)

という　結果を得たいのですが。。。
互いのテーブルにないものを　抽出したいので　どちらのテーブルの品番　ロット　数量のものがないのか
分かるようにするには、どのようにすればよろしいでしょうか。

------ ここまで、質問内容 ------

お互いのテーブルの差を求めるみたいですね。
よくある在庫と注文の引き落とし処理と似ているが、
お互いの差ってのがくせ者ですね。
※同じ品番・ロットが無ければ、不一致クエリー2回とも思ったけど、、、

まぁ、カッコイイ方法ありそうだけど、原始的な方法でやってみます。
仕様を受け取ったら、実際に人間が手でやれるか？考えてみます。
※人がやる方法をコンピューターにやらせるよりも、
　コンピューター的な方法が良い場合もあるけど、
　今回は、人の(手作業)をプログラムに落とす、そんな原始的な方法でやってみます。


マッチング処理を手作業で試行錯誤 落書き


初めに、結果を保存するテーブル CCCを1つ作ります。

CCCテーブル  
品番	ロット	数量	結果  ← 結果を入れるフィールドを1つ作ります。

次は、マッチング処理を手作業で試行錯誤してみます。
私がよくやるのは、紙の上に落書きを書いたり、
Excelのシート上で、コピーしたり、貼り付け、消したりと動作のイメージを膨らませます。

↓実際の試行錯誤の様子
※動画のリンクを入れる。。。。001http://www.youtube.com/watch?v=yUbXv0lI4cg



Access DAO DB.Execute SQL で SQL文の実行


下記のような方針、作業手順でVBAのプログラムを作成したいと思います。

'1.結果のテーブルCCCを全て削除
'2.AAAテーブルの内容をCCCへ全て追加 結果にaaaを固定でセットする

'3.BBBテーブルの頭から 1レコード単位で下記の処理を行う※EOFまでループ
	'3.1 BBBテーブルから品番 ロット 数量を取り出す
	'3.2 結果のCCCテーブルを3.1で取り出した品番、ロット、数量を条件に検索する
	'3.3 検索結果の判断
		'3.3.1 検索できた、データが存在したら、a=bの一致なのでCCCテーブルから見つかった1件削除
		'3.3.2 検索不可、データが存在しない場合、bのデータがaに無いので、CCCに1件追加する 

↑日本語で書くと、こんな感じかなぁ。
　いっけん、aaaを処理していない感じがするけど、
  cccへ全て追加後、bbbを使った削除と追加処理で、
  削除されず、残ったデータがaaaにありbbbに無い（不一致）のデータです。
　まぁ、逆の追加したデータが、bbbにありaaaに無い（不一致）のデータって感じです。

次に、チョット力業ですが、この流れでVBAを作成してみます。

DAOで作りたいので、参照設定確認後、
Dim DB As DAO.Database と 変数を切ってから、
Set DB = CurrentDb()  '自分自身で、データベースを指定して、
やりたいことが、
>1.結果のテーブルCCCを全て削除
なので、
    '無条件で（全て）テーブル内を削除
    DB.Execute "DELETE * FROM CCC;"   'SQLクエリーの発行
こんな感じて削除のSQLを走らせました。

次は、
>'2.AAAテーブルの内容をCCCへ全て追加 結果にaaaを固定でセットする
なので、追加のSQLをまたまた発行しました。

    Dim strSQL As String
    
    strSQL = "INSERT INTO CCC ( 品番, ロット, 数量, 結果 ) "
    strSQL = strSQL & " SELECT AAA.品番, AAA.ロット, AAA.数量, ""aaa"" as flg FROM AAA;"
    
    DB.Execute strSQL   'SQLクエリーの発行 AAAからCCCへデータを追加

まぁ、2つSQLを.Execute で発行しただけです。。。

↓実際の試行錯誤の様子
※動画のリンクを入れる。。。。002 http://www.youtube.com/watch?v=AjdbPIQJcmo




Access DAO Recordsetでデータを1つ1つEOFまで取り出す


CCCの結果テーブルが用意できたので、
次は、BBBテーブルのレコードを1つ1つ取り出し、
CCCテーブルとぶつけてみます。

まずは、BBBのテーブルでループさせたいので、
Dim TB_BBB As DAO.Recordset  'レコードセットの変数
を作り、
Set TB_BBB = DB.OpenRecordset("Select * from BBB;") 'テーブルBBB全て
で、テーブル全てを引っぱります。
あとは、いつもの？.EOFと.MoveNextを使い、頭からループさせます。

    While TB_BBB.EOF = False '※EOFまでループ
        '3.1 BBBテーブルから品番 ロット 数量を取り出す
        Debug.Print TB_BBB![品番]
        Debug.Print TB_BBB![ロット]
        Debug.Print TB_BBB![数量]
    
        TB_BBB.MoveNext  '次のレコードへ移動
    Wend

↓実際の試行錯誤の様子
※動画のリンクを入れる。。。。003 http://www.youtube.com/watch?v=5Vjql2dgrwg




Access DAO テーブルを条件付きで検索し削除と追加を行う


次は、ハマりそうな感じの
        '3.2 結果のCCCテーブルを3.1で取り出した品番、ロット、数量を条件に検索する
        '3.3 検索結果の判断
            '3.3.1 検索できた、データが存在したら、a=bの一致なのでCCCテーブルから見つかった1件削除
            '3.3.2 検索不可、データが存在しない場合、bのデータがaに無いので、CCCに1件追加する
です。

'3.2 結果のCCCテーブルを3.1で取り出した品番、ロット、数量を条件に検索する
いつものように、SQLを作ります。ポイントは、TB_BBB![フィールド名]で、値を条件にすることです。
strSQL = "SELECT CCC.* FROM CCC "
strSQL = strSQL & "WHERE (((CCC.品番)='" & TB_BBB![品番] & "') "
strSQL = strSQL & " AND ((CCC.ロット)='" & TB_BBB![ロット] & "') "
strSQL = strSQL & " AND ((CCC.数量)=" & TB_BBB![数量] & "));"

'CCCのレコードセットを作成します
Set TB_CCC = DB.OpenRecordset(strSQL) 'BBBを条件とした

Debug.Print "COUNT=" & TB_CCC.RecordCount

↑あとは、.OpenRecordset後の.RecordCountで単純に0以上か、チェックしました。↓

'3.3 検索結果の判断
If TB_CCC.RecordCount > 0 Then
    '3.3.1 検索できた、データが存在したら、a=bの一致なのでCCCテーブルから見つかった1件削除
    Debug.Print "見つかったので削除"
    TB_CCC.Delete   'レコードを削除
Else
    '3.3.2 検索不可、データが存在しない場合、bのデータがaに無いので、CCCに1件追加する
    Debug.Print "BBBをCCCへ追加"
    TB_CCC.AddNew  '新しいレコード追加
    TB_CCC![品番] = TB_BBB![品番]     'データをセット
    TB_CCC![ロット] = TB_BBB![ロット]
    TB_CCC![数量] = TB_BBB![数量]
    TB_CCC![結果] = "bbb"
    TB_CCC.Update  'レコード確定
End If

TB_CCC.Close

↓実際の試行錯誤の様子
※動画のリンクを入れる。。。。004 http://www.youtube.com/watch?v=DatUH2smAGk



完成した VBA マクロ


全部足すと、無駄に長いけど、こんな感じかなぁ？


Sub aaa()

    Dim DB As DAO.Database

'1.結果のテーブルCCCを全て削除
    Set DB = CurrentDb()  '自分自身
    
    '無条件で（全て）テーブル内を削除
    DB.Execute "DELETE * FROM CCC;"   'SQLクエリーの発行

'2.AAAテーブルの内容をCCCへ全て追加 結果にaaaを固定でセットする

    Dim strSQL As String
    
    strSQL = "INSERT INTO CCC ( 品番, ロット, 数量, 結果 ) "
    strSQL = strSQL & " SELECT AAA.品番, AAA.ロット, AAA.数量, ""aaa"" as flg FROM AAA;"
    
    DB.Execute strSQL   'SQLクエリーの発行 AAAからCCCへデータを追加

'3.BBBテーブルの頭から 1レコード単位で下記の処理を行う※EOFまでループ
    Dim TB_BBB As DAO.Recordset  'レコードセットの変数
    Dim TB_CCC As DAO.Recordset
    
    Set TB_BBB = DB.OpenRecordset("Select * from BBB;") 'テーブルBBB全て
    
    While TB_BBB.EOF = False '※EOFまでループ
        '3.1 BBBテーブルから品番 ロット 数量を取り出す
        Debug.Print TB_BBB![品番]
        Debug.Print TB_BBB![ロット]
        Debug.Print TB_BBB![数量]
                
        '3.2 結果のCCCテーブルを3.1で取り出した品番、ロット、数量を条件に検索する
        strSQL = "SELECT CCC.* FROM CCC "
        strSQL = strSQL & "WHERE (((CCC.品番)='" & TB_BBB![品番] & "') "
        strSQL = strSQL & " AND ((CCC.ロット)='" & TB_BBB![ロット] & "') "
        strSQL = strSQL & " AND ((CCC.数量)=" & TB_BBB![数量] & "));"
        
        'CCCのレコードセットを作成します
        Set TB_CCC = DB.OpenRecordset(strSQL) 'BBBを条件とした
        
        Debug.Print "COUNT=" & TB_CCC.RecordCount
        
        '3.3 検索結果の判断
        If TB_CCC.RecordCount > 0 Then
            '3.3.1 検索できた、データが存在したら、a=bの一致なのでCCCテーブルから見つかった1件削除
            Debug.Print "見つかったので削除"
            TB_CCC.Delete   'レコードを削除
        Else
            '3.3.2 検索不可、データが存在しない場合、bのデータがaに無いので、CCCに1件追加する
            Debug.Print "BBBをCCCへ追加"
            TB_CCC.AddNew  '新しいレコード追加
            TB_CCC![品番] = TB_BBB![品番]     'データをセット
            TB_CCC![ロット] = TB_BBB![ロット]
            TB_CCC![数量] = TB_BBB![数量]
            TB_CCC![結果] = "bbb"
            TB_CCC.Update  'レコード確定
        End If

        TB_CCC.Close '使ったレコードセットを忘れずに閉じる
        
        TB_BBB.MoveNext  '次のレコードへ移動
    Wend
    
    '後始末 クローズ処理
    TB_BBB.Close
    DB.Close
    
End Sub

終わりの挨拶

こんな感じで、試行錯誤しながら作ってみました。
流れ的には、仕様の理解〜コーディングまでやりました。
なにか抜けてない？
あっ、テストしてないや・・・完成したと思って、安心してちゃイケナイよね、キット。

読者から追加の質問、バグの報告が来る予感がしつつ、失礼します。三流プログラマー Ken3

ページフッター

ここまで、読んでいただきどうもです。ここから下は、三流君宛のメッセージ送信や 三流君のホームページの紹介・案内です
目的の情報が見つかったか？少々心配しつつ、、、※質問や感想は、気軽に送ってくださいね。

質問や要望など メッセージを送る(三流君に連絡する)

質問や要望など連絡方法でお互い確認が取りやすく、便利なのが掲示板なのですが、私の対応のまずさから不定期で荒れてしまい、掲示板は現在封鎖中です。(反省しなきゃ)
感想や質問・要望・苦情など 三流君へメッセージを送る。
時間的余裕のある要望・質問・苦情の場合は、下記のフォームからメッセージを送ることができます。
あなたのお名前（ニックネーム）:さん
返信は？: 不用(HP更新を待つ) , E-mail→ アドレス:に返事をもらいたい



(感想や質問・要望 メッセージはHPで記事に載せることがあります。)

急ぎで連絡がほしい、そんな時は:[三流君連絡先]に連絡してください。

リンクや広告など

項目別に↓に人気の記事をまとめてみました。お探しのジャンルを選択してください。
人気記事(来場者が多いTOP3):
[VBAでIE,WebBrowserを操作]・・・VBAでIE,WebBrowserを操作する サンプルです
[Access から Excel 連携 CreateObject("Excel.Application")]・・・AccessからExcelを操作したりデータの書き出しなどです
[VBAでOutlookの操作 CreateObject("Outlook.Application" )]・・・VBAからOutlookを使い、メール関係を処理するサンプルです
↑上記３つみたいなCreateObjectで他のアプリケーションを操作するサンプルが人気です。

開発時の操作: [F1を押してHELPを見る]/ [Debug.Print と イミディエイトウインドウ]/ [実行時エラーでデバッグ]/ [ウォッチ式とSTOP]/ [参照設定を行う]

仕様書(設計書？) XXXX書類: [基本設計書や要求仕様書]/ [テスト仕様書 テストデータ]/ [バグ票]/ [関数仕様書]/ [流れは 入力・処理・出力]

Excel関係:
[Excel UserFormを操作する]・・・エクセルでユーザーフォームを作成して入力などを行ってます
[ExcelからAccessを操作する]・・・ExcelからAccessのマクロを起動してみました、
[Excel関係 関数、その他]・・・その他Excel関係です

Access関係:
[Access UserForm/サブフォーム 操作]・・・アクセスでフォームを使ったサンプルです
[Access レポート操作]・・・レポートを操作してみました
[Access クエリーやその他関数]・・・あまりまとまってませんが、スポット的な単体関数の解説です

その他：VBAの共通関数やテキストファイルの操作など
[VBAでテキストファイル(TextFile)の操作]・・・普通のテキストファイルを使ったサンプルです
[VBA 標準関数関係とその他解説]・・・その他、グダグタ解説してます

Blog:[三流君の作業日記]/ [サンプルコードのゴミ箱]/ 広告-[通販人気商品の足跡]


[三流君(TOP ken3.org へ戻る)] / [VBA系TOPへ] / [VBA系バックナンバー目次へ移動]