Access VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
847 / 2272 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【9809】2テーブル共通のレコード検索について ゆっぴぃ 07/9/7(金) 13:20 質問[未読]
【9810】Re:2テーブル共通のレコード検索について 小僧 07/9/7(金) 14:27 回答[未読]
【9817】Re:2テーブル共通のレコード検索について ゆっぴぃ 07/9/7(金) 22:44 質問[未読]
【9818】Re:2テーブル共通のレコード検索について ゆっぴぃ 07/9/8(土) 11:22 質問[未読]
【9819】Re:2テーブル共通のレコード検索について 小僧 07/9/8(土) 11:52 回答[未読]
【9820】Re:2テーブル共通のレコード検索について ゆっぴぃ 07/9/8(土) 15:20 質問[未読]
【9821】Re:2テーブル共通のレコード検索について 小僧 07/9/8(土) 17:50 回答[未読]
【9822】Re:2テーブル共通のレコード検索について ゆっぴぃ 07/9/8(土) 22:56 質問[未読]
【9823】Re:2テーブル共通のレコード検索について 小僧 07/9/9(日) 7:02 回答[未読]
【9824】Re:2テーブル共通のレコード検索について ゆっぴぃ 07/9/9(日) 14:16 お礼[未読]
【9826】Re:2テーブル共通のレコード検索について hatena 07/9/10(月) 10:23 発言[未読]
【9809】2テーブル共通のレコード検索について
質問  ゆっぴぃ  - 07/9/7(金) 13:20 -
引用なし
パスワード
   すみませんがどなたか教えてください。


テーブルＡ・Ｂがあってそれぞれに共通のフィールドがいくつかあります。
この共通フィールドにまったく同じデータが入っているレコードだけを探し、
該当するレコードがあればそのレコードにあるデータを追記したいのです。

単純にまったく同じデータをもつレコードを探すのですが、自分が思い
ついたのはＶＢＡを使って、フィールドの値が一致するかをＩＦで
判定してそれを条件のフィールドについてすべて行い、すべての条件を満たした
場合のみレコードを追記する・・・・

というものでした。


きっとこれならできないことはないのかもと思うのですが、もっと簡単な
方法というのはあるのでしょうか？クエリにはそれらしいのはなかった
ような気がするのですが。

どなたかご存知でしたら教えてください。お願いします。
【9810】Re:2テーブル共通のレコード検索について
回答  小僧  - 07/9/7(金) 14:27 -
引用なし
パスワード
   ▼ゆっぴぃ さん：
こんにちは。

具体的な例示がないため、当方にはちょっと解りづらいです。
見当違いの回答になっていたら申し訳ありません。


テーブルA
ID 品名　　単価
1　りんご　 100
2　みかん　　50
3　なし　　 150


テーブルB
ID 品名　　単価
1　りんご　 Null
3　ぶどう　 Null


テーブルAのID、品名とテーブルBのID、品名が一致した場合のみ
テーブルBの単価を更新をしたい、という様な事でしたら、こんな感じです。

まずはID、品名が一致しているレコードの検索です。

SELECT テーブルA.ID, テーブルA.品名
FROM テーブルA
LEFT JOIN テーブルB
ON (テーブルA.品名 = テーブルB.品名) AND (テーブルA.ID = テーブルB.ID)
WHERE テーブルB.ID Is Not Null;


また一発で更新を行いたい場合は

UPDATE テーブルA
LEFT JOIN テーブルB
ON (テーブルA.品名 = テーブルB.品名) AND (テーブルA.ID = テーブルB.ID)
SET テーブルB.単価 = テーブルA!単価
WHERE テーブルB.ID Is Not Null;


こんな感じでしょうか。

テーブルA、テーブルBを作った後、
クエリのSQLビューに上記SQLを記載し
デザインビューに戻してみると
やっている事が解りやすくなるかもしれません。
【9817】Re:2テーブル共通のレコード検索について
質問  ゆっぴぃ  - 07/9/7(金) 22:44 -
引用なし
パスワード
   小僧 さんへ

お返事ありがとうございます。
教えていただいた、まさにそれがやりたいことです♪
とはいえまだできたわけではないのでまた質問をさせていただくかもです。
そのときはまたお願いします。

ありがとうございました！！
【9818】Re:2テーブル共通のレコード検索について
質問  ゆっぴぃ  - 07/9/8(土) 11:22 -
引用なし
パスワード
   小僧 さん・・・
やっぱりできませんでした。


SELECT テーブルA.ID, テーブルA.品名
FROM テーブルA
LEFT JOIN テーブルB
ON (テーブルA.品名 = テーブルB.品名) AND (テーブルA.ID = テーブルB.ID)
WHERE テーブルB.ID Is Not Null;

教えていただいたSQLはこれです。
実際にはテーブルBの価格を更新したいので

