Access 2007 Table Record Number Out of Synch with Item # Field

  • Thread starter Thread starter Seth
  • Start date Start date
S

Seth

I have a database table that has an ID field that is automatically generated.
It is the basis of a rickety program I hammered together when I was young and
smart. The ID field must be in synch with the record number field for the
program to work. Somehow (deleted fields?) it no longer is in synch. The
bottom of the table, for example, shows "Record 6993 or 6993, but the ID
field shows 6997. It should show 6993 also. A long time ago I had this
problem and I somehow recopied the database table onto a new table and
renamed it to the old table after deleting the old table. I apologize for my
ignorance. I should be able to figure this out on my own, but I just don't
remember how I did it and have thrown away my textbook. I would very much
appreciate it if someone could help, maybe outline a few simple steps for me.
Again, I apologize for my ineptitude. Seth
 
Seth said:
I have a database table that has an ID field that is automatically generated.
It is the basis of a rickety program I hammered together when I was young and
smart. The ID field must be in synch with the record number field for the
program to work. Somehow (deleted fields?) it no longer is in synch. The
bottom of the table, for example, shows "Record 6993 or 6993, but the ID
field shows 6997. It should show 6993 also. A long time ago I had this
problem and I somehow recopied the database table onto a new table and
renamed it to the old table after deleting the old table. I apologize for my
ignorance. I should be able to figure this out on my own, but I just don't
remember how I did it and have thrown away my textbook. I would very much
appreciate it if someone could help, maybe outline a few simple steps for me.
Again, I apologize for my ineptitude. Seth
Click to expand...

I would create an append query to append the data to a table with the
same structure as the first table. Be sure to place the value of your
item number field into the ID field. For example:

tblMyData
ID AutoNumber
ItemNumber Long
SomeData Text
ID ItemNumber SomeData
1 1 A
2 2 B
3 4 C
4 5 D

Somehow ID and ItemNumber are out of synch. Perhaps a value in another
table was used to record the last used ItemNumber and a run time error
prevented it from actually being used. Try the following steps:

1. Back up the table first (copy and paste the structure and data to
tblMyDataBak)

2. Create a table with the same structure (copy and paste using the
Structure Only paste option to tblMyDataDup)

3. Run the following query:

INSERT INTO tblMyDataDup ( ID, ItemNumber, SomeData ) SELECT ItemNumber,
ItemNumber, SomeData FROM tblMyData;

tblMyDataDup
ID AutoNumber
ItemNumber Long
SomeData Text
ID ItemNumber SomeData
1 1 A
2 2 B
4 4 C
5 5 D

I think that's what you're looking to do. Sometimes, similarly, in
large tables I save a backup ID in another field so that I can recover
foreign key values in the event the primary key index gets corrupted.

James A. Fortune
(e-mail address removed)
 
It appears you anticipate that an AutoNumber is a monotonically increasing
long integer, but that is not necessarily so. There are a number of
conditions under which numbers can be skipped. You would be well advised
not just to correct your current problem, but take this fact into account in
re-designing your database.

One, a few, or many numbers may be skipped in the autonumber sequence, and
it is dangerous to rely on them always increasing by one from the
previously-generated autonumber.

Autonumbers are intended for internal use, joining one table to another, and
the design goal is only that they be unique, not in a particular sequence.

Larry Linson
Microsoft Office Access MVP
 
Back
Top