Key field in book library

[Jim]

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

 

[Guest]

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
(e-mail address removed)

 

[Fred Boer]

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]

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

 

[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]

Hi Jim:

There is a sample database here:

http://rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.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

 

[gls858]

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

 

[John Vinson]

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]

 

[Jim]

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

 

[Jim]

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

 

[Fred Boer]

Ah, Jim, you think like I do! It' just plain fun to build your own!

Listen, if you care to communicate with me privately, I could show you
sample of what I've done, and I'd love to look at what you've come up with.
If you do, you can derive my email address from this post. Just remove the
spam munging. If not, well, that's just fine, too! Good luck with your
projects!

Best wishes,
Fred

 

[John Vinson]

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.

You'ld need both fields in the child table (or tables). You can join
up to ten fields in the Relationships window, or in the query design
window - drag ISBN to ISBN, CopyNo to CopyNo, and you'll get two join
lines.

It's sometimes simpler to use an autonubmer as a "surrogate" key,
especially if you have multiple relationships. If you do, be sure to
put a unique non-primary Index on the combination of ISBN and CopyNo
to prevent the possiblility of having two books with identical
ISBN/CopyNo combinations (and just different BookID's, which the user
shouldn't see and can't use).

John W. Vinson[MVP]

 

[Fred Boer]

Hey, that's a good idea I hadn't thought of... I'm going to use it, too, ok?


Cheers!
Fred Boer

 

[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.

Thanks for the tip. But I'll need to track realities like knowing if
Dora returned copy 1 of "Elsie's Stolen Heart" and if Rachel returned
copy 2 of the same title. It's not like an inventory where all I need is
an "in stock" number. I have some other tips that should do the trick.
This is sure a great forum!

--Jim

 

[Jim]

John,

Thanks for the guidance. You and several others prove that this is a
great newsgroup!

--Jim Levitt

 

[John Vinson]

Hey, that's a good idea I hadn't thought of... I'm going to use it, too, ok?

Great minds run in the same channels...

but then of course, little minds run in the same gutters, so that
doesn't prove anything!

<bg>

John W. Vinson[MVP]