Help for Tracking Library Rentals with Access

[Kay Price via AccessMonster.com]

I work at a University and have been tasked with restructuring an Access
database that is used to keep track of about 300 books that
students/faculty may check out. I have worked with Access before but not
really as a designer.

Currently, the database consists of only a table which lists the Title,
Subject,and Author. I know that there is so much more that can be done with
Access and I would like some advice on how I should utilize its features.
The main things I would like to be able to do are (1)add new books to the
inventory as they are acquired, (2)report what books are checked out,due,
missing, etc at any given point, (3)track how many times a book is checked
out and by whom and (4)be able to track how many copies of a particular
book are available for checkout.

Any advice will be greatly appreciated!

 

[Fred Boer]

Dear Kay:

A small library! Just my cuppa tea! I'd be happy to give you some help, if
you like. I have created a library system for a small school library in
Access, and although I am *not* an expert (seriously!), I can give you some
suggestions. First suggestion is... don't re-invent the wheel. There are
many good, professional library system applications out there, many of them
very reasonably priced. You might find an excellent library system out
there, at a reasonable price, which would be much cheaper in the long run
than building it yourself. I had some good reasons to build my own system,
(most of all the fun of it!), but you should carefully consider that it
*will* take a lot of time and learning and work to build it yourself if you
aren't a pro!

Access is a relational database, which means that it is great at linking
data together from multiple tables. A single table might work, but not well.
You might consider the following tables:

tblLibrary

Title
DeweyDecimalNumber (or Library of Congress number)
VolumeNumber
FormatID (Lookup to tblFormat)
PublisherID (Lookup to tblPublisher)
DateOfPublication
CopyNumber
PlaceOfPublicationID (Lookup to tblPlaceofPublication)
..
..
..

And whatever other fields will apply specifically to the book in hand.

Some of the information will be best stored in other tables with a
relationship between the tables created..

tblPublisher

PublisherID
PublisherName

tblPlaceOfPublication

PlaceOfPublicationID
Place

tblFormat

FormatID
Format

Now..You also want information about the author(s), right? Another table...

tblAuthor

AuthorID
AuthorFirstName
AuthorMiddleName
AuthorLastName
YearOfBirth
....

To link authors to books, you need an intersection table, because it is a
many-to-many relationship...

tblBookAuthor

BookAuthorID
BookID
AuthorID

Subjects, like authors...

tblSubjects

SubjectID
Subject

To link subject headings to books, another intersection table...

tblBookSubject

BookSubjectID
BookSubject
BookID

Now for circulation, you need a table for patron information:

tblPatrons

PatronID
PatronFirstName
PatronMiddleName
PatronLastName
..... all other information you need about patrons.

And you need another intersection table...

tblCirculation

CirculationID
BookID
PatronID

To check a book out, you append a new record in tblCirculation. To check a
book in, you find the record that applies to the particular loan, and update
the record.

WHEW! That's a lot all at once, I think... See how well you can manage this,
and post back with questions, etc. I will watch this thread.

HTH
Fred Boer

 

[Kay Price via AccessMonster.com]

Fred,

Wow. Thank you soo much. I will see how this all works out. I knew that it
could be done. I was just curious about why having all of the little tables
is better than one big table?

My other question is whether or not you had any specific suggestions for
the library software you talked about. If it is reasonably priced, I may be
able to get funding for it. Otherwise, I am not sure I would be able to
justify the purchase for such a small department.

I will definitely be turning to you in the not-so-distant future.

Thanks again.

 

[Fred Boer]

Dear Kay:

Well, as far as specific suggestions for commercial library software, I
can't recommend anything specific, since, as I said, I created my own using
Access. You might try some of the links here:

http://directory.google.com/Top/Reference/Libraries/Library_and_Information_Science/Software/

Just a quick look at the above listing shows at least one free (open source)
library system: http://www.koha.org/ .
Or perhaps try a search for shareware or freeware at www.tucows.com.

Take a few hours and look around the 'net; you might find just what you
need.

Gotta check on supper now... I'll try and answer the question about why the
little tables later tonight, but I can tell you quickly that your question
relates to the fundamental nature of relational databases and normalization,
so you might want to do some searching for those terms...

HTH
Fred