Table redesign help.. retain autonumber?

Hello:

I am considering a redesign of my tables. Currently I use an Autonumber
field called Book_ID, to identify records in my library table (Tbl_Library).
Now, this number has no meaning to the users of the application, so the
possiblity of gaps isn't a problem. However, it *is* used to create barcodes
which are put in the books for use in the library circulation system.

Everything works fine. However, I am a little concerned about the use of an
Autonumber field, since the numbers *are* visible to users (on the barcode
labels), and, more importantly, the possiblity that exists that the numbers
might get very long, or even go negative. A long number might not fit on the
labels, for example.

So, I've created a version of the application in which I don't use
Autonumber for Book_ID, but, instead, replace the Autonumber field with a
simple number field, and populate the field using a DMax expression as the
default value for the Book_ID field. (Kudos to Roger Carlson for his helpful
example at
http://www.rogersaccesslibrary.com/d...berProblem.mdb
!)

My question is: is there any reason to keep the old Autonumber Book_ID
field? Or is it really superfluous given the redesign?

Thanks!

Fred Boer

You don't need to use the AutoNumber Field in this case. Make sure you set
the custom-number-seq. Book_ID as the PK in the new database.

If you want to import Records from the old database to the new database and
you have related Records, e.g. Loans Records, that use the AutoNumber Field
for the relationship, you will need to take care to relate the Records
correctly in the new database.

--
HTH
Van T. Dinh
MVP (Access)




"Fred Boer" wrote in message
...
Hello:

I am considering a redesign of my tables. Currently I use an Autonumber
field called Book_ID, to identify records in my library table
(Tbl_Library).
Now, this number has no meaning to the users of the application, so the
possiblity of gaps isn't a problem. However, it *is* used to create
barcodes
which are put in the books for use in the library circulation system.

Everything works fine. However, I am a little concerned about the use of
an
Autonumber field, since the numbers *are* visible to users (on the barcode
labels), and, more importantly, the possiblity that exists that the
numbers
might get very long, or even go negative. A long number might not fit on
the
labels, for example.

So, I've created a version of the application in which I don't use
Autonumber for Book_ID, but, instead, replace the Autonumber field with a
simple number field, and populate the field using a DMax expression as the
default value for the Book_ID field. (Kudos to Roger Carlson for his
helpful
example at

http://www.rogersaccesslibrary.com/d...berProblem.mdb
!)

My question is: is there any reason to keep the old Autonumber Book_ID
field? Or is it really superfluous given the redesign?

Thanks!

Fred Boer

Thanks, Van!

Fred

"Van T. Dinh" wrote in message
...
You don't need to use the AutoNumber Field in this case. Make sure you set
the custom-number-seq. Book_ID as the PK in the new database.

If you want to import Records from the old database to the new database
and
you have related Records, e.g. Loans Records, that use the AutoNumber
Field
for the relationship, you will need to take care to relate the Records
correctly in the new database.

--
HTH
Van T. Dinh
MVP (Access)




"Fred Boer" wrote in message
...
Hello:

I am considering a redesign of my tables. Currently I use an Autonumber
field called Book_ID, to identify records in my library table
(Tbl_Library).
Now, this number has no meaning to the users of the application, so the
possiblity of gaps isn't a problem. However, it *is* used to create
barcodes
which are put in the books for use in the library circulation system.

Everything works fine. However, I am a little concerned about the use of
an
Autonumber field, since the numbers *are* visible to users (on the
barcode
labels), and, more importantly, the possiblity that exists that the
numbers
might get very long, or even go negative. A long number might not fit on
the
labels, for example.

So, I've created a version of the application in which I don't use
Autonumber for Book_ID, but, instead, replace the Autonumber field with
a
simple number field, and populate the field using a DMax expression as
the
default value for the Book_ID field. (Kudos to Roger Carlson for his
helpful
example at


http://www.rogersaccesslibrary.com/d...berProblem.mdb
!)

My question is: is there any reason to keep the old Autonumber Book_ID
field? Or is it really superfluous given the redesign?

Thanks!

Fred Boer

Fred

You probably already considered this, but if your Autonumber (PK) field is
used as a foreign key in any other tables (one-one, one-many), you'll need
to keep the value. Or you could decide to dump it, after updating all
"child" tables with the new ID.

--
Good luck

Jeff Boyce
Access MVP

Hello, Jeff!

Yes, I have recreated all of the relationships to refer to the new BookID
number. I couldn't *think* of a good reason to keep the old Autonumber
field, but over the years I've learned to ask first rather than regret it
later.. g

Thanks a lot!

Fred


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Fred

You probably already considered this, but if your Autonumber (PK) field is
used as a foreign key in any other tables (one-one, one-many), you'll need
to keep the value. Or you could decide to dump it, after updating all
"child" tables with the new ID.

--
Good luck

Jeff Boyce
Access MVP