登録番号を詰めたい２

質問

重要な部分が欠けていたので質問させていただきます。またご教授おねがいします。
以前このような質問をしたものですが、
http://okwave.jp/qa4859369.html
連番がずれてしまっていたので、以前説明できてなかった部分を説明したいと思います。

電話番号や会社名などの個人情報が入っている個人情報テーブル(以下個人Ｔ)と
値段や出荷した荷物などの詳細が書かれている注文テーブル(以下注文Ｔ)があるとします。
２つのテーブルは"登録番号"とフィールドで関連付けをしてあります。
その登録番号を虫食い無しの綺麗な連番にしたいと思います

以下の様な状態です。
"連番"というのは後の作業で"登録番号"と入れ替えをするために作成されたものです。

注文Ｔ　　　　注文Ｔ　　｜　　個人Ｔ　　　　　個人Ｔ
登録番号　　　連番　　　｜　登録番号　　　　　連番
ZZZZ0001　　　null　　　｜　ZZZZ0001　　　　ZZZZ0001
ZZZZ0002　　　null　　　｜　ZZZZ0002　　　　ZZZZ0002
ZZZZ0002　　　null　　　｜　ZZZZ0003　　　　ZZZZ0003
ZZZZ0004　　　null　　　｜　ZZZZ0004　　　　ZZZZ0004
ZZZZ0006　　　null　　　｜　ZZZZ0006　　　　ZZZZ0005
(テーブル２には同じ会社から何度も注文がある場合"登録番号"フィールドには同じ"登録番号"が存在する場合がある)
ところが、個人Ｔには個人情報があるにもかかわらず注文Ｔには注文された内容が無い場合があります。

以前質問した時に教えていただいたものですと

Private Sub Sample4()
　　Dim rs As New ADODB.Recordset
　　Dim i As Integer
　　Const フィールド名 As String = "☆連番☆"　　　　' ☆１
　　Const テーブル名 As String = "☆元テーブル☆"　　' ☆１
　　Const 抽出 As String = "☆登録番号☆"　　　　　　' ☆１
　　Const 並び替え As String = "☆登録番号☆"　　　　' ☆１
　　Dim sTmp As String

　　i = 0
　　sTmp = ""
　　rs.Source = "SELECT * FROM " & テーブル名 & _
　　　　　　　　" WHERE " & 抽出 & " LIKE 'ZZZZ%' ORDER BY " & 並び替え & ";"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
　　While (Not rs.EOF)
　　　　If (rs(並び替え) <> sTmp) Then
　　　　　　i = i + 1
　　　　　　sTmp = rs(並び替え)
　　　　End If
　　　　rs(フィールド名) = "ZZZZ" & Format(i, "0000")
　　　　rs.Update
　　　　rs.MoveNext
　　Wend
　　rs.Close
End Sub

になりますが、反映させた結果は、

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

と、いうものになります。

本来個人情報だけがある登録番号(ZZZZ0003)に、登録番号(ZZZZ0004)の情報が加わってしまうのです。
したがってまったく関連がない会社同士の情報が混ざってしまうのです
(個人情報しかない"登録番号"でずれてしまう)

教えていただいたものは完璧だったのですが、私が情報の足りない質問をしてしまったのが失敗でした・・・申し訳ありません。

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

注文Ｔがこのような形になるのが理想なのですが、どのように連番を振る途中で飛ばすのか分かりません。
だいぶＶＢＡが変わってしまいそうな気もしますが、どうかよろしくお願い致します。

回答No.3

30246kiku · ベストアンサー

私も考え方は、＃１、＃２さんと同じです。

同じ事を書いていると思いますが、
既に個人Tの連番に新しい連番を振っている(?)ので、注文Tの登録番号（旧体系）と個人Tの登録番号（旧体系）を関連付けて個人Tの連番を引っ張ってくれば良いと思います。

Private Sub Sample5()
　　Dim sSql As String

　　sSql = "UPDATE 注文T INNER JOIN 個人T ON 注文T.登録番号=個人T.登録番号 SET 注文T.連番 = 個人T.連番;"
　　CurrentProject.Connection.Execute sSql
End Sub

