Part 1 - Get content for your database and update tables
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.
Save the database file with the name Steven.Khalifeh_Exam2 (your file should automatically be named)
Start Access and open theExam 2 database file you downloaded. If you see the yellow security warning then click the Enable Content button.
Open Table1 and verify that there are 68 records in the table. - Example
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.
Rename Table1 to tblProduct
Rename Table2 tblOrder
Close all tables
Part 2 - Import Data
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!
Update each table by setting appropriate field types, format, and sizes.
Set appropriate key fields. Every table except tblOrderDetail requires a key field.
Save any change to your tables.
Part 4 - Add Validation and Lookup Field
Add Validation to the Category field of tblProduct so that only the follow entries are allowed: Beer Candy Drinks Food Other Snack
Add the following validation text: Must be one of the following: Beer, Candy, Drinks, Food, Other, Snack.
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
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.
Include all 6 tables in the Relationship view.
Add appropriate relationships between the tables.
Make sure that Enforce Referential Integrity and Cascade Update Related Fields is set for each relationship.
Adjust the relationship layout so relationship lines are not hidden or go behing any of the tables and do not cross other relationship lines.
Save the changes to the relationships
Part 6 - Create a Filter
Create a filter in tblProduct that lists only the ItemCost is 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.
Toggle the filter on to verify that it works.
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.
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.
Sort the query design by Inventory in descending order.
Save the query
Create a new query named qryOtherItemsFromCA using data from tblProduct and tblSupplierthat displays only the SupplierName and Item fields in the results for all items in the Other category for suppliers in California.
Sort the query design by Item in Ascending order.
Adjust the width of each column as needed to properly display the results in the datasheet view.