VBA比較処理の仕方

質問

今アクセスで、比較処理を行いたいとおもうのですが、
いまいちソースを書いたことがなくわからないので、教えてください。
(内容)
表１．２に共通するのは、 商品noと金額が一覧であります。しかし、表１にnoがなく表2にはあるものや、その逆もあります。それだけではなく、表１．２に同じno があるが表2では金額があがってるものもあり、
自動で金額を更新し、表２にあって表1にない商品や表１にあって表２にあるものをすべて一覧でだしたいとおもっています。なにかいい方法はないでしょうか？？

回答No.6

x0000x

#2です。

>どこが更新されたか一覧にだして、色をつけたいのですが、いい方法はないでしょうか？

以下のサイトが参考になると思います。
データシートで条件に当てはまるフィールドの色を変更しています。

http://www.moug.net/tech/acvba/0100030.htm

更新を識別する必要があるので、
(1)更新識別フィールドの追加。
更新フラグフィールドを追加する必要があります。
（更新日時フィールドでも可。以下では更新フラグフィールドで説明）

(2)更新前に更新フラグフィールドを初期化(全件)
update 表1 set
更新フラグ = 0

(3)更新処理SQLで更新を識別できるようにフラグを設定。
■一致するもの（表1.金額 < 表2.金額 で 表1.金額を表2.金額で更新）
update 表1 INNER JOIN 表2 on 表1.商品no = 表2.商品no
set 表1.金額 = 表2.金額
,更新フラグ = 1
where 表1.金額 < 表2.金額

(4)条件に当てはまるフィールドの色を変更

で如何ですか。

回答No.5

kurodai2

フォームや、レポートで色を設定するのであれば
作成した表3に、その識別のための　更新FLGを持たせました。
更新FLGが立っていれば、色を変えるようにして下さい。

表3の作成

表1
商品ID 金額
1 \500
3 \300
4 \200
6 \100

表2
商品ID 金額
1 \450
2 \200
4 \300
5 \500

結果
表3
商品ID 金額 更新FLG
1 \450 1
2 \200 0
3 \300 0
4 \300 1
5 \500 0
6 \100 0

フォームのボタンをクリックしたときのイベントです。
Private Sub コマンド0_Click()
Dim Db As DAO.Database
Dim strSQL As String

Set Db = CurrentDb

'データを書き出す　表3テーブルをクリア
strSQL = "delete * from 表3"
Db.Execute strSQL, dbFailOnError

'表３テーブルにデータを書き出し
strSQL = "INSERT INTO 表3 ( 商品ID, 金額, 更新FLG ) "
strSQL = strSQL & "SELECT 表12結合.商品ID, "
strSQL = strSQL & "IIf(IsNull(表12結合.表2金額),表12結合.表1金額,表12結合.表2金額) AS 金額, "
strSQL = strSQL & "IIf(Not IsNull(表12結合.表1金額) And Not IsNull(表12結合.表2金額),1,0) AS 式1 "
strSQL = strSQL & "FROM ("
strSQL = strSQL & "SELECT d.商品ID, 0 AS 金額, Sum(d.金額1) AS 表1金額, Sum(d.金額2) AS 表2金額 "
strSQL = strSQL & "FROM (SELECT 商品ID,金額 as 金額1,NULL as 金額2 FROM 表1 union all SELECT 商品ID,NULL ,金額 FROM 表2 ) AS d "
strSQL = strSQL & "GROUP BY d.商品ID) AS 表12結合"
Db.Execute strSQL, dbFailOnError
Db.Close

MsgBox ("表3作成されました。")
End Sub

VBAでと言うことでしたので、SQL自体まとめました。
個々のクエリーを作ってよいのであれば、単純なSQLを数珠つなぎで処理できますが・・

表3をデータソースに　レポートを作られるのであれば　更新FLGが1の時
色を変えるようにして下さい。

PS.　ACCESS2007で確認しています

回答No.4

kurodai2

もう少し、補足してください。

根本的な問題になりますが・・
質問では、アクセスでとありますが 途中の表現で
シートとあります。
本当にACCESSについてでしょうか？
色を付けるとか・・・EXCELでイメージされていませんか？
ACESSで色を付けるとすると
・フォーム上
・レポート上
になりますが、何れを指していますか？

更新（更新とは言えませんが）したのが、解るようにとあるのは
どの場合を指しますか

1、表1にのみに金額があった場合 →　金額は表1の金額
2、表2のみに金額があった場合 →　金額は表2の金額
3、表1、表2ともに金額があった →　金額は無条件に表2の金額

sos0619さんが色を付けると考えておられるのは、
1,2,3どの場合か、列挙してください。

補足

補足をさせて頂きます。

すべてアクセス（VBA）で行いたいと思っています。
色をつけたいのは、3の場合になります。
ACESSで色をつける場合・フォーム上・レポート上どちらでもかまいません。
よろしくお願い致します・

回答No.3

kurodai2

どの様な結果を、目的にされているのか　補足して頂けますか？
最終的な形が、見えません。

>自動で金額を更新し、表２にあって表1にない商品や表１にあって表２にあるものをすべて一覧でだしたいとおもっています。なにかいい方法はないでしょうか？？

自動で金額を更新とは、
表1と表2で、明確に書くと　表1の金額を表2の金額で更新ですか？
更新自体の説明がないので・・何を・何で更新？

