CPT 106 · Franklin College · Erich Prisner · 2002-2006

CPT105 Databases

CPT105 Access

Tables

Relationships

Expressions

Queries

Forms

Macros

SQL

Access Practice

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

• book table, containing information on books. Import the book table given as an Excel sheet. Insert one new field displaying the status "checked out" or not.
• author table, containing information on authors. Import the author table given as an Excel sheet.
• two tables on awards
• customer table, containing information on customers. Create this table on your own. 
• transaction table. Leave this table empty---it will be filled if our transactions start.

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

• One form where customers search for books. All fields should be locked---customers are not supposed to change data in the database. Searching is done by rightclicking on the fields and using the Filter option.
• A special form for searching only for books that are available.
• A special form searching only for "important" books. A book is important if the author won the Nobel Prize, or if the book won the National Book Award. 
• One form used by the librarian to add, edit, or delete books and for checking out or checking in books.

 

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

• Now every non-student can check out up to 3 books at a time, and every student can check out up to 5 books at a time. Change the transaction macros.
• Insert a Librarian table with some information there. Connect it to the other tables.
• Assign a password to every customer and also to every librarian. Change the start menu form in such a way that you have to give your name and password if you log in, and can only proceed if both match.
• Create a title search button---together with a field, where the customer would type in part of the title---in all book search tables. It works with a parameter query.
• Create an author search button, together with a field where the customer tapes in part of the name---in all book search tables.

 

 

---

Erich Prisner 2006