L i b r a r y  

Access Project: Mini-
CPT 106 · Franklin College · Erich Prisner · 2002-2006

This is a project in two phases. Each phase has a deadline and will get a grade. You will get feedback on your phase 1 project together with your grade, and you are supposed to fix those parts that were missing or not done properly for your phase 2 submission.

Phase 1

Build a simple database system for a small library. The tables are

Create forms. Some of these form are based on queries, so you have to create the queries as well.


Step by Step

  1. Download the three Excel files Books.xls, Authors.xls, NobelPrize.xls, NationalBookAward.xls.
  2. Create a new database in Access. Click on blank database.
  3. Import the four tables into the database
  4. "Clean" the tables. Adjust the file names, data types, and so on. Make sure no empty records and empty fields are in the table. 
  5. All four tables should also have a field "Full Name". The reason is that this field should serve as primary key in the authors table. We create four more tables, called Books1, Authors1, NobelPrize1, NationalBookAward1 containing all information plus one more calculated field for Full Name using a MakeTable Query.
  6. The table books1 should have the BookID (Number, or even better Autonumber) as primary key. The table authors1 should have the "Full Name" as primary key. Now you can link the four tables.
  7. However, referential integrity cannot be enforced. There is this problem with double authors. Advanced students, please try to fix this problem, the others do without.
  8. Now you need more tables. Create a table "Customers" and put some customers in. Use the fields "first name", "last name", "Address", "City", "Telephone number" (text type), "Birthday" (a date), "Student" (a yes/no type), and "books checked out" displaying the number of books the user has currently checked out.
  9. Create a table "Transactions". It should contain the fields "TransactionID" (the primary key, Autonumber), "BookID", "CustomerID", CheckOutDate, DueDate, CheckInDate.
  10. Since you need to see whether a book is presently checked out, add a field into the "Books1" table called "CheckedOut" with datatype "Yes/No".
  11. Link the new tables to the old ones.
  12. ...
  13. For the librarian's book edit and check-out check-in form, create a form showing all books. Go to the design view and rightclick to make the toolbox visible. Add two buttons, the check-out and the check-in button. Add a Combo Box for entering the customer ID. The default value should be -1---in this way we can get a warning if the librarian tries to check out a book without typing in the customer ID.
  14. The two buttons above should run certain macros, the checkout and the check-in macro.
  15. The check-out macro should check whether the book is available and whether the librarian put the customer ID into the combo box. If one of them is not true, a message box should occur. If both conditions are true, it should run another macro, which contains the actual transaction, call it "doCheckOut".
  16. First we need a transaction form, based on the transaction table. The "doCheckOut" macro opens the transaction form and adds a new transaction (using the action "GoToRecord" with 3rd argument "New"). After that it changes some values of this form, as well as some values on the librarian book form (namely the satutus of the book changes from "available" to "checked-out". These changes are done by the macro action "SetValue". The argument "item" means the field that is being changed, like [Forms]![Transactions]![BookID], please use theexpression builder for clicking in these expressions. Close the transaction form after finishing the transaction, maybe send a message. 


Phase 2



Erich Prisner 2006