access IDの振り直しについて

質問

皆さん、明けましておめでとうございます。今年もいろいろとお世話になりますm(_ _)m

さて、accessに挑戦しているところですが、データをいろいろいじくっているうちに、オートナンバー型のIDが不整列となってしまいました。そこで、いったん「デザイン」からIDを削除し、再び「行の挿入」でIDを作成したところ、今度はまたIDが別の形で(飛び番=例えば、100の次に1000が来て、1100の次に101が来るというような)不整列となりました。

テーブルのフィールドには「No」というのを設けてあり、これはデータのレコード番号なのですが、それとIDが一致するようにするつもりでした。

このような場合、IDの「振り直し」はどのようにやれば出来るのでしょうか？

もう一つお尋ねです。最終「No」とレコード数は一致するはずなのですが、レコードが１個足りません。このような場合、何番のレコード(No)が抜けているのか知るには、どのようにすればよろしいでしょうか？

access初心者ですので、いろんなところでつまづいてしまいます。どうかご教示のほどよろしくお願いしますm(_ _)m

xp+office2003です。

回答No.5

nicotinism · ベストアンサー

こんにちは
前にも述べましたが、
オートナンバーは一意の番号を得るためのものであって連番の為のものではありません。

一時的な「連番」はこのようなクエリで求められます。
テーブル名を T1 としています。
新規SQLビューに貼り付けてみて下さい。

SELECT T1.ID, CLng(DCount([ID],"T1","ID<=" & [ID])) AS 連番
FROM T1
ORDER BY T1.ID;

ど～～～～しても連番にしたい欠番も嫌！という場合はオートナンバー型を止めて
長整数型にしてフォームのイベントでVBAにて付与するしかありません。
が・・・一意である ID の値がコロコロ変わると他のテーブルとリンクしていた場合に
別の問題が発生しそうです。
なのでオートナンバー型の連番にこだわっても『労多くして功少なし』です。
　　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ところで欠番レコードを求めるためには連番のあるテーブルが必要なのですが
エクセル2003まででは65536までしか求められないので
テーブルを新規作成し、名前をTR、フィールド名を連番とします。
連番フィールドに、0から9 まで番号を手入力します
（10レコード出来上がります）
このテーブルを使ってクエリを作ります。
SQLビューだと
SELECT TR.連番, TR_1.連番, TR_2.連番, TR_3.連番, TR_4.連番, TR_5.連番,
[TR].[連番] & [TR_1].[連番] & [TR_2].[連番] & [TR_3].[連番] & [TR_4].[連番] & [TR_5].[連番] AS 仮番,
CLng([TR].[連番] & [TR_1].[連番] & [TR_2].[連番] & [TR_3].[連番] & [TR_4].[連番] & [TR_5].[連番]) + 1 AS 新番
FROM TR, TR AS TR_1, TR AS TR_2, TR AS TR_3, TR AS TR_4, TR AS TR_5
ORDER BY CLng([TR].[連番] & [TR_1].[連番] & [TR_2].[連番] & [TR_3].[連番] & [TR_4].[連番] & [TR_5].[連番]) + 1;

同じテーブルをクエリのデザインビューにドラッグするたびに添え字が_1 _2 _3 と自動的に付きます。

で10万レコードまでの「連番」が出来ますがご参考までに。
（かなり重たいですPentium 2.8Ghzで20秒弱掛かります）
これを元に不一致クエリを作成すれば欠番は出せますが、止まったのかと思える位相当重いはずです。
※万が一を考えてバックアップは取ってから試して下さい。

補足

ご返事が遅くなりましたm(_ _)m

詳細なアドバイスを頂きまして、落ち着いたときにトライしてみようと思います。これまで時間のない時に焦っていじくるうちに、ドジッた経験が何度もありますので・・・(^^;)

＞なのでオートナンバー型の連番にこだわっても『労多くして功少なし』です

ありがとうございます。初心者なので、なんか連番でないとまずいような(何の根拠もない・・・)気持ちに囚われていたんです。

最近では、「まっ、いいか！」という気分です。いろいろご教示いただき、ありがとうございました。ｍ（＿　＿）ｍ

回答No.4

beach2005

同じ構造のテーブルを作成して、ID以外のデータを貼りつけて、もとのテーブル名をつける・・・とかではだめでしょうか？
思いついただけなのですが・・・。

補足

ご回答ありがとうございますm(_ _)m

早速やってみたのですが、レコード数が66,000以上あって、エラーが出てしまいます(^^;)

IDの“飛び番”にこだわりすぎかな？と半分諦めモードです。

またいいお知恵があればご教示下さい。

回答No.3

nicotinism

