library inventory control

[jlydon]

I have a library in a small school which needs to control its
inventory. I have three tables, one with the book information, one
with Student information and one with Book-Status. In the book Status
there is a form which is used when a book is taken out or returned.
We scan in the student ID number, then scan in the book barcode
(unique to each book). On the screen appears the student name and the
book name. There is then a place to register the date for taking out
the book. This information is then entered into the table of the Book-
status. However, when the book is returned and the Student ID and
Book barcode are re-entered the return date is recorded and this forms
a new line in the table, rather than update the previous entry. So
when I run a query to see which books are still out or overdue, it
lists all the books ever taken out, because the return-date
information is not on the same row as the out-date information, rather
it is in a new row.
Is there some way to set the form so that the original row of
information updated with the return-date when the book is returned, so
that when I run a query I will see only which books have not been
returned. Thanks.
This is the structure I have set up:

Books:
barcode - primary key
author
title
etc.

Students:
student_id- primary key
first_name
last_name
etc.

Book-status:
barcode (related to books.barcode)
student_id (related to students.student_id)
date_out - (date that the book was signed out)
date_in - (date that the book was returned)

 

[Mr. B]

I have a library in a small school which needs to control its
inventory. I have three tables, one with the book information, one
with Student information and one with Book-Status. In the book Status
there is a form which is used when a book is taken out or returned.
We scan in the student ID number, then scan in the book barcode
(unique to each book). On the screen appears the student name and the
book name. There is then a place to register the date for taking out
the book. This information is then entered into the table of the Book-
status. However, when the book is returned and the Student ID and
Book barcode are re-entered the return date is recorded and this forms
a new line in the table, rather than update the previous entry. So
when I run a query to see which books are still out or overdue, it
lists all the books ever taken out, because the return-date
information is not on the same row as the out-date information, rather
it is in a new row.
Is there some way to set the form so that the original row of
information updated with the return-date when the book is returned, so
that when I run a query I will see only which books have not been
returned. Thanks.
This is the structure I have set up:

Books:
barcode - primary key
author
title
etc.

Students:
student_id- primary key
first_name
last_name
etc.

Book-status:
barcode (related to books.barcode)
student_id (related to students.student_id)
date_out - (date that the book was signed out)
date_in - (date that the book was returned)

It sounds like you may need to redesign what you code is doing.

You state that when the book is returned, a new record is being
created. When a book and a student ID is scanned, you should have
your code first check to see if there is an existing record for that
book and that student. Then if it finds that record, then it would
simply update that record to record the current date as the return
date.

HTH

Mr B

 

[jlydon]

It sounds like you may need to redesign what you code is doing.

You state that when the book is returned, a new record is being
created. When a book and a student ID is scanned, you should have
your code first check to see if there is an existing record for that
book and that student. Then if it finds that record, then it would
simply update that record to record the current date as the return
date.

HTH

Mr B

thanks for the help. Could you explain how I would do this?

 

[Mr. B]

thanks for the help. Could you explain how I would do this?- Hide quoted text -

- Show quoted text -

Well, I am assuming from your original post that you have some code
somewhere. You indicate that you are processing a scan of the book
barcod and the student's id from the student card and then creating
the new record. I as simply suggesting that you would modify the code
that is processing the scanned information and creating the new
record.

You indicate that you have a form (one) that is used for this. Does
this mean that you have this form opening to a new record each time
that you need to scan in information? Are you simply adding a new
record each time by simply opening this form to a new record
everytime?

If the above things are correct, then you will need to make some major
modifications. You may need to create an unbound form that would
receive the scanned info. Then at the point when the book and the
student info have been scanned, you would need code behind the form
that would do the evaluating that I described in my first response.
That code can not only evaluate any existing data, but it can also
present any existing record in your existing form where the record
could be marked as returned.

I'm afraid that I cannot give you more specific instructions but I
would need much more to work with.

HTH

Mr B

 

[Fred Boer]

Dear jlydon:

You appear to have 3 tables, one for book information, one for patron
information, and one to track circulation. The circulation table
(Book-Status) appears to be a "junction" table. I can't tell exactly how
your circulation process works based on what you've given. I expect that the
way that a book is taken out involves creating a new record in the
Book-Status table, listing the book, patron, and date/time. When the book is
returned, this specific record *should be* retrieved and the current
date/time should be placed in the "date-in" field. From what you've said,
though, it appears that you are entering the date-out, and the date-in in
separate records. That won't work properly.

Creating a library application in Access can be a challenge, depending on
the level of complexity you need. There are other sources for library
software that might do the job for you. There are professional quality
library programs available for very low cost ($50...), or even freeware.
Considering how much time it might take to create your own, you might be
better off using something pre-built.

A quick Google search reveals many dozens of vendors selling a wide variety
of library management software. For example, you might look at the
following:

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

Or, you can find shareware library systems as well, with a search at
www.tucows.com, or even a Google search for "free library software".

You might also be interested in a small library application which I have
created using Access, and which is available as freeware. It includes a
circulation system and an online catalogue. You can check it out here:

http://www3.sympatico.ca/lornarourke/fred.html

Good luck!
Fred Boer



To list over

 

[jlydon]

Dearjlydon:

You appear to have 3 tables, one for book information, one for patron
information, and one to track circulation. The circulation table
(Book-Status) appears to be a "junction" table. I can't tell exactly how
your circulation process works based on what you've given. I expect that the
way that a book is taken out involves creating a new record in the
Book-Status table, listing the book, patron, and date/time. When the book is
returned, this specific record *should be* retrieved and the current
date/time should be placed in the "date-in" field. From what you've said,
though, it appears that you are entering the date-out, and the date-in in
separate records. That won't work properly.

Creating a library application in Access can be a challenge, depending on
the level of complexity you need. There are other sources for library
software that might do the job for you. There are professional quality
library programs available for very low cost ($50...), or even freeware.
Considering how much time it might take to create your own, you might be
better off using something pre-built.

A quick Google search reveals many dozens of vendors selling a wide variety
of library management software. For example, you might look at the
following:

http://directory.google.com/Top/Reference/Libraries/Library_and_Infor...

Or, you can find shareware library systems as well, with a search atwww.tucows.com, or even a Google search for "free library software".

You might also be interested in a small library application which I have
created using Access, and which is available as freeware. It includes a
circulation system and an online catalogue. You can check it out here:

http://www3.sympatico.ca/lornarourke/fred.html

Good luck!
Fred Boer

To list over









- Show quoted text -

Many thanks for the guidance. I will look at your program. Since I
have entered into Access all the book information for over 6,000
titles and all the student information, I would hate to have to re-
enter it into another program. So I appreciate your offerig your own
Access program. Thanks. John