クエリで求めた空き番を登録したい

詰まってしまったので質問させていただきます。

登録番号の空き番号を求めることが出来るクエリを使い空いている番号を埋めたいと思います。

個人Ｔ…テーブル。空き番号をもつ"登録番号"フィールドと空き番号がない綺麗な"連番"フィールドを持つ

ZZZZ空き番号抽出クエリ…"登録番号"の空き番号を昇順で並べた"仮想ID"がある

Private Sub Sample6()

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Variant
Dim J As Variant


Set Db = CurrentDb
Set rs = Db.OpenRecordset("個人Ｔ")

J = Right(DMax("連番", "個人Ｔ"), 4)
i = 0

While i < J

rs.AddNew
i = DMin("仮想ID", "ZZZZ空き番号抽出クエリ")
→→ rs(登録番号) = "ZZZZ" & i
rs.Update
rs.MoveNext

Wend

End Sub

これで実行すると「このコレクションには項目がありません。」
と「rs(登録番号) = "ZZZZ" & i」がエラーになってしまいます。

書き方自体が悪いのはものすごく分かるのですが・・・
どこを直したら正常に空き番号を埋めることができるでしょうか？

No.6 回答者： 30246kiku 回答日時： 2009/04/09 11:39

＃１、＃４です

言われていたことがわかってきました。

テーブル　受注Tと個人Tにリレーションが設定されているのでしょうか。
（確かに、参照整合性が付いているとエラーになりますね：Access単体で）

1度リレーション設定を解除し、各テーブル単位で更新処理後、再度リレーションを設定してみてください。



※ ご質問の趣旨は、

個人Ｔ　　　　　個人Ｔ
登録番号　　　　連番
ZZZZ0001　　　　ZZZZ0001
ZZZZ0003　　　　ZZZZ0002
ZZZZ0006　　　　ZZZZ0003
ZZZZ0007　　　　ZZZZ0004
ZZZZ0009　　　　ZZZZ0005

とあったとき、2, 4, 5, 8 の登録番号を追加して・・・
というものだったのかと思いますが、

その処理をしたとして、注文Tは連番で置き換えができると思います。が、
個人Tを置き換える処理で苦労すると思います。

３ → ２に置き換える時や、６ → ３に置き換える時など
２を追加する時に３の情報で作っておけばよいとは思いますが、
６を３にする時って・・・

この回答への補足

ご回答ありがとうございます。
そうです、リレーションシップでお互いをくっつけていました。

趣旨としましては、30246kikuさんの仰るとおりの作業になると思っていました。最小値の方から順に入れ替えを行えば・・・
と思っていたのですが

>３ → ２に置き換える時や、６ → ３に置き換える時など
>２を追加する時に３の情報で作っておけばよいとは思いますが、
>６を３にする時って・・・

３を切り取って２に上書き、６切り取って３上書きとずっと繰り返しが
出来ないものかと思っていました。
そうとう大変な動作だとは思うのですがこれしかないような気がして・・・

リレーションシップを削除してからデータシート上で１つ変更してみようとしました。
ですが、同じく更新に失敗しました。とでてしまいました。
postgresSQL側でもリンクテーブルのようなものでつながれている？のかもしれません

補足日時：2009/04/09 12:33

この回答へのお礼

お礼からで失礼します。
SQL側でやはりテーブル同士がくっついているようです。
なのでやはり個人Ｔの登録番号の空き番号をうめて注文Ｔの登録番号を移動させ、
個人Ｔの連番を元に個人Ｔの登録番号を変えるしかありませんよね

お礼日時：2009/04/09 14:22

No.5 回答者： Dxak 回答日時： 2009/04/09 11:27

＃３です

> この作成方法はまず
> TbNumテーブルを作成し、クエリ作成でTbNum呼び出し追加クエリを作って
> 追加するテーブルを個人ＴにしてＳＱＬシートにコピーして各箇所を変更
> で大丈夫ですよね？

