![]() 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. 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. 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. Again, a description for the book is also included. everything from tblAccession where qryBooksOut is null. 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. QryBooksIn therefore uses QryBooksOut as its starting point, and finds all of the books that are not in the list generated by that query. One thing you can always say about them, however, is that they are not out on loan. 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. Working out which books are still in the library is a bit more tricky. 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. The + symbol can be used to concatenate text, but the accession field is numeric, and can't be concatenated. The description is generated using the formula Trim(Str(!))+" - "+!+", "+!. 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. Books that are on loan will have entries in tblLoan, but will not have a return date because they have not been returned. ![]() These are used by the forms to populate combo-boxes with lists of books, and could also be used for stock-taking purposes. The first two queries are QryBooksIn and QryBooksOut. A real library would have facilities for searching for books by title, classification, key word, etc., and probably facilities for searching through lenders. 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. 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. ![]() The field can be either a simple Yes/No field, or you can store the date on which the book was returned. It can also be used to produce a list of the books that are in stock or out on loan. 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. Lots of people forget the returned field. 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. In tbl_loan, the DueBack field isn't strictly necessary, as it can always be calculated from the DateOut field and the loan period. In modern libraries, the accession number is usually marked on each book as a barcode, which can be scanned as the book is borrowed. With four tables, the library can stock multiple copies of a particular book, each with a unique accession number. 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. Notice that there are four tables, and not the three that you might expect from text-book examples. Here is the relationship structure for the system (you can download the database as an. I will describe a school library system, as the date processing is possibly slightly more complex. The systems are basically the same, of course, just with different fields to store the appropriate details for whatever is being lent. One of the most popular projects for school/college students of Access is a system for libraries or video-shops. There is a playlist called Microsoft Access Library Example on the Advanced ICT and Computing YouTube channel which shows how to implement some of the techniques required to create a library system in Access. Home Other Database Examples Video Shop/Library
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |