Lending Library Records

[Sarah]

I've just found these discussion groups and have noticed that responses to
questions by novices and newbies are really informative, helpful and
generous, therefore......

I am trying to draw up a database to replace a manual system that has been
in place for thirty years. It records all data related to the lending of
books between our library authority and others. It also needs to record the
supply of photocopies where the book or serial is not available for loan. I
have sketched out the tables I "think" I need:

Borrowers (members of our authority)
Libraries (branch libraries in our authority)
Authorities (the contact details for interlibrary loan in other authorities)
Requests (the details of each request made (by us or to us)
Search (the progress that is being made in acquiring an item requested by us
from another authority - if one authority doesn't have it, we move to the
next one likely to have it)
Loan (details of each satisfied request - eg date due back, whether
extension allowed, renewals, etc)

I'm having trouble getting my head around how to set up the tables and
design the form to 'react' to data input. For example, I would like to be
able to select 'incoming request' or 'outgoing request' either by tick box or
pull-down list and have the choice affect how the form displays (only the
required input fields) - an incoming request does not require insertion of
borrower details. In the same way, an outgoing request for a loan doesn't
require that I fill in SerialName, Volume/Part, and an outgoing request for a
photocopy doesn't require that I fill in loan details (as photocopies become
the property of the borrower).

As well as recording the data, I need to be able to generate reports. I
think I have a handle on this, but if you have any advice.....

Really, I would like someone to do it for me, but as that isn't going to
happen, any advice, information, guidance or redirection would be very much
appreciated.

Many thanks in anticipation

Sarah

 

[mscertified]

The first thing to do is research if there is an existing database that you
can use. A lending library is a common thing and it must have been done
before. It will not be a trivial task to develop such a database from
scratch, especially for someone inexpert at Access. No-one can tell you how
to do it, but we can respond to technical questions about specific problems.

You have made a good start in defining the tables. Now you need to define
how the tables are related to each other and then what operations need to be
performed on the data. Forms can be handled in various ways, you can have the
fom dynamically change itself (not always recommended) or you can simply open
a different form for different functions.

Dorian

 

[Sarah]

Hi Dorian

Thanks for your positive feedback.

You are right, there are plenty of lending library programs around, we have
a Library Management System via contract, but enabling the inter-library
module would be cost prohibitive taking into account the small volume of
interlibrary loans handled, which is why we are still writing out little
cards fifteen years after installation of computerised lending.

The process that I want to computerise is relatively simple, I think it
could even work on a flat database, but that would be cumbersome and
inefficient:

1) Record requests received from other authorities: type of request, request
no, name and no of requesting authority, title details requested, dates
received, supplied, returned

2) Record requests originating in our authority: name and number of
borrower, pickup branch, type of request, title details, date sent,
forwarding details (authority no, date forwarded, method) for each occasion
request forwarded (usually no more than three times, date received, due back
date, renewal date, date returned

We are not interested in generating reminders, tracking borrower or loan
history, and we would not be inputting potential borrowers or potential
lenders - just entering data as requests come in (rarely would we have more
than 20 active loans at any one time).

I have tried to find something suitable online, but so far have been
woefully unsuccessful. And it has to be free.

I know I can "eventually" figure out how make the appearance of subforms
dependent on insertion of data in particular fields, but I don't have the
termininology to quickly and easily find the information I need in the Help
manuals.

Any pointers, either specific or towards a coaching website, would be very
much appreciated.

Thanks

 

[mscertified]

Your next step should be to go thru your list of objects (borrowers,
libraries, requests etc) and:
(1) List all the attributes of the Object e.g. Name, Address, Date due,
Status etc.
(2) Decide how the objects are related to each other e.g. Each request can
have one Search record and one Loan record, an authority can have multiple
requests etc.
(3) Decide what operations need to be performed on each Object e.g. add a
new borrower, delete a borrower, change a borrower's details, update a search
status

Normally each table should have an autonumber column called simply ID. To
relate one table to another table, the other table will have a column
referring to this. Lets say an authority has multiple requests. The Authority
table has a column called ID and the Request table has a column called
AuthID, so you will have multiple records in the Request table each pointing
to the same AuthID.

If you have no expertise in Access, you will have to buy a book on Access,
check out Amazon.com book reviews or find a willing college intern or
something.

You don't have to use subforms. If you have all your requests in one table
with a column identifying which type of request, then you can simply have a
form listing them all. When the user selects the request and presses a
button, you look at the type column and open a different form for each
different type of request.

Dorian


list what operations need to be performed involving them e.g.
Borrowers: Add borrower, Change a borrowers details, List borrowers
Requests:

 

[Sarah]

Hello Dorian

Thanks for the info - the suggestion about forms opening on a button press
is something I should have already thought of, and I welcome the 'nudge in
the ribs'.

I have more than enough to be going on with now, but no doubt I will be back
in the not too distant future.

Thanks again