The purpose of this assignment is to demonstrate skills developed using Access. In this project, you are to create a database as described below.
The name of the database should be AccessExam_YourName
You are the owner of a small coffee shop and your clientele has grown due to the overwhelming signature coffee the shop serves. Up until now, you have been using an Excel file to keep track of your vendors and products. However, you have completed your computer class and learned how to use MS Access. It is now time to put the skills to the test and create a more sophisticated tracking system. Your database will contain information on each supply item and the vendor contact information that sells the item. When you are finished, your .accdb file must contain the following:
1. Create a table by importing the data from the Suppliers Excel file and name it Suppliers. The fields in the table should be created as shown in the structure table below:
| Field | Data Type | Field Size | Primary Key | Description |
| Vendor ID | Text | 8 | Yes | Vendor ID |
| Vendor Name | Text | 25 |
| Company Name of Supplier |
| Contact Name | Text | 20 |
| Supplier Contact Name |
| Business Phone | Text | 14 |
| Business Phone |
| | Text | 50 |
| Email Address of the contact person |
2. Create another table by importing the data from the Products Excel file and name it Products. The fields in the table should be created as shown in the structure table below.
| Field | Data Type | Field Size | Primary Key | Description |
| Product Number | AutoNumber |
| Yes | Assigned as new record is entered. |
| Product Name | Text | 30 |
| Name of the product |
| Product Description | Long Text |
|
| Description of the product |
| Vendor ID | Lookup from Supplier table |
|
| Vendor ID |
| Last Purchase Date | Date/Time | Short Date |
| Date of last order |
| OnHand | Number | Long Integer |
|
|
| Cost | Currency | 2 decimals |
|
|
3. Create this frmSupplersProducts form:
4. Create following reports
Products – Suppliers List - sorted by Product Name)
Supplier/Products Report (sort products by ProductID) Highlight products that have not been purchased since 9/30/2009
5. Create a query to list the ProductName, OnHand, VendorName, ContactName, and BusinessPhone for all products that have anOnHand value less than 100.
6. Create a query to list all suppliers in the 243 area code.
7. Create a query to calculate the unit price and list the total cost of all products. (Unit price should be calculated as cost/onhand.) Sort by Unit Price, lowest to highest. Hide the OnHand field.
Extra Credit (10 pts.)
Create a query to list the ProductName, OnHand, LastPurchaseDate, VendorName, ContactName, and BusinessPhone for all products that have anOnHand value less than 100 or the product has not been purchased since 9/30/2009. Sort by LastPurchaseDate
Rubric:
| Correct Supplier table structure and data import | 15 |
| Correct Product table structures and data import | 15 |
| Form | 15 |
| Reports (10 pts @) | 20 |
| Product Reorder Query | 10 |
| Supplier Area Code Query | 10 |
| Product Unit Price Query | 15 |
| Total | 100 |
