One of the most popular projects for school/college students of Access is a system for libraries or video-shops. The systems are basically the same, of course, just with different fields to store the appropriate details for whatever is being lent. I will describe a school library system, as the date processing is possibly slightly more complex.
Here is the relationship structure for the system (you can download the database as an .mdb or .accdb file):
Notice that there are four tables, and not the three that you might expect from text-book examples. If the loan table were linked directly to the book table, with ISBN as the key field, that would mean that the library could only stock one of each type of book. With four tables, the library can stock multiple copies of a particular book, each with a unique accession number. In modern libraries, the accession number is usually marked on each book as a barcode, which can be scanned as the book is borrowed.
In tbl_loan, the DueBack field isn't strictly necessary, as it can always be calculated from the DateOut field and the loan period. This library has the same loan period (one month) for all books, but in reality you could store the loan period in either tblAccession (if you had, say a copy of a book in the general lending section, and another copy in a reference or short-loan section), or tblBook.
Lots of people forget the returned field. Without it, it's impossible to tell whether a book is overdue or not, because you can't tell whether it was brought back, or whether the lender still has it. It can also be used to produce a list of the books that are in stock or out on loan. The field can be either a simple Yes/No field, or you can store the date on which the book was returned. The benefit of using the date is that you can use DateDiff() to calculate fines, and also see how many times lenders have been late in bringing books back.
In the system as it stands, there are just four queries - these are the four fundamental queries required for the library to operate and determine which books are overdue. A real library would have facilities for searching for books by title, classification, key word, etc., and probably facilities for searching through lenders.
The first two queries are QryBooksIn and QryBooksOut. These are used by the forms to populate combo-boxes with lists of books, and could also be used for stock-taking purposes.
QryBooksOut is quite simple. Books that are on loan will have entries in tblLoan, but will not have a return date because they have not been returned. The query (shown below) searches tblLoan for records where returned is null, but also derives a description for the book by concatenating the accession number, title and author.
The description is generated using the formula Trim(Str([tblLoan]![Accession]))+" - "+[tblBook]![Title]+", "+[tblBook]![Author]. The + symbol can be used to concatenate text, but the accession field is numeric, and can't be concatenated. The str() function turns the number into text, but then this also pads out the number with extra spaces, which are removed by the trim() function.
Working out which books are still in the library is a bit more tricky. Books that are in stock might never have been loaned, in which case there will be nothing in tblLoan, or they might have been loaned (possibly several times) but brought back. One thing you can always say about them, however, is that they are not out on loan.
QryBooksIn therefore uses QryBooksOut as its starting point, and finds all of the books that are not in the list generated by that query.
In query terms, we're looking for all of the accession numbers in tblAccession where there isn't a corresponding entry in qryBooksOut - i.e. everything from tblAccession where qryBooksOut is null. Again, a description for the book is also included.
The final query is an update query that updates the loan record in tblLoan for the book selected on frmReturn so that returned contains today's date. The book might have been loaned many times, and may have many records in tblLoan, but the only the current loan will have no returned date, which is why Null has also been included as a criterion for the Returned field.
There is another query in the system, called qryReturnDetails, but the purpose of this is just to combine all the details about a loan - book, lender name, etc - in one place, which can then be used as a data-source for a form.
There is nothing interesting to say about the forms, except that frmAccession and frmLoan have their Data Entry property set to Yes, so that the form is blank and ready to accept data when it is opened. frmReturn is unbound, as it doesn't save anything - it just collects the access number of the book being returned and passes it to the update query. After the button is clicked, the .Requery method is run to update the list of books out on loan.
Download a copy of the library database in Access format (178k).