Using MS Access/Book Library Database

Advertisement


Question
QUESTION: I'm making a database for a book library.
I have a book table, member table, staff table and a transactions table.
In the book table i have a field called total copies. This is how many copies of each book is available in the library.
I cant figure out how to make a query to show the number of books available when someone borrows a book. When someone borrows a certain book i want the total copies to be subtracted by 1 and if more than one of the same book is borrowed it should show how many copies are currently available.
How can I do this?

Thank you.

ANSWER: Not the best approach to this. Use your transactions table and calculate the number.

In your transactions table should be a field for returned date. So a query like this should work:

SELECT BookID, Count(BookID) As Outstanding
FROM Transactions
GROUP BY BookID
WHERE ReturnedDate is Null;

Call this qryOutstanding. This will give you a count of all books out on loan.

Then create another query like this:

SELECT BookID, BookTitle, TotalCopies, Outstanding, TotalCopies-Outstanding AS BooksOnHand
INNER JOIN Books.BookID = qryOutstanding.BookID
FROM Books, qryOutstanding
ORDER By BookTitle;

Since this is a calculated value it should be calculated on the fly, not stored.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: I have done that and it works perfectly.
How would I show the availavble copies on a form though?
In the books lending form I would like it to show this.
I made the record source of the form as a query which shows all the fields in the transaction table and the available copies field. When I do this it doesn't allow me to add new records. I'm guessing that it is because it is a calculated value?
I am pretty new to access so I don't know what to do. How can I show the field on the form?

Answer
Make the Recordsource the table and have a textbox for available. Make the Controlsource a DLookup. Like:

=DLookup("[Available]","qryAvailable","[BookID] = "& Me.BookID)

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.