• Upload 1 file: to the drop box using the Exam 2 link

THIS IS A TEST I understand that I can only ask the instructor for assistance completing the exam. I also understand that if I submit a file which is derived from the work of another person that I will not receive any credit for the assignment. You must submit the completed exam using the file you download for Exam 2 via the link below. If you do not submit your completed exam using the same file you download to start this exam then you will receive a score of 0. click the checkbox to agree and continue to the instructions

Part 1 – Get content for your database and update tables

1. Each student has a unique file for this assignment.
   By clicking Create My Data File the button below you agree to follow the guidelines for this assignment.
   Click once
   It takes about 15 seconds to generate your file.
2. Save the database file with the nameAiham.Asmroo_Exam2 (your file should automatically be named)
3. Start Access and open the Exam 2 database file you downloaded.
   If you see the yellow security warning then click the Enable Content button.
4. Open Table1 and verify that there are 68 records in the table. – Example
5. Open Table2 and verify that there are 100 records in the table. – Example
   If you don’t have the number of records listed above contact your instructor before proceeding.
6. Rename Table1 to tblProduct
7. Rename Table2 tblOrder
8. Close all tables

Part 2 – Import Data

1. Import the text file as a table named tblOrderDetail.
   Note 1: The text file is a comma delimited data file where the first row contains field names.
   Note 2: No key field or index required for this table! This is a junction table.
   (A junction table maps two or more tables together by referencing the primary keys of each data table.) Junction table example
2. Import the spreadsheet file as a table named tblSupplier
   Note 1: SupplierID should be set as the primary key
   See example of tables excluding your employee table/s
3. Review the field types and set them as appropriate for the data size and type.
4. In tblSupplier change the name of record 1 contact A. Schmitt to A. Asmroo
5. Import 2 tables (tblEmployees and tblJobs) which you created in your Aiham.Asmroo_Exam1 Exam 1 database.

Part 3 – Update Table Design

Modify table design following best database design practices.
You can refer to your text starting on page A2-104
You can also review Microsoft Database Design and my Database Design information pages.

1. Update each table by setting appropriate field types, format, and sizes.
2. Set appropriate key fields. Every table except tblOrderDetail requires a key field.
3. Save any change to your tables.

Part 4 – Add Validation and Lookup Field

1. Add Validation to the Category field of tblProduct so that only the follow entries are allowed:
   Beer Candy Drinks Food Other Snack
2. Add the following validation text:
   Must be one of the following: Beer, Candy, Drinks, Food, Other, Snack.
3. Modify the Category field to be a Lookup Field such that the following values can be selected from a list when you click in the Category field of any item:
   Beer, Candy, Drinks, Food, Other, Snack
4. Save the changes to the table.

Part 5 – Create Relationships

Now that all the tables have had their design optimized and key fields are set the relationships can be set. If you have trouble creating a relationship you may need to modify the table design.

Use the following to help create the relationships:

• Jobs are filled with employees
• employees take customer orders
• orders are made up of order details
• order details contain a list items
• items come from suppliers.

Note: In this design tblOrderDetails is a junction table. A junction table contains 2 FK’s to relate to the PK’s in other tables (tblOrder and tblProduct).

1. Include all 6 tables in the Relationship view.
2. Add appropriate relationships between the tables.
3. Make sure that Enforce Referential Integrity and Cascade Update Related Fields is set for each relationship.
4. Adjust the relationship layout so relationship lines are not hidden or go behing any of the tables and do not cross other relationship lines.
5. Save the changes to the relationships

Part 6 – Create a Filter

1. Create a filter in tblProduct that lists only the ItemCostis equal to OR below 1 Your filter must produce the correct results if the values are changed or new items are added to the table.
2. Toggle the filter on to verify that it works.
3. Toggle the filter off and close the table saving your changes with the filter off.

Part 7 – Create Queries

The manager wants to set up a discount section by the register with items which cost 99 cents.
He also want a list of all the items from California which are from the Other category.

1. Create a new query named qry99CentItems including items which have an ItemPrice of 99 cents including the following fields in this order: Item, ItemPrice, and Inventory.
2. Sort the query design by Inventory in descending order.
3. Save the query
4. Create a new query named qryOtherItemsFromCA using data from tblProduct and tblSupplier that displays only the SupplierName and Item fields in the results for all items in the Other category for suppliers in California.
5. Sort the query design by Item in Ascending order.
6. Adjust the width of each column as needed to properly display the results in the datasheet view.
7. Save the query. – Example

Part 8 – Create a Form

1. Create a form for tblProducts using the Form Wizard in the columnar format.
2. Adjust the width of the Category, ItemCost, and ItemPriceso they are the same width as SuplierID and Inventory. Do not adjust the width of the Item field.
3. Change the text of the form header of the form to Products
4. Save the form as frmProduct – Example

Part 9 – Create a Report

1. Create a report including the SupplierName from tblSupplier and Item, ItemCost, Inventory from tblProduct.
2. Select to view by tblSupplier
3. Sort by Item and select Stepped layout
4. Save the report as rptSupplierItems
5. Adjust the columns so all the column names and data are correctly displayed
6. Change the report title in the header to Supplier Items
7. Add a total in the SupplierID footer to list the number of items from each supplier and align it with the left edge of the Items
8. Add a caption Total Items for the sum
9. Change the font color for the Total Items caption and the calculated total
10. Change the text of the report header to Supplier Items – Example
11. Save the report

Part 10 – Finish and Submit

1. Set the database file option to compact on close. See the video how to do this.
2. Upload your Aiham.Asmroo_Exam2 file using the Exam 2 link.
   Caution! Files must be submitted to the class drop box to earn credit.