>結果には、商品NO金額の最大値しかのらないとおもうのですが、
>表２のデータが表１のデータより金額が安い場合もあるのですが、
>その際はどのように実施しればいいでしょうか？

その場合（両方に金額がある場合）、どうしたいのでしょうか？
表1・表2にお互い存在したり、片側だけ存在しない状態から
全ての商品を取り出し、更新とあったので大きい金額を取り出し並べたのが、先の回答でした。
ほしい結果を、具体的かいて頂けますか？

表1
商品NO 金額
1 500
3 300
4 200
5　金額なし
6 100
7　金額なし
＊商品No　2は存在しない

表2
商品NO 金額
1 450
2 200
4 300
5 500
6　金額なし
7　金額なし
＊商品No　3は存在しない

このデータを、使った場合の、目的の形を書いてもらった方が
分かり安いと思います。

補足

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

下記にて、最終的な形について記載させて頂きます。
最終的には、
1.同じNOが表１表２に両方にある場合、表2の金額を別のシート表３に出力
2.表1のみにNOがある場合も別シート表３に出力
3.表3のどこが更新したのか分かるように、変更された所に色をつけたいと思っています。
※表3ですが、1.2の結果を（NOと金額）を記載されるようにしたいです。

（質問について）
自動で金額を更新とは、
表1と表2で、明確に書くと　表1の金額を表2の金額で更新ですか？
更新自体の説明がないので・・何を・何で更新？
●上記についてですが、表1の金額を表2の金額で更新したいと思っています。

（両方に金額がある場合）、どうしたいのでしょうか？
●上記についてですが、両方に金額がある場合、表2の金額をだしたいです。

以上になります。
どうぞよろしくお願い致します

回答No.2

x0000x

>表１．２に共通するのは…
表＝テーブルであれば、以下のSQLをクエリとして実行すればOKです。

クエリで「新規作成」新しいクエリダイアログを表示。
「デザインビュー」選択。
「閉じるボタン」選択。
テーブル表示ペインでマウス右ボタンクリックし、メニューから「SQLビュー」を選択。

以下のそれぞれを貼り付けます。
貼り付けた後、名前を付けて保存し、クエリを実行します。
更新クエリでは、更新前の確認メッセージも表されます。

■一致するもの
select 表1.商品no,表1.金額,表2.金額
from 表1 INNER JOIN 表2 on 表1.商品no = 表2.商品no

■一致するもの（表1.金額 < 表2.金額）
select 表1.商品no,表1.金額,表2.金額
from 表1 INNER JOIN 表2 on 表1.商品no = 表2.商品no
where 表1.金額 < 表2.金額

■一致するもの（表1.金額 < 表2.金額 で 表1.金額を表2.金額で更新）
update 表1 INNER JOIN 表2 on 表1.商品no = 表2.商品no
set 表1.金額 = 表2.金額
where 表1.金額 < 表2.金額

■不一致のもの(表１あり、表２なし）
select 商品no,金額 from 表1
where not exists (select * from 表2 Where 表2.商品no = 表1.商品no)

■不一致のもの(表2あり、表1なし）
select 商品no,金額 from 表2
where not exists (select * from 表1 Where 表2.商品no = 表1.商品no)

VBAで処理する必要があるなら、
以下のクエリで抽出した結果をlistboxなどのデータソースとしてプロパティ設定することで表示できます。

また、更新処理は以下の様に「更新ボタン」押下後の処理として記述します。
Private Sub btnUpdate_Click()

If MsgBox("更新しますか？", vbYesNo) <> vbYes Then Exit Sub

Dim qdf As QueryDef
Dim cnt As Long
Dim strSql As String
strSql = ""
strSql = strSql & "update 表1 INNER JOIN 表2 on 表1.商品no = 表2.商品no "
strSql = strSql & "Set 表1.金額 = 表2.金額"
strSql = strSql & "where 表1.金額 < 表2.金額"

Set qdf = CurrentDb.CreateQueryDef("", strSql)
qdf.Execute dbFailOnError
cnt = qdf.RecordsAffected
qdf.Close
Set qdf = Nothing

MsgBox cnt & "件更新しました。"
End Sub

お礼

とてもわかりやすく教えて頂きありがとう御座います。実施してみたいと思います。
又、もう一点お伺いしたいのですが、どこが更新されたか一覧にだして、色をつけたいのですが、いい方法はないでしょうか？
色々とご質問してしまい大変申し訳御座いません。

回答No.1

kurodai2

比較処理と言うのがよくわかりませんが、一覧を取得するのであれば
VBAと言うよりも、単にクエリを作成するだけではないでしょうか？

表A
商品NO 金額
1 500
2 0
3 300

表B
商品NO 金額
1 0
2 200
4 0
5 500

クエリ
SELECT 商品NO, Max(金額) AS 金額の最大
FROM
(SELECT 商品NO,金額 FROM 表A
union all
SELECT 商品NO,金額 FROM 表B)
GROUP BY 商品NO;

結果
商品NO 金額の最大
1 500
2 200
3 300
4 0
5 500

意味を取り違えていたら、ごめんなさい。

お礼

こちらこそわかりずらい説明になってしまい申し訳御座いません。
上記で教えて頂いた事を実行した場合、
結果には、商品NO金額の最大値しかのらないとおもうのですが、
表２のデータが表１のデータより金額が安い場合もあるのですが、
その際はどのように実施しればいいでしょうか？
教えて頂ければ、幸いです。