Using MS Access/Book Library Database


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
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,
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?

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,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




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


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

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

Brooklyn College BA

©2016 All rights reserved.