Microsoft Access Graded Project Advanced PC Applications : Microsoft Access Graded Project Lesson 2 Overview The purpose of this project is to create a home inventory database that can be referred to in case of damage, theft, or natural disaster. You’ll assume the role of homeowner, who over time has acquired many valuable assets. You would like to more easily manage purchase history, product serial and model numbers, warranties, and appraisal information. For this project, you must create an Access database with forms, queries, and reports that you’ll store off site in case of emergency. 2.1 Create a home inventory database that can be referred to in case of damage, theft, or natural disaster Graded Project: Microsoft Access READING ASSIGNMENT Advanced PC Applications (v2) : Lesson 2 : Page 1© 2020 Penn Foster Inc.

Your project must be submitted as a Word document (.docx, .doc)*. Your project will be individually graded by your instructor and therefore will take up to a few weeks to grade. Be sure that each of your files contains the following information: Your name Your student ID number The lesson number (584046) Your email address Note: If you have more than 10 attachments, you’ll need to WinZip all of the project’s associated files along with all documentation using the WinZip software program. To submit your graded project, follow these steps: Go to ( . Log in to your student portal.

Click on Take Exam next to the lesson you’re working on. Follow the instructions provided to complete your exam. Be sure to keep a backup copy of any files you submit to the school! Instructions Create a Database 1. Start Access and create a Blank database, naming it “Inventory.†2.

On the Home tab, select View and then go to Design. 3. When prompted, name the new table "Inventory." 4. In Design view, change the field name 'ID' to 'Item ID' and verify that it’s data type AutoNumber and has been selected as the primary key. 5.

In Design view, add the remaining fields and corresponding data types: Advanced PC Applications (v2) : Lesson 2 : Page 2© 2020 Penn Foster Inc. Field Name Data Type Description Item Name Short Text Category Short Text Appliances, Electronics, Jewelry, Other Manufacturer Short Text Model Short Text Serial Number Short Text Purchase Date Date/Time (Short Date) Purchase Price Currency Merchant ID Number Online Purchase Yes/No Credit Card Purchase Yes/No Warranty Type Short Text Store, Manufacturer, Other Warranty Length Short Text Repair Yes/No Repair Date Date/Time (Short Date) Comments Short Text 6. Save and then close the Inventory table. 7. Create a second table in Design view.

Name the table “Merchants.†8. Add the following fields and corresponding data types. Be sure Merchant ID is the primary key: Advanced PC Applications (v2) : Lesson 2 : Page 3© 2020 Penn Foster Inc. Field Name Data Type Description Merchant ID AutoNumber Merchant Name Short Text Address Short Text City Short Text State Short Text Zip Short Text Email Hyperlink Website Hyperlink Telephone Short Text (Phone number input mask). Fax Short Text 9.

Save and then close the Merchants table. 10. Create a relationship by linking the Merchant ID in the Inventory table to the Merchant ID in the Merchants table. Be sure to enforce referential integrity. Create Forms and Populate the Database 1.

Create a Merchants form. Advanced PC Applications (v2) : Lesson 2 : Page 4© 2020 Penn Foster Inc. 2. Create an Inventory form, with the purchase price as

Microsoft Access Graded Projectadvanced Pc Applications Microsofta

Microsoft Access Graded Project Advanced PC Applications : Microsoft Access Graded Project Lesson 2 Overview The purpose of this project is to create a home inventory database that can be referred to in case of damage, theft, or natural disaster. You’ll assume the role of homeowner, who over time has acquired many valuable assets. You would like to more easily manage purchase history, product serial and model numbers, warranties, and appraisal information. For this project, you must create an Access database with forms, queries, and reports that you’ll store off site in case of emergency. 2.1 Create a home inventory database that can be referred to in case of damage, theft, or natural disaster Graded Project: Microsoft Access READING ASSIGNMENT Advanced PC Applications (v2) : Lesson 2 : Page 1© 2020 Penn Foster Inc.

Your project must be submitted as a Word document (.docx, .doc)*. Your project will be individually graded by your instructor and therefore will take up to a few weeks to grade. Be sure that each of your files contains the following information: Your name Your student ID number The lesson number (584046) Your email address Note: If you have more than 10 attachments, you’ll need to WinZip all of the project’s associated files along with all documentation using the WinZip software program. To submit your graded project, follow these steps: Go to ( . Log in to your student portal.

Click on Take Exam next to the lesson you’re working on. Follow the instructions provided to complete your exam. Be sure to keep a backup copy of any files you submit to the school! Instructions Create a Database 1. Start Access and create a Blank database, naming it “Inventory.†2.

On the Home tab, select View and then go to Design. 3. When prompted, name the new table "Inventory." 4. In Design view, change the field name 'ID' to 'Item ID' and verify that it’s data type AutoNumber and has been selected as the primary key. 5.

In Design view, add the remaining fields and corresponding data types: Advanced PC Applications (v2) : Lesson 2 : Page 2© 2020 Penn Foster Inc. Field Name Data Type Description Item Name Short Text Category Short Text Appliances, Electronics, Jewelry, Other Manufacturer Short Text Model Short Text Serial Number Short Text Purchase Date Date/Time (Short Date) Purchase Price Currency Merchant ID Number Online Purchase Yes/No Credit Card Purchase Yes/No Warranty Type Short Text Store, Manufacturer, Other Warranty Length Short Text Repair Yes/No Repair Date Date/Time (Short Date) Comments Short Text 6. Save and then close the Inventory table. 7. Create a second table in Design view.

Name the table “Merchants.†8. Add the following fields and corresponding data types. Be sure Merchant ID is the primary key: Advanced PC Applications (v2) : Lesson 2 : Page 3© 2020 Penn Foster Inc. Field Name Data Type Description Merchant ID AutoNumber Merchant Name Short Text Address Short Text City Short Text State Short Text Zip Short Text Email Hyperlink Website Hyperlink Telephone Short Text (Phone number input mask). Fax Short Text 9.

Save and then close the Merchants table. 10. Create a relationship by linking the Merchant ID in the Inventory table to the Merchant ID in the Merchants table. Be sure to enforce referential integrity. Create Forms and Populate the Database 1.

Create a Merchants form. Advanced PC Applications (v2) : Lesson 2 : Page 4© 2020 Penn Foster Inc. 2. Create an Inventory form, with the purchase price as $0.00. 3.

Use the Merchants form to populate the appropriate sections in the Merchants table with the records listed below. Merchant Records Advanced PC Applications (v2) : Lesson 2 : Page 5© 2020 Penn Foster Inc. Merchant ID 1 Merchant Name Electronics Mart Address 65 Resister Ave. City Blankston State PA Zip 18454 Email [email protected] Website Telephone ( Fax ( Merchant ID 2 Merchant Name Appliances Inc. Address 2020 Mechanics Road City Blankston State PA Zip 18454 Email n/a Website n/a Telephone ( Fax (none) Advanced PC Applications (v2) : Lesson 2 : Page 6© 2020 Penn Foster Inc.

Merchant ID 3 Merchant Name Stuff Mart Address 721 Frengburg St. City Shopville State NY Zip 10022 Email [email protected] Website Telephone ( Fax n/a Merchant ID 4 Merchant Name Phones and More Address 21 Framer Circle City Klossville State PA Zip 19019 Email [email protected] Website Telephone ( Fax n/a Advanced PC Applications (v2) : Lesson 2 : Page 7© 2020 Penn Foster Inc. Merchant ID 5 Merchant Name Jewelry Warehouse Address 24 Karat Street City Platoid State NY Zip 00050 Email [email protected] Website Telephone ( Fax ( Merchant ID 6 Merchant Name Crazy John’s Computers Address 456 Enterprise St. City Wynnsville State CO Zip 18888 Email [email protected] Website Telephone ( Fax n/a Advanced PC Applications (v2) : Lesson 2 : Page 8© 2020 Penn Foster Inc.

Merchant ID 7 Merchant Name Collector’s Emporium Address 256 Antiques Ave. City Oldensurg State PA Zip 18999 Email [email protected] Website Telephone ( Fax (. Use the Inventory form to populate the appropriate sections in the Inventory table with the records listed below. Inventory Records Item ID 1 Item Name GameBox Category Electronics Manufacturer Super Model GB928 Serial Number R Purchase Date 2/1/2020 Advanced PC Applications (v2) : Lesson 2 : Page 9© 2020 Penn Foster Inc. Purchase Price $599.00 Merchant ID 1 Online Purchase No Credit Card Purchase Yes Warranty Type Manufacturer Warranty Length 2 years, parts only Repair No Repair Date (none) Comments packaged with one free game control Item ID 2 Item Name Smart TV Category Electronics Manufacturer Super Model 4200 Serial Number Purchase Date 2/20/2020 Purchase Price $499.00 Merchant ID 1 Online Purchase No Credit Card Purchase Yes Advanced PC Applications (v2) : Lesson 2 : Page 10© 2020 Penn Foster Inc.

Warranty Type Manufacturer Warranty Length 90 days, parts only Repair No Repair Date (none) Comments (none) Item ID 3 Item Name Laptop Category Electronics Manufacturer Deluxe Model SuperFast Serial Number Purchase Date 2/27/2020 Purchase Price $899.00 Merchant ID 3 Online Purchase No Credit Card Purchase Yes Warranty Type Manufacturer Warranty Length 1 year, parts and labor Repair Yes Repair Date 6/1/2020 Advanced PC Applications (v2) : Lesson 2 : Page 11© 2020 Penn Foster Inc. Comments keyboard replaced Item ID 4 Item Name Bluetooth Headset Category Electronics Manufacturer Wireless Model BT54910 Serial Number Purchase Date 2/22/2020 Purchase Price $99.00 Merchant ID 4 Online Purchase Yes Credit Card Purchase Yes Warranty Type Manufacturer Warranty Length 12 months, parts only Repair No Repair Date (none) Comments (none) Item ID 5 Item Name Ink Jet Printer Advanced PC Applications (v2) : Lesson 2 : Page 12© 2020 Penn Foster Inc.

Category Electronics Manufacturer Pro Model U750 Serial Number Purchase Date 1/15/2020 Purchase Price $49.00 Merchant ID 6 Online Purchase Yes Credit Card Purchase Yes Warranty Type Manufacturer Warranty Length 90 days, parts and tech support Repair No Repair Date (none) Comments (none) Item ID 6 Item Name Refrigerator Category Appliances Manufacturer Wonderlux Advanced PC Applications (v2) : Lesson 2 : Page 13© 2020 Penn Foster Inc. Model Ice Age 2000 Serial Number Purchase Date 4/5/2020 Purchase Price $999.00 Merchant ID 2 Online Purchase No Credit Card Purchase Yes Warranty Type Manufacturer/store Warranty Length 2 years, parts and labor/5 years, refrigeration parts Repair No Repair Date (none) Comments (none) Item ID 7 Item Name Washing Machine Category Appliances Manufacturer Wonderlux Model CL900 Serial Number Purchase Date 6/4/2020 Advanced PC Applications (v2) : Lesson 2 : Page 14© 2020 Penn Foster Inc.

Purchase Price $625.00 Merchant ID 2 Online Purchase No Credit Card Purchase Yes Warranty Type Manufacturer Warranty Length 1 year, parts and labor Repair No Repair Date (none) Comments (none) Item ID 8 Item Name Clothes Dryer Category Appliances Manufacturer Wonderlux Model DR199 Serial Number Purchase Date 6/4/2017 Purchase Price $700.00 Merchant ID 2 Online Purchase No Advanced PC Applications (v2) : Lesson 2 : Page 15© 2020 Penn Foster Inc. Credit Card Purchase Yes Warranty Type Manufacturer Warranty Length 1 year, parts and labor Repair No Repair Date (none) Comments (none) Item ID 9 Item Name Dishwasher Category Appliances Manufacturer Washomatic Model DW19 Serial Number Purchase Date 8/12/2020 Purchase Price $475.00 Merchant ID 3 Online Purchase No Credit Card Purchase Yes Warranty Type Manufacturer Warranty Length 1 year, parts and labor Repair No Advanced PC Applications (v2) : Lesson 2 : Page 16© 2020 Penn Foster Inc.

Repair Date (none) Comments (none) Item ID 10 Item Name Smart Phone Category Electronics Manufacturer EasyPhone Model 17S Serial Number 567-39QR4512 Purchase Date 1/30/2020 Purchase Price $799.00 Merchant ID 4 Online Purchase No Credit Card Purchase Yes Warranty Type Manufacturer Warranty Length 1 year, parts only Repair No Repair Date (none) Comments (none) Item ID 11 Advanced PC Applications (v2) : Lesson 2 : Page 17© 2020 Penn Foster Inc. Item Name Heart Pendant on Gold Chain Category Jewelry Manufacturer GoldPlus Model n/a Serial Number n/a Purchase Date 2/11/2020 Purchase Price $599.00 Merchant ID 5 Online Purchase No Credit Card Purchase Yes Warranty Type n/a Warranty Length n/a Repair No Repair Date (none) Comments 24 karat gold, 18" serpentine link chain Item ID 12 Item Name Engagement Ring Category Jewelry Manufacturer Jewelserv Model n/a Advanced PC Applications (v2) : Lesson 2 : Page 18© 2020 Penn Foster Inc.

Serial Number n/a Purchase Date 2/12/2020 Purchase Price $2,500.00 Merchant ID 5 Online Purchase No Credit Card Purchase Yes Warranty Type n/a Warranty Length n/a Repair No Repair Date (none) Comments Appraisal in safety deposit box Item ID 13 Item Name Superhero #1 Comic Book Category Collectibles Manufacturer Funny Publishing May 1976 (publisher) Model n/a Serial Number n/a Purchase Date 5/25/2020 Purchase Price $39.00 Merchant ID 7 Advanced PC Applications (v2) : Lesson 2 : Page 19© 2020 Penn Foster Inc. Online Purchase Yes Credit Card Purchase Yes Warranty Type n/a Warranty Length n/a Repair No Repair Date (none) Comments 9.4 Comic Book Grading Item ID 14 Item Name Superhero #6 Comic Book Category Collectibles Manufacturer Funny Publishing November 1976 (publisher) Model n/a Serial Number n/a Purchase Date 5/25/2020 Purchase Price $67.00 Merchant ID 7 Online Purchase Yes Credit Card Purchase Yes Warranty Type n/a Warranty Length n/a Advanced PC Applications (v2) : Lesson 2 : Page 20© 2020 Penn Foster Inc.

Repair No Repair Date (none) Comments 9.2 Comic Book Grading Query the Database 1. Create a select query that retrieves the Item Name, Purchase Date, Purchase Price, and Online Purchase fields from the Inventory table for items purchased online. a. Have the select query sort the results in chronological order by purchase date. b. Be sure to format the Datasheet view so that all field names and data are displayed entirely. c. Save the query, naming it “Online Purchases,†and then close the query.

2. Create a select query that retrieves the Merchant Name from the Merchants table and the Item Name, Category, Manufacturer, and Purchase Date from the Inventory table for appliances. a. Have the select query sort the results in alphabetical order by merchant name. b. Be sure to format the Datasheet view so that all field names and data are displayed entirely. c. Save the query, naming it “Appliance Purchases,†and then close the query.

Create Reports Advanced PC Applications (v2) : Lesson 2 : Page 21© 2020 Penn Foster Inc. 1. Create a tabular report using the Online Purchases query. a. Sort the report data by purchase date. b. Total the items by Purchase Price. c.

Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one d. Save the report, naming it “Online Purchases,†and then close the report. 1. Create a tabular report using the Appliance Purchases query. a. Reduce field widths so that all fields are displayed in portrait orientation. b.

Move the Page 1 of 1 footer so that it’s centered below the report data. c. Delete the record count and the summary line below the Merchant Name data. d. Delete the date and time in the report's header (if necessary). e. Group the report data by merchant name. Advanced PC Applications (v2) : Lesson 2 : Page 22© 2020 Penn Foster Inc. f.

Sort the report data by purchase date. g. Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page. h. Save the report, naming it “Appliance Purchases,†and then close the report. Scoring Guidelines Rubric SKILL/GRADING CRITERIA EXEMPLARY (4) PROFICIENT (3) FAIR (2) POOR (1) NOT EVIDENT (0) Create tables Tables with correct field names and types have been created. Tables with mostly correct field names and types have been created.

Tables with some correct field names and types have been created. Tables without correct field names and/or types have been created. No attempt has been made to create tables. Advanced PC Applications (v2) : Lesson 2 : Page 23© 2020 Penn Foster Inc. Create forms Forms corresponding to tables have been created.

N/A N/A An attempt has been made to create forms, but they don’t correspond to the tables. No attempt has been made to create forms. Perform data entry All the designated records have been entered with minimal errors. Most of the designated records have been entered with minimal errors. Some of the designated records have been entered with minimal errors.

Few of the designated records have been entered with minimal errors. No attempt has been made to perform data entry. Create a select query A select query with the designated fields and correct criteria has been created. A select query with correct criteria and some of the designated fields has been created. A select query with correct criteria and a few of the designated fields has been created.

A select query that doesn’t contain the correct criteria has been created. No attempt has been made to create a select query. Sort select query results A select query with the correct sort for the designated field has been created. A select query with the correct sort for the wrong field has been created. A select query with the wrong sort has been created.

A filter has been applied to query results to provide a sort. No attempt has been made to sort query results. Format Datasheet view All the field names and field data are completely displayed in select query Datasheet view. Some of the field names and field data are completely displayed in select query Datasheet view. Few of the field names and field data are completely displayed in select query Datasheet view.

An attempt has been made to format Datasheet view. No attempt to format Datasheet view has been made. Create a report A formatted report with grouping and sorting has been created. A report with grouping and sorting that’s missing formats has been created. A formatted report with missing grouping and sorting has been created.

Minimal effort has been made to group, sort, and format a report. No attempt to create a report has been made. Advanced PC Applications (v2) : Lesson 2 : Page 24© 2020 Penn Foster Inc. Submission Checklist Before submitting your project, make sure you’ve correctly completed the following steps: Create, save, and name an Access database. Create tables with appropriate field names and corresponding data types.

Create formatted forms that correspond to tables. Use forms to populate a database with records. Create a select query using fields from one table. Create a select query using fields from multiple related tables. Designate query criteria for select query results.

Designate a sort order for select query results. Format select query Datasheet view to completely display field names and field data. Create a report. Sort and group a report. Edit a report format. Advanced PC Applications (v2) : Lesson 2 : Page 25© 2020 Penn Foster Inc.