Video Shop/Library
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.
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.
Tables
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.
Queries
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.
Forms
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).
Fruit Machine
Access isn't only there for the nasty things in life, so I've included a more frivolous example. While it's not really suitable for an examination course project, it uses some useful techniques that could be adapted to a more serious application, and includes some proper VBA with loops and variables (for more information about these things, see the Programming section. It doesn't behave exactly like a real fruit machine, but could be tailored to do so.
The system isn't really a database, although it does contain a table, which is used to store the names of the pictures, and also the pictures themselves. Theses are then displayed on the subfrm_reel form, and inserted three times into the main form to provide the illustration.
Apart from some variable declarations and definitions, all the "work" is done with the OnClick event of the Play button. Each reel is assigned a random number from 0 to 4, and a string array is used to assign each value a name - e.g. cherry, bell, lemon, etc. In this way, you could have several numbers corresponding to the same image, altering the probability of a win. This process is done 15 times to simulate the spinning of the reels.
The code then checks how many of the images are the same - you then win 10p for two, and £1 for three. The amount is added to the bank, and the value updated.
Download a copy of the fruit machine in .mdb format or as an .accdb file.