（実行は、データをバックアップしてから）

> 引用するだけですと注文Ｔの連番は空きがなくなってしまいませんか？
> 会社名だけわかっていて注文を受けていない登録番号もありますので・・・

ここの部分、もう少し分かりやすく説明してもらえませんか？

お礼

お礼から失礼します。
教えていただいたものをそのまま試してみました。
実行はできそうなのですが、エラーが出てしまいました。
「指定されたデータ量がフィールドサイズを超えています。データ量を減らし、挿入または貼り付けを行って下さい」
となってしまいます。
これは補足で書きました個人Ｔ連番の書き込み制限である１２文字だと思います。
データシートで注文Ｔ連番に１２文字で入力しようとすると強制的に８文字にされてしまいます。

Dim ID As String

ID ="個人Ｔ.連番"
ID = Mid$(ID, 5)

など試してみましたがだめでした。根本的に私の書き方が悪いのだと思いますが・・・
考え方としてはXXXXを抜いてから注文Ｔ連番へ、でいいとは思うのですが
何かいい案はありますでしょうか？

補足

ご回答ありがとうございます。
>既に個人Tの連番に新しい連番を振っている(?)
そうです。空き番号のない連番が振られている状態です、情報が足りなくて申し訳ありませんでした＞＜
不一致クエリでどれが中身のない登録番号かは分かったのですがVBAでの書き方がわかりませんでした・・・

> 引用するだけですと注文Ｔの連番は空きがなくなってしまいませんか？
> 会社名だけわかっていて注文を受けていない登録番号もありますので・・・
これは、引用というのを完全にコピーして貼り付けをする。
という風に理解してしまったのでこの様に書いてしまいました。
実際には登録番号(旧体系)を関連付けてある場合は引用をして、無い場合は飛ばす。と言う事だったのですね

また追加してしまって申し訳ないのですが、個人Ｔ連番には書き込み制限がありまして、１２文字でないとDBにアップロードできないようでした。
なので

注文Ｔ　　　　　注文Ｔ　　　　｜　個人Ｔ　　　　　個人Ｔ
登録番号　　　　連番　　　　　｜　登録番号　　　　連番
ZZZZ0001　　　ZZZZ0001　　　｜　ZZZZ0001　　　　XXXXZZZZ0001
ZZZZ0002　　　ZZZZ0002　　　｜　ZZZZ0002　　　　XXXXZZZZ0002
ZZZZ0002　　　ZZZZ0002　　　｜　ZZZZ0003　　　　XXXXZZZZ0003
ZZZZ0004　　　ZZZZ0004　　　｜　ZZZZ0004　　　　XXXXZZZZ0004
ZZZZ0006　　　ZZZZ0005　　　｜　ZZZZ0006　　　　XXXXZZZZ0005

と、なっています。
この余分なXXXXを取るには
個人Ｔ連番 = Mid$(個人Ｔ連番, 5)
このような形を取れば外すことはできるのでしょうか？

回答No.4

Dxak

＃１です

> UPDATE 注文Ｔ SET 注文Ｔ.連番 = DLookUp("連番","個人T","登録番号 = '" & [登録番号] & "'");

これは、「個人Ｔ.登録番号」に、プライマリキー（主キー）の割り当てがされていない前提で、考慮したため・・・JOIN句を使わず、DLookUp関数で、「個人Ｔ」から、引用してます

> 更新クエリは注文Ｔから登録番号と連番を選択するだけですよね？

違うと思うけど？？？
更新クエリで、「注文Ｔ」から、「連番」を選択し
DLookUp("連番","個人T","登録番号 = '" & [登録番号] & "'")
値を更新していると言う状態ですよ

普通は、クエリは、言葉で説明せずに、ＳＱＬビューに切り替えて、ＳＱＬ文というのを貼り付けてるだけですので・・・

コピー＆ペーストで、大体、終わるはずで、打ち直しの必要が、ありませんが・・・実際のテーブル名、フィールド名が違う場合、自分で書き換えになります