う～ん、途中から、ぜんぜん違うもののような気がしますが・・・
初歩的な使用方法から、説明すれば・・・

クエリ新規作成を選択、デザインビューでＯＫ、テーブルの表示が出るが、何も選択せず閉じる
左上の「デザインビュー」から「ＳＱＬビュー」に切り替える
で・・・
Selectから;までを、コピーペーストで、ＳＱＬビューに貼り付ける

で、データシートビューにすれば、結果、欠番の連番が得られてます

ここから、「デザインビュー」に切り替え、追加クエリに変更、テーブルを選択し、追加する先のフィールドを選択して、実行すれば追加できます

この回答への補足

ご回答ありがとうございます。

手順まですみませんでした＞＜

>データシートビューにすれば、結果、欠番の連番が得られてます
テーブルや追加クエリとかを選ばずにここまでの作業をすると言う事だったのですね、
申し訳ありませんでした。

早速試させていただきました。
データシートビューをすると起動に時間はかかりましたが
序盤の空き番号は表示されます。
ですが後半になるとずっと検索しているようで終わりそうにありません
マシンスペックの問題かもしれないですね・・・

補足日時：2009/04/09 12:29

No.4 回答者： 30246kiku 回答日時： 2009/04/09 10:48

＃１です

前回の最終形で考えてみます。
（登録番号の数字が飛ぶように変形してます）

注文Ｔ　　　　注文Ｔ　　　　｜　個人Ｔ　　　　　個人Ｔ
登録番号　　　連番　　　　　｜　登録番号　　　　連番
ZZZZ0001　　　ZZZZ0001　　　｜　ZZZZ0001　　　　ZZZZ0001
ZZZZ0003　　　ZZZZ0002　　　｜　ZZZZ0003　　　　ZZZZ0002
ZZZZ0003　　　ZZZZ0002　　　｜　ZZZZ0006　　　　ZZZZ0003
ZZZZ0007　　　ZZZZ0004　　　｜　ZZZZ0007　　　　ZZZZ0004
ZZZZ0009　　　ZZZZ0005　　　｜　ZZZZ0009　　　　ZZZZ0005

> 当初予定していたのは前回までに出来た変更予定の連番を元に旧形体の連番に上書き。

これを、注文T、個人Tに適用すると

注文Ｔ　　　　注文Ｔ　　　　｜　個人Ｔ　　　　　個人Ｔ
登録番号　　　連番　　　　　｜　登録番号　　　　連番
ZZZZ0001　　　ZZZZ0001　　　｜　ZZZZ0001　　　　ZZZZ0001
ZZZZ0002　　　ZZZZ0002　　　｜　ZZZZ0002　　　　ZZZZ0002
ZZZZ0002　　　ZZZZ0002　　　｜　ZZZZ0003　　　　ZZZZ0003
ZZZZ0004　　　ZZZZ0004　　　｜　ZZZZ0004　　　　ZZZZ0004
ZZZZ0005　　　ZZZZ0005　　　｜　ZZZZ0005　　　　ZZZZ0005

> そうなると注文Ｔにある登録番号とつりあわなくなってしまうようです
これは、片側のテーブルだけを行った状態でみているものと思います。
両方のテーブルに適用することで、関連付けが失われることはありません。
（もちろん、個人Tの登録番号は連続した番号になります）


> といった形をとりたかったのですが登録番号が完全に抜けてしまっている場合、データシートビュー上で番号をつめる事もできませんでした。
> なぜなら、登録されていない登録番号に変更することは出来ないからです
？？？


> 電話番号、会社名などの情報をレコード毎に移す
既に個人Tの他フィールドにある情報なら移す必要はないと思います。


他のテーブルにも登録番号で関連付けられたものがあるのであれば、同様な準備をするか、個人Tの登録番号と連番の関係を適用する処理が必要です。
（一番最後に適用するのは、個人Tになると思います）

この回答への補足

