GO16_AC_VOL1_GRADER_CAP_AS - InventorySupplies Project Description:
In this project, you will apply skills you practiced from theObjectives in Access Chapters 1 through 3. You will create adatabase for Frank Osei, Vice President of Finance, that containsinventory and supplier information. In addition, you will createqueries that answer specific questions relating to the inventoryitems and suppliers, forms for entering and updating information, and reports.
Steps to Perform:
Step
Instructions
Points Possible
1 Start Access. Download, open, and save the database namedgo_acc_grader_capstone_Invento ry_Suppliers. accdb.0
2 Open the Cap1 Inventory table in Design View. Rename the IDfield to Item ID and change the Data Type to ShortText. Continue adding
the following fields as Short Text except forRetail Price, which should have a Currency Data Type:
Item Name
Retail Price
Category
Sport
Supplier ID 7
3 Switch to Datasheet view. Enter the following two records intoCap1 Inventory:
Item ID: 106555
Item Name: Snowboard
Retail Price: 256.99
Category: Equipment
Sport: Skiing
Supplier ID: SUPP-06
Item ID: 106556
Item Name: Wool Socks
Retail Price: 12.59
Category: Socks and Gloves
Sport: Skiing
Supplier ID: SUPP-04 5
4 Close the table. Append a copy of the records from thedownloaded aCap1_Inventory workbook to the Cap1 Inventorytable. Accept all defaults in the wizard and do not save the Importsteps. Open the table, verify there are 25 records, and apply BestFit to all fields. Save and close the table. 4
5 Import the source data from the downloadedaCap1_Suppliers workbook into a new table in the currentdatabase. In the wizard, click the First Row Contains ColumnHeadings option and set the primary key to Supplier ID. Name thetable Cap1 Suppliers. 6
6 Open the Cap1 Suppliers table in Design view. Delete the FaxNumber field and save the table. Switch to Datasheet view and applyBest Fit to all of the fields. Save and close the table. 4
7 On the Navigation Pane, group the objects by Tables and RelatedViews. 2
8 Create a relationship between the two tables in the databaseusing Supplier ID as the common field. Enforce ReferentialIntegrity and
select both Cascade options. One supplier can supplymany inventory items. 6
9 Create a relationship report. Save the report withRelationships as the name. Close the Relationshipswindow. 4
10 Create a query in Design view, using your Cap1 Inventory tableto answer the question What is the item name (in alphabeticalorder), retail price, and category for the sport of hiking?Add the fields in that order. Do not display the Sport field in thequery results. Run the query, save it as Cap1 HikingQuery, then close the query. Seven records match thecriteria.6
11 Create a copy of Cap1 Hiking Query named Cap1 Hiking andBiking Shoes Query. Redesign the query design to answerthe question What is the sport, item name, retail price, andsupplier ID for the category of shoes where the sport is hiking orbiking? Arrange the fields in the order in which they'rementioned in the question. Sort the records first in ascendingorder by Sport and then in ascending order by Item Name. Do notdisplay the Category field in the query results, but display theSport field. Run and then close the query. Six records match thecriteria. 8
12 Create a query in Design view, using both tables to answer thequestion What is the category, item name, company, and phonenumber for a category that begins with the letter S for sports witha criteria of fitness? Add the fields in that order. Sort therecords first in ascending order by the Category field and then inascending order by the Item Name field. Do not display the Sport inthe query results. Run the query, save it as Cap1 Fitness SCategory Query, and then close it. Four records match thecriteria.