SELECT テーブルA.ID, テーブルA.品名, テーブルB.価格
FROM テーブルA
LEFT JOIN テーブルB
ON (テーブルA.品名 = テーブルB.品名) AND (テーブルA.ID = テーブルB.ID)
WHERE テーブルB.ID Is Not Null;

のようにSELECTの一番後ろに更新したいフィールドを追加しました。


そしてそのフィールドに【100】という数字を入れてみようと思ったので

Dim stSQL as string
Set rs = db.OpenRecordset(stSQL, dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF
　　rs.Edit
　　rs.価格 = 100
　　rs.Update
Loop

と記述してみたのですが・・・
rs.Edit　のところで
《実行時エラー3027
データベースまたはオブジェクトは読み取り専用なので、更新できません。》
と表示されます。

そういえば、選択クエリは２つ以上のテーブルからフィールドを選択した場合
更新とかはできなかったような気がします。


教えていただいたコードの後はどのように書けばよかったのでしょうか？
それともSQLの追加の方法が間違っていたのでしょうか。

質問ばかりで申し訳ないのですがぜひとも教えてください！
【9819】Re:2テーブル共通のレコード検索について
回答  小僧  - 07/9/8(土) 11:52 -
引用なし
パスワード
   ▼ゆっぴぃ さん：
こんにちは。

> そういえば、選択クエリは２つ以上のテーブルからフィールドを選択した場合
> 更新とかはできなかったような気がします。

そんな事はありませんよ＾＾

そのクエリで更新するとデータベースにとって不都合が
出てしまう可能性がある、という事で拒否されていますね。

今回の例では
ID と 品名 の2フィールドで Uniqueである(他のレコードと絶対に被らない)事を
テーブルに明示的に書いてあげないと更新ができません。

CREATE UNIQUE INDEX index1 ON テーブルA (ID, 品名);
CREATE UNIQUE INDEX index1 ON テーブルB (ID, 品名);

上記2つのSQLを実行してあげると、
ゆっぴぃさんのプログラムでも更新が可能になるかと思われます。


また、上記SQLの操作は

テーブルをデザインビューで開く ⇒ ツールバーの [表示] から
[インデックス] を開く事で手動で設定する事も可能ですね。
【9820】Re:2テーブル共通のレコード検索について
質問  ゆっぴぃ  - 07/9/8(土) 15:20 -
引用なし
パスワード
   またまた丁寧な回答ありがとうございます。

えっと・・・


>ID と 品名 の2フィールドで Uniqueである(他のレコードと絶対に被らない)事を
>テーブルに明示的に書いてあげないと更新ができません。

とのことですが、これはフィールドの中に同じ値があってはいけないということ
なのでしょうか？

実は教えていただいたものを基に作ったSQLは、実際にはフィールドが４つあって
個々のフィールド内で同じ値を持つものはたくさんあります。
けれど４つのフィールドすべてが同じというレコードはないのです。
重なるデータがある場合にはインデックスは使えない・・・のかな？？

それにしても、SQLでインデックスって設定できるんですね。
テーブルでやる方法しか知りませんでした。
Accessは奥が深いなぁ〜。私もまだまだです＾＾
【9821】Re:2テーブル共通のレコード検索について
回答  小僧  - 07/9/8(土) 17:50 -
引用なし
パスワード
   ▼ゆっぴぃ さん：
こんにちは。

>>ID と 品名 の2フィールドで Uniqueである(他のレコードと絶対に被らない)事を
>>テーブルに明示的に書いてあげないと更新ができません。

>とのことですが、これはフィールドの中に同じ値があってはいけないということ
>なのでしょうか？

ID の中で重複、品名の中で重複は許されますが、
IDと品名を合わせると重複は許されない、という事ですね。

ID 品名
1　りんご
2　りんご
2　なし
2　なし　　　　←　これはダメ


例として、

テーブルA
ID　 品名　 価格
1　りんご　　100
2　みかん　　 50
3　なし　　　150


テーブルB
ID　 品名　 価格
1　 りんご　null
3　 ぶどう　null
1　 りんご　null


SELECT テーブルB.ID, テーブルB.価格
FROM テーブルA
LEFT JOIN テーブルB
ON (テーブルA.ID = テーブルB.ID) AND (テーブルA.品名 = テーブルB.品名)
WHERE テーブルB.ID Is Not Null;

ID　価格
1　 null
1　 null


このような状態になると、価格フィールドを更新したくても
テーブルBのどちらのレコードなのかという区別がつかないため、
一意制約が必要となります。
(前回の回答ではテーブルAにもIndexをつけましたが、
正確にはテーブルBのみIndexをつければ更新は可能です。)


>重なるデータがある場合にはインデックスは使えない・・・のかな？？

結合しているフィールド(上記例の場合はID、品名)に
重複がある場合は Index を使う方法はできませんね。


データの中身は適当で良いので
ゆっぴぃさんの使っているテーブルの情報をご提示して頂くと
もうちょっとピンポイントな回答ができるかもしれません。
【9822】Re:2テーブル共通のレコード検索について
質問  ゆっぴぃ  - 07/9/8(土) 22:56 -
引用なし
パスワード
   小僧 さん、ご丁寧にありがとうございます。
やっぱりインデックスはつけれないということなのかな。。。

それでは申し訳ないのですがお言葉に甘えさせていただきます。


テーブルA

フィールド名　　データ型　　　　　インデックス
1.ID　　　　　　オートナンバー　　はい（重複なし）
2.事業者NO　　　数値型　　　　　　はい（重複あり）
3.営業所NO　　　数値型　　　　　　はい（重複あり）
4.顧客NO　　　　数値型　　　　　　はい（重複あり）
5.入場証NO　　　数値型　　　　　　はい（重複あり）


テーブルB

フィールド名　　データ型　　　　　インデックス
1.ID　　　　　　オートナンバー　　はい（重複なし）
2.事業者NO　　　数値型　　　　　　はい（重複あり）
3.営業所NO　　　数値型　　　　　　はい（重複あり）
4.顧客NO　　　　数値型　　　　　　はい（重複あり）
5.入場証NO　　　数値型　　　　　　はい（重複あり）
6.来場確認　　　テキスト型


テーブルAおよびテーブルBの2.〜5.を比較し、すべての
データが一致した場合のみテーブルBの6.に『来場しました』の文字列を
入れたいと思います。
それぞれのテーブルの各フィールドには同じ数値が入ることもありますが、
2.〜5.すべてが同じということは絶対にありません。


ここに記入したことが小僧さんの言われる《テーブルの情報》になって
いるのかどうかわかりませんが、とりあえず書かせていただきます。
よろしくお願いします。
【9823】Re:2テーブル共通のレコード検索について
回答  小僧  - 07/9/9(日) 7:02 -
引用なし
パスワード
   ▼ゆっぴぃ さん：
こんにちは。

>2.〜5.すべてが同じということは絶対にありません。


という事でしたら

CREATE UNIQUE INDEX index1 ON テーブルB (事業者NO, 営業所NO, 顧客NO, 入場証NO);

で４つのフィールドを合わせて一つの Unique なキーにして、


SELECT テーブルB.ID,
テーブルB.来場確認
FROM テーブルA
INNER JOIN テーブルB
ON (テーブルA.入場証NO = テーブルB.入場証NO) AND
(テーブルA.事業者NO = テーブルB.事業者NO) AND
(テーブルA.営業所NO = テーブルB.営業所NO) AND
(テーブルA.顧客NO = テーブルB.顧客NO);


で更新可能なクエリになりそうです。


また、レコードセットを開かなくても

UPDATE テーブルA
INNER JOIN テーブルB
ON (テーブルA.入場証NO = テーブルB.入場証NO) AND
(テーブルA.顧客NO = テーブルB.顧客NO) AND
(テーブルA.営業所NO = テーブルB.営業所NO) AND
(テーブルA.事業者NO = テーブルB.事業者NO)
SET テーブルB.来場確認 = "来場しました";


のような更新クエリで、１発更新も可能です。
更新クエリを使うのであればIndex を付けなくても良さそうですね。
【9824】Re:2テーブル共通のレコード検索について
お礼  ゆっぴぃ  - 07/9/9(日) 14:16 -
引用なし
パスワード
   小僧 さんへ

できました！！
最終的には更新クエリで処理するようにしたのですが

>CREATE UNIQUE INDEX index1 ON テーブルB (事業者NO, 営業所NO, 顧客NO, 入場証NO);

も、いつでも使えるようにしておいたほうがよさそうですね！！

いろいろ教えていただいてありがとうございました！
【9826】Re:2テーブル共通のレコード検索について
発言  hatena  - 07/9/10(月) 10:23 -
引用なし
パスワード
   補足させてください。

>>CREATE UNIQUE INDEX index1 ON テーブルB (事業者NO, 営業所NO, 顧客NO, 入場証NO);
>
>も、いつでも使えるようにしておいたほうがよさそうですね！！

複数フィールドインデックスは、上記の方法でもできますが、
テーブルのデザインビューで、メニュー[表示]-[インデックス]
をクリックして、

インデックス名　フィールド名　並べ替え順序
------------------------------------------
index1　　　　　事業所NO　　　昇順
　　　　　　　　営業所NO　　　昇順
　　　　　　　　顧客NO　　　　昇順
　　　　　　　　入場証NO　　　昇順

インデックスプロパティ
主キー　　いいえ
固有　　　はい
Null無視　いいえ

というように設定しても可能です。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
847 / 2272 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
1078201
(SS)C-BOARD v3.8 is Free