Changes to Autofill Numbering

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Hi,

I have a database that has a key field that is a unique number, where
records are added, this field increments the number by one. There are
several thousand records with that run from 1 to 9654 albeit many of the
records have been deleted and thus there are huge gaps in the number. We
have a requirement to renumber the existing records from 1 to 2010 (2010 is
the number of live records) but because the table that the number is held is
linked to other tables on the field and Autofill is set in the properties,
we don't appear to have a means to alter the numbering.

Any ideas would be most welcome.

Thanks, Rob
 
Rob

If the only reason why you want to renumber is so you'll know how many
records you have, don't use (and don't change your) Autonumbers. Instead,
run a simple Totals query that counts the number of records ... or use the
DCount() function.

Access Autonumbers are intended to be used as unique row identifiers, to
help in relationships among tables (as it sounds like you've done).
Modifying a table's key ID could open a mess of work making sure the changes
"ripple" out to the child records. Or you could look at the Cascading
Updates setting in the relationship window.

Before you do any of that, make a backup!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Rob said:
Hi,

I have a database that has a key field that is a unique number, where
records are added, this field increments the number by one. There are
several thousand records with that run from 1 to 9654 albeit many of
the records have been deleted and thus there are huge gaps in the
number. We have a requirement to renumber the existing records from
1 to 2010 (2010 is the number of live records) but because the table
that the number is held is linked to other tables on the field and
Autofill is set in the properties, we don't appear to have a means to
alter the numbering.
Any ideas would be most welcome.

Thanks, Rob
Click to expand...

Easy, leave the existing number as it is. Don't worry about what number
is what and gaps. The only need for that field is that the numbers be
unique. It is best that any viewer of your data never even see that number.
Consider it an internal number only. Don't add it to queries reports or
forms and avoid letting users see tables.

Then add a new field for the public number. I might suggest that you
may not even want to use auto number to fill that one in as autonumber does
not guarantee consecutive numbers. There are other methods to fill in
consecutive numbers.
 
Thanks for the advice Jeff.

I need to renumber as we want to use the id no. as a physical file reference
number that already exists. I tried adding new field for this purpose but
because of the links, this also failed.

Rob
 
Rob said:
Thanks for the advice Jeff.

I need to renumber as we want to use the id no. as a physical file reference
number that already exists. I tried adding new field for this purpose but
because of the links, this also failed.

Rob
Click to expand...

The other posters have given good advice. If you are in a situation
where you cannot follow their advice perhaps the following method will
work (make a backup first):

Suppose you have the following tables (two records being deleted from
tblMain have caused gaps):

tblMain
MID AutoNumber (PK)
Data1 Text
MID Data1
2 D1
4 D2

tblSubMain
SMID AutoNumber (PK)
MID Long (Foreign Key)
SubData Text
SMID MID SubData
1 2 SD1
2 2 SD2
3 4 SD3
4 4 SD4

You'd like to end up with:

tblMain
MID AutoNumber (PK)
Data1 Text
MID Data1
1 D1
2 D2

tblSubMain
SMID AutoNumber (PK)
MID Long (Foreign Key)
SubData Text
SMID MID SubData
1 1 SD1
2 1 SD2
3 2 SD3
4 2 SD4

Starting with:

tblMain
MID AutoNumber (PK)
Data1 Text
MID Data1
2 D1
4 D2

Take the primary key off MID, change the data type to Number Long and
rename it OldKey (delete the Default Value as well). Save the design.

Add a new AutoNumber primary key called MID. Save the design again.

tblMain should now look like:

tblMain
MID OldKey Data1
1 2 D1
2 4 D2

Then run the following query:

UPDATE tblMain INNER JOIN tblSubMain ON tblMain.OldKey = tblSubMain.MID
SET tblSubMain.MID = tblMain.MID;

Note: It is important that the update query is only run once.

Then delete the OldKey field and put any relationships back :-).

James A. Fortune
(e-mail address removed)
 
Back
Top