で、
> XXXXZZZZ0005
など、なら・・・「Right(～,8)」で、右から８文字切り取れば良いという事ね

他に、補足を読めば読むほど、判らないのですが・・・
「注文Ｔ」の「連番」って・・・発注元の会社登録番号？（＝個人Ｔ.登録番号）
これなら、更新クエリで行わず（余分なデータと思われる）、常時、選択クエリで良いような気がするのですが・・・

SELECT 注文T.登録番号, Right(個人T.連番,8) As New連番
FROM 注文T INNER JOIN 個人T ON 注文T.登録番号 = 個人T.登録番号;

って、感じね
これが、主キーの割り当てをしておらず、更新可能なクエリにならないので、何とか！っと、言うことなら、DLookUp関数で

SELECT 注文Ｔ.登録番号, Right(DLookUp("連番","個人T","登録番号 = '" & [登録番号] & "'"),8) AS New連番
FROM 注文Ｔ;

と、言う感じ・・・

補足

ご回答ありがとうございます。
>「注文Ｔ」の「連番」って・・・発注元の会社登録番号？（＝個人Ｔ.登録番号）

この連番を振っている理由としまして、
仮に振っている新登録番号なのです。旧体系の注文Ｔ.登録番号と仮連番である注文Ｔ.連番を入れ替える、もしくは注文Ｔ.連番を注文Ｔ.登録番号に上書きしたかったのです。

Right(個人Ｔ.連番,8)を参考に＃３さんのコードでやってみたところ
うまく連番を振ることが出来ました。
説明が下手で申し訳ありませんでした・・・

回答No.2

ple_mania

こんばんは。

わたしもDxakさんと同意見ですね。

最初に提示された個人Ｔは既に登録番号の新旧対照表に
なっているという認識です。したがって、個人Ｔを１件
ずつ読みながら、登録番号が一致する注文Ｔの連番を
個人Ｔの連番で更新してあげれば望んだ状態になると
思われます。

残念なことにDAOのコードばかり見ている間に、ADOの
コードは忘れてしまったので、コード自体の提示は
控えます。

認識相違があれば読み飛ばしてかまいませんが、
参考になるようならご自身で実装されたコードを
こんな観点で見直してみてはいかがでしょう。

補足

ご回答ありがとうございます。
>最初に提示された個人Ｔは既に登録番号の新旧対照表に
なっているという認識です。
はい、その認識で問題ありません。個人Ｔ連番は空きの無い完璧な連番です
そういう補足が足りなかったですね・・・申し訳ありませんでした。

同じ登録番号があるときにのみ、その登録番号のレコードにある連番で更新
といった考え方でしょうか。なるほど、動きがわかりました！

＃１さんの引用とはこう言う事だったのですね。読解力が無くてすみませんでした

回答No.1

Dxak

もう少し判りやすく説明が、ならないでしょうか？
読んでる途中で、挫折しましたorz

で・・・結果だけ見れば、注文Ｔの登録番号をキーに、個人Ｔの連番を引用しているだけのように見えます
それなら更新クエリで

UPDATE 注文Ｔ SET 注文Ｔ.連番 = DLookUp("連番","個人T","登録番号 = '" & [登録番号] & "'");

で、終わりますが？？？
もう少し規則性、法則性を、判りやすく、まとめてみて下さい

補足

ご回答ありがとうございます。
長くなってしまって申し訳ありません。
重要なのは
>本来個人情報だけがある登録番号(ZZZZ0003)に、登録番号(ZZZZ0004)の情報が加わってしまうのです。
ということです。ずれて違う会社の注文が登録されてしまうこと・・・

引用するだけですと注文Ｔの連番は空きがなくなってしまいませんか？
会社名だけわかっていて注文を受けていない登録番号もありますので・・・

教えていただいた更新クエリをやってみたのですが、指定した式の構文が正しくありません。となってしまいました。

更新クエリは注文Ｔから登録番号と連番を選択するだけですよね？
式は連番のレコードの更新に書き込みました。