Key field in book library

I designed a database for my church and used the 13 character ISBN (978
prefix plus the classic 10 character) as the key field for each book.
We're using a 39 scanner code for the books and patrons.

I'm stumped at how to track multiple copies of the same book title.

I can add a character to the ISBN and print my own bar codes that are
unique, but that would waste a lot of time since the 13 character codes
are already on the back of the books. If I only use the ISBN numbers as
the unique keys (as I'm doing now) it won't permit duplicates.

Do you use a composite key? Like ISBN and copyNum?

I'm probably missing something simple. Any clues?

Thanks.

--Jim

Jim, use a separate field called BookID (set to autonumber) to be your
primary key. The key should serve the primary purpose of keeping your
records unique, and not as an additional data field for the user to store
other data. Does that address the question?

Destin Richter


"Jim" wrote:

I designed a database for my church and used the 13 character ISBN (978
prefix plus the classic 10 character) as the key field for each book.
We're using a 39 scanner code for the books and patrons.

I'm stumped at how to track multiple copies of the same book title.

I can add a character to the ISBN and print my own bar codes that are
unique, but that would waste a lot of time since the 13 character codes
are already on the back of the books. If I only use the ISBN numbers as
the unique keys (as I'm doing now) it won't permit duplicates.

Do you use a composite key? Like ISBN and copyNum?

I'm probably missing something simple. Any clues?

Thanks.

--Jim

Dear Jim:

As Destin has suggested an autonumber primary key would be the reasonable
solution. This will require however, that you use the autonumber primary key
as your book code, and print barcode labels using the primary key number.
This does, unfortunately, mean you can't just use the ISBN for your barcode
label.

In addition, you might want to consider the use of a "custom" autonumber as
a primary key, since an Autonumber field can develop gaps, become large, and
even possibly become negative numbers.

HTH
Fred Boer



"Jim" wrote in message
...
I designed a database for my church and used the 13 character ISBN (978
prefix plus the classic 10 character) as the key field for each book. We're
using a 39 scanner code for the books and patrons.

I'm stumped at how to track multiple copies of the same book title.

I can add a character to the ISBN and print my own bar codes that are
unique, but that would waste a lot of time since the 13 character codes
are already on the back of the books. If I only use the ISBN numbers as
the unique keys (as I'm doing now) it won't permit duplicates.

Do you use a composite key? Like ISBN and copyNum?

I'm probably missing something simple. Any clues?

Thanks.

--Jim

Destin,
Thanks!
I think I understand the purpose of a key field for in keeping records
unique. But if I use a unique bookID and want to use barcodes for
check-in and check-out, won't that force me to create barcodes on the
bookID field for every book?
I did that at first, but then wondered that since each book comes with a
barcode, there might be a way to avoid re-barcoding all the books for
the 1% of cases where I have multiple copies. My guess is probably not.

--Jim

Destin Richter wrote:
Jim, use a separate field called BookID (set to autonumber) to be your
primary key. The key should serve the primary purpose of keeping your
records unique, and not as an additional data field for the user to store
other data. Does that address the question?

Destin Richter


"Jim" wrote:


I designed a database for my church and used the 13 character ISBN (978
prefix plus the classic 10 character) as the key field for each book.
We're using a 39 scanner code for the books and patrons.

I'm stumped at how to track multiple copies of the same book title.

I can add a character to the ISBN and print my own bar codes that are
unique, but that would waste a lot of time since the 13 character codes
are already on the back of the books. If I only use the ISBN numbers as
the unique keys (as I'm doing now) it won't permit duplicates.

Do you use a composite key? Like ISBN and copyNum?

I'm probably missing something simple. Any clues?

Thanks.

--Jim

Fred,

Thanks for the feedback. I appreciate your insights, Fred.

I suspect my choice is between doing this right--using a BookID field
for key and printing out bar codes--and making it complicated by trying
to save time by not re-printing barcodes.

Where can I find guidance in designing a 'custom' autonumber field?

Thanks again!

--Jim Levitt


Fred Boer wrote:

Dear Jim:

As Destin has suggested an autonumber primary key would be the reasonable
solution. This will require however, that you use the autonumber primary key
as your book code, and print barcode labels using the primary key number.
This does, unfortunately, mean you can't just use the ISBN for your barcode
label.

In addition, you might want to consider the use of a "custom" autonumber as
a primary key, since an Autonumber field can develop gaps, become large, and
even possibly become negative numbers.

HTH
Fred Boer



"Jim" wrote in message
...

I designed a database for my church and used the 13 character ISBN (978
prefix plus the classic 10 character) as the key field for each book. We're
using a 39 scanner code for the books and patrons.

I'm stumped at how to track multiple copies of the same book title.

I can add a character to the ISBN and print my own bar codes that are
unique, but that would waste a lot of time since the 13 character codes
are already on the back of the books. If I only use the ISBN numbers as
the unique keys (as I'm doing now) it won't permit duplicates.

Do you use a composite key? Like ISBN and copyNum?

I'm probably missing something simple. Any clues?

Thanks.

--Jim

Hi Jim:

There is a sample database he

http://rogersaccesslibrary.com/downl...berProblem.mdb

That should help you.

Just as an aside, I have used Access to create a library application for a
small school library. How far along are you in creating your own
application? Have you considered a professional/shareware/freeware
application? I was just looking at one yesterday, and it seemed reasonably
fully featured, and cost only $59.95! I enjoyed creating my own application,
but, given that a professionally created application might be available for
the cost of a few books.. well, that is certainly worth considering!

Cheers!
Fred


"Jim" wrote in message
...
Fred,

Thanks for the feedback. I appreciate your insights, Fred.

I suspect my choice is between doing this right--using a BookID field for
key and printing out bar codes--and making it complicated by trying to
save time by not re-printing barcodes.

Where can I find guidance in designing a 'custom' autonumber field?

Thanks again!

--Jim Levitt


Fred Boer wrote:

Dear Jim:

As Destin has suggested an autonumber primary key would be the reasonable
solution. This will require however, that you use the autonumber primary
key as your book code, and print barcode labels using the primary key
number. This does, unfortunately, mean you can't just use the ISBN for
your barcode label.

In addition, you might want to consider the use of a "custom" autonumber
as a primary key, since an Autonumber field can develop gaps, become
large, and even possibly become negative numbers.

HTH
Fred Boer



"Jim" wrote in message
...

I designed a database for my church and used the 13 character ISBN (978
prefix plus the classic 10 character) as the key field for each book.
We're using a 39 scanner code for the books and patrons.

I'm stumped at how to track multiple copies of the same book title.

I can add a character to the ISBN and print my own bar codes that are
unique, but that would waste a lot of time since the 13 character codes
are already on the back of the books. If I only use the ISBN numbers as
the unique keys (as I'm doing now) it won't permit duplicates.

Do you use a composite key? Like ISBN and copyNum?

I'm probably missing something simple. Any clues?

Thanks.

--Jim

Jim wrote:
I designed a database for my church and used the 13 character ISBN (978
prefix plus the classic 10 character) as the key field for each book.
We're using a 39 scanner code for the books and patrons.

I'm stumped at how to track multiple copies of the same book title.

I can add a character to the ISBN and print my own bar codes that are
unique, but that would waste a lot of time since the 13 character codes
are already on the back of the books. If I only use the ISBN numbers as
the unique keys (as I'm doing now) it won't permit duplicates.

Do you use a composite key? Like ISBN and copyNum?

I'm probably missing something simple. Any clues?

Thanks.

--Jim
Maybe all you need is a couple of quantity fields. Something to track the
number of copies in house and checked out. When you check a book out
it subtracts from one field and adds to the other so you always
know what you have on hand.

gls858

On Wed, 15 Jun 2005 08:27:45 -0500, Jim wrote:

I did that at first, but then wondered that since each book comes with a
barcode, there might be a way to avoid re-barcoding all the books for
the 1% of cases where I have multiple copies. My guess is probably not.

Well... since duplicate copies will have identical preprinted bar
codes, you're stuck. If you want the bar code reader to distinguish
which copy is which, you have no choice but to put the copy number on
the bar code somehow.

I'd suggest using a joint two-field primary key, ISBN and CopyNo
(default value 1); concatenate them to create the bar code.

John W. Vinson[MVP]

Fred,

Thanks for your insight. I appreciate the spirit in which you write.
It's thoughtful and encouraging, Fred.

My library database is 95% finished, I have 1,200 volumes what we call a
resource center. It's all non-fiction. If I have time and the
volunteers, I'll re-barcode the books. Like yourself, I enjoy doing
projects like this, so it's not a factor of what is most cost effective.
It gives me the chance to improve my skills with Access.

I'm redesigning a similar database for another location (our children's
and youth library) so I will use a bookID (not ISBN) as key field for
that project.

Thanks again, Fred.

--Jim Levitt

John,

Thanks for the tip. If I have enough volunteers this summer, I may go
back and reprint the bar codes for the 1,200 books I have in the
database using a unique bookID field.

If I were to use a two-field primary key, how do I place that two-field
key in a one-to-many relationship with my book checkout table? Would
that dual field link to one field or two in the secondary table? I
haven't seen an example of how that is done.

Thanks John!

--Jim Levitt

John Vinson wrote:
Well... since duplicate copies will have identical preprinted bar
codes, you're stuck. If you want the bar code reader to distinguish
which copy is which, you have no choice but to put the copy number on
the bar code somehow.

I'd suggest using a joint two-field primary key, ISBN and CopyNo
(default value 1); concatenate them to create the bar code.

John W. Vinson[MVP]