ご回答ありがとうございます。
>> そうなると注文Ｔにある登録番号とつりあわなくなってしまうようです
>これは、片側のテーブルだけを行った状態でみているものと思います。
>両方のテーブルに適用することで、関連付けが失われることはありません。
両方のテーブルを同時に更新といった作業をする事ができるのですか！？
テーブル個々でだと思っていました・・・
これが出来れば簡単だろうなぁとは思っていたのですが


>？？？
個人Ｔに存在しない登録番号に注文Ｔの登録番号(この番号を元に他フィールドの情報を引っ張る)を移動できない。

ここが一番難しいので説明が下手なのが悔しいです・・・


>> 電話番号、会社名などの情報をレコード毎に移す
>既に個人Tの他フィールドにある情報なら移す必要はないと思います。
では、登録番号だけ変更するだけでやれそうでしょうか？

>他のテーブルにも登録番号で関連付けられたものがあるのであれば
他のテーブルとは注文Ｔの事でしょうか、テーブルは個人Ｔと注文Ｔだけですので
注文テーブルには注文内容などが書かれているために登録番号で関連付けはされています(なので注文Ｔのデータシート上では個人Ｔの登録番号にはない空き番号にはつめられない？)

どのような対応が必要になりますでしょうか・・・
頼ってしまい申し訳ありません

補足日時：2009/04/09 11:08

No.3 回答者： Dxak 回答日時： 2009/04/08 23:28

ＤＡＯ、ＡＤＯに、こだわってるけど？・・・

まぁ、そこを無視して＾＾；

テーブル名：TbNum
フィールド名：Num（０～９までの数値が入った、単純なテーブル）
Num
0
1
2
3
4
5
6
7
8
9

で、クエリで・・・
クエリ名：QryNum

SELECT "ZZZZ"+Format([No4].[Num]*1000+[No3].[Num]*100+[No2].[Num]*10+[No1].[Num],"0000") AS 登録番号
FROM TbNum AS No1, TbNum AS No2, TbNum AS No3, TbNum AS No4
WHERE ((("ZZZZ"+Format([No4].[Num]*1000+[No3].[Num]*100+[No2].[Num]*10+[No1].[Num],"0000"))
Not In (SELECT 個人Ｔ.登録番号 FROM 個人Ｔ))
AND (([No4].[Num]*1000+[No3].[Num]*100+[No2].[Num]*10+[No1].[Num]) Between 1 And 20));

で、「個人Ｔ」の「登録番号」の欠番（１～２０までの条件付）が取得できます
これを元に、追加クエリを動作させて終わり

この辺りが理解できるようになれば、Access初心者が抜けられるかな？

この回答への補足

ご回答ありがとうございます。
早速試してみました。
この作成方法はまず
TbNumテーブルを作成し、クエリ作成でTbNum呼び出し追加クエリを作って
追加するテーブルを個人ＴにしてＳＱＬシートにコピーして各箇所を変更

で大丈夫ですよね？
1 AND 20
のまま実行したところ０件更新しますよろしいですか？
ときたので何も変わらなかったのですが、1 And 3209
で実行したらいつまでたっても終わらない状態になってしまいました
１～３２０９の条件に変わった。ということではないのでしょうか・・・

補足日時：2009/04/09 09:59

No.2 回答者： ple_mania 回答日時： 2009/04/08 22:14

以前の質問で提示されていた「個人Ｔ」と「注文Ｔ」の例で、

簡単なコードを作ってみました。Excel上で書いたので動作確認も
出来てませんし、大量更新ならトランザクション処理を考慮するとか
あるのでしょうが参考になればと思います。

'ＤＢを開く
Dim db As DAO.Database
Set db = DAO.OpenDatabase("")

'個人Ｔを開く
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("個人Ｔ", DAO.dbOpenSnapshot)

Dim SQL As String