オートナンバーに関しては好きなのを選んでください (^^ゞ
リンクが切れないと良いのですが・・
http://support.microsoft.com/search/default.aspx?catalog=LCID%3D1041&1041comm=1&1041mt=1&1033comm=1&spid=global&query=%E3%82%AA%E3%83%BC%E3%83%88%E3%83%8A%E3%83%B3%E3%83%90%E3%83%BC&pwt=false&title=false&kt=ALL&mdt=0&res=20&ast=1&ast=2&ast=3&ast=&ast=&mode=a&adv=1

65536レコードまでの欠番の探し方はExcelで連番を作成して、Accessにインポート。
で、そのテーブルとの不一致クエリで求められます。

「No」はAccessの予約語なので変更することをお勧めします。
http://support.microsoft.com/default.aspx?scid=kb;ja;286335

オートナンバーは一意（ユニーク）な番号を得るためにあるので
不連続となっていても気にすることはないです。
オートナンバーフィールドがクエリやりレーションシップのキーに使われていたりすると、
番号の振りなおしも難しくなりますし、レコードを削除すれば欠番もでます。
そもそも連番のためのデータ型ではありません。
またテーブルでは必ずしも入力順に並ぶ訳ではありませんし。

補足

ご回答、ありがとうございますm(_ _)m

いいサイトをご紹介していただき、参考になりそうです。

#2の方の解答とあわせてトライしてみます。

＞オートナンバーは一意（ユニーク）な番号を得るためにあるので
不連続となっていても気にすることはないです
＞そもそも連番のためのデータ型ではありません

そんな仰っていただき、少し気が楽になりました。

＞「No」はAccessの予約語なので変更することをお勧めします

は知りませんでした(^^;)

早速これについては改めようと思います。

回答No.2

charlief

手元のパソコンにAccessが入ってないので、確認できませんが。オートナンバーフィールド挿入の方法を使うと、元のレコードの順番が不特定になります。定かではないですが、下記の様な方法で試してみてください。
１．元テーブルと同じテーブルを作成する
２．クエリーの新規作成で編集画面を開く
３．メニューバーで追加クエリーを選択、追加先テーブルをコピーしたテーブルに指定する
４．編集画面に元テーブルを追加する
５．オートナンバーフィールドを除くすべてのフィールドを一覧に追加する。
６．追加元テーブルフィールドの下方に追加先テーブルフィールドが表示されるので、一致であることを確認。一致でなければ、変更することもできる。
７．noフィールドの並び替えを上り順に指定する
８．クエリーを実行する。
９．レコードを確認。
１０．問題がなければ、元のテーブルを削除して、新しいテーブルのテーブル名を元のテーブル名に変更する

やり方がいろいろがあると思いますが、Accessクエリーの中身は、ＳＱＬ文です。データーベースを扱うにはＳＱＬが欠かせません。

欠番レコードを割り出し方法も、抽出クエリーでできるので、チャンレンジしてみてください。

補足

ご回答、ありがとうございますm(_ _)m

#3の方のご回答とあわせてトライしてみます。

回答No.1

charlief

どのようにレコードを挿入しているかはわかりませんが、おそらく挿入元となるレコードがソートされていないです。クエリで挿入処理をしているのであれば、クエリの編集画面で、ソート順を指定できるので、やってみてはいかが？
>もう一つお尋ねです。最終「No」とレコード数は一致するはずなのですが、レコードが１個足りません
元のレコード数は足りてますか？

補足

早速のご回答、ありがとうございますm(_ _)m

どうも私の説明が不十分だったようですね(^^;)

テーブルにレコードを挿入しているのではなく、テーブルのデザイン画面で「行の挿入」をしているのです。つまり、テーブルで言えば「ID」という名前の「フィールド」を挿入しているのです。

この「ID」が“飛び番”になってしまうので、いったん「ID」フィールドを削除し、改めて「ID」フィールドを設定しているわけです。

＞>もう一つお尋ねです。最終「No」とレコード数は一致するはずなのですが、レコードが１個足りません

＞元のレコード数は足りてますか？

レコード数が、65,000くらいあって、「ID」とは別に通し番号の「No」というフィールドを設けているのですが、レコードが欠けていないかぎり、「No」の最終値(最大値)とレコード数は一致するはずなのです。

それが１個足りません。65,000個の「No」を目で追ってゆくわけにも行かず、なんかクエリを利用してどの番号が欠けているか調べたいのですが・・・。EXCELなら簡単に出来るのですがね。１行前の「No」との“差”を計算して、これが「2」になっているところで、欠番が生じている事になります。

ACCESSで、一つ前の「No」との“差”を計算するということができればいいのですが・・・。