'個人Ｔがあるだけ処理を繰り返す
Do While Not rs.EOF
'個人Ｔの登録番号をキーにして注文Ｔの連番を更新する
'（該当する登録番号がなければ０件更新）
SQL = "UPDATE 注文Ｔ SET 連番 = '" & rs![連番] & "' WHERE 登録番号 = '" & rs![登録番号] & "'"
db.Execute SQL
'次を読む
rs.MoveNext
Loop

db.Close
Set rs = Nothing
Set db = Nothing

この回答への補足

ご回答ありがとうございます。
書き方が非常に勉強になります。

これは最終的には注文Ｔの連番を作り上げる作業でしょうか？
個人Ｔの登録番号の空き番号をうめるものではない？
みただけで判断できなくて申し訳ありません・・・

補足日時：2009/04/09 14:39

No.1 回答者： 30246kiku 回答日時： 2009/04/08 21:12

大変申し訳ございません。

たぶん、初期のころから回答を付けていたと思います。

ただ、今回のご質問内容はちょっと分かりません。というか先が見えません。

http://oshiete1.goo.ne.jp/qa4823586.html
ここから始まって、

http://oshiete1.goo.ne.jp/qa4848965.html
http://oshiete1.goo.ne.jp/qa4857359.html
http://oshiete1.goo.ne.jp/qa4859369.html
http://oshiete1.goo.ne.jp/qa4860036.html
という流れがあったと思います。


> rs.AddNew
追加するの？

> i = DMin("仮想ID", "ZZZZ空き番号抽出クエリ")
クエリの内容は分かりませんが、常に同じ値が得られそうな雰囲気もあります。

> →→ rs(登録番号) = "ZZZZ" & i
以前の方法では、
rs(登録番号) = "ZZZZ" & Format(i, "0000")
のような形式だったような気もします。


いま一つ、自分は何をしたいのか、見つめ直して（整理して）みてはいかがでしょう。
万が一、元に戻るようなことになっても、今まで得たやり方は今後役に立つのかも?。

この回答への補足

ご回答ありがとうございます。いつもお世話になっております。
少し長くなってしまいますが補足させていただきます

当初予定していたのは前回までに出来た変更予定の連番を元に旧形体の連番に上書き。
といった形をとりたかったのですが登録番号が完全に抜けてしまっている場合、データシートビュー上で番号をつめる事もできませんでした。
なぜなら、登録されていない登録番号に変更することは出来ないからです

個人Ｔ主キーの登録番号を変えてしまうと変える前の番号、
たとえばZZZZ0100をZZZZ0099につめる場合
つめてしまうと一時的にでもZZZZ0100が消えてしまうことになります。
そうなると注文Ｔにある登録番号とつりあわなくなってしまうようです
(リンクテーブル"個人Ｔ"での更新に失敗しましたとエラー)

なので先に
(1)個人Ｔの登録番号の空き番号を埋めてから、
(連番をDMaxで、どこまで埋めればいいか求められると思いました)
(2)注文Ｔの登録番号を、連番を元に登録番号をつめて
(3)個人Ｔの連番の最小値から順に電話番号、会社名などの情報をレコード毎に移す
と、いった流れになりました。


>> rs.AddNew
>追加するの？
空き番号でレコードが無いので追加であっていると思ったのですが・・・
Editとはまた違いますよね

>> i = DMin("仮想ID", "ZZZZ空き番号抽出クエリ")
>クエリの内容は分かりませんが、常に同じ値が得られそうな雰囲気もあります。
while~wendの中に入れることによって最小値を埋めた後にまたDMinをすることによって次の空き番号を求められるのでは、と思ったのですが検討違いだったでしょうか・・・

>rs(登録番号) = "ZZZZ" & Format(i, "0000")
は空き番号だけ埋めることも可能だったのでしょうか
完全な連番にだけ対応しているものと思いました。
試していなかったので試してみたいと思います

(1)～(3)が今行いたい流れになります。
遠回りになっているかもしれませんが、今現状これしか無いように思えます。
何かいい案はありますでしょうか・・・

補足日時：2009/04/09 08:36