Microsoft Access Graded Project

Lesson_2/Section_1/reading_assignment_core_1.html

Graded Project: Microsoft Access

Your project must be submitted as a Microsoft Access document (.accdb). 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  http://www.pennfoster.edu.
• 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:

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

1. Save and then close the Inventory table.
2. Create a second table in Design view. Name the table “Merchants.”
3. Add the following fields and corresponding data types. Be sure Merchant ID is the primary key:

Field Name	Data Type	Description
Merchant ID	Number
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

1. Save and then close the Merchants table.
2. 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.

1. Create an Inventory form, with the purchase price as $0.00.

1. Use the Merchants form to populate the appropriate sections in the Merchants table with the records listed below.

Merchant Records
Merchant ID	1
Merchant Name	Electronics Mart
Address	65 Resister Ave.
City	Blankston
State	PA
Zip	18454
Email	[email protected]
Website	www.emart.com
Telephone	(570) 555-1111
Fax	(570) 555-1112

Merchant ID	2
Merchant Name	Appliances Inc.
Address	2020 Mechanics Road
City	Blankston
State	PA
Zip	18454
Email	n/a
Website	n/a
Telephone	(570) 555-1234
Fax	(none)

Merchant ID	3
Merchant Name	Stuff Mart
Address	721 Frengburg St.
City	Shopville
State	NY
Zip	10022
Email	[email protected]
Website	www.stuffmart.com
Telephone	(212) 555-5432
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	www.phonesandmore.com
Telephone	(612) 555-9876
Fax	n/a

Merchant ID	5
Merchant Name	Jewelry Warehouse
Address	24 Karat Street
City	Platoid
State	NY
Zip	00050
Email	[email protected]
Website	www.jewelware.com
Telephone	(609) 555-3344
Fax	(609) 555-3345

Merchant ID	6
Merchant Name	Crazy John’s Computers
Address	456 Enterprise St.
City	Wynnsville
State	CO
Zip	18888
Email	[email protected]
Website	www.merchandizing.net/crazy
Telephone	(301) 555-9080
Fax	n/a

Merchant ID	7
Merchant Name	Collector’s Emporium
Address	256 Antiques Ave.
City	Oldensurg
State	PA
Zip	18999
Email	[email protected]
Website	www.collectibles.net
Telephone	(570) 555-1608
Fax	(570) 555-1609

1. 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	R729-382
Purchase Date	2/1/2020
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	1930456
Purchase Date	2/20/2020
Purchase Price	$499.00
Merchant ID	1
Online Purchase	No
Credit Card Purchase	Yes
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	879603-109-345
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
Comments	keyboard replaced

Item ID	4
Item Name	Bluetooth Headset
Category	Electronics
Manufacturer	Wireless
Model	BT54910
Serial Number	345-896-000
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
Category	Electronics
Manufacturer	Pro
Model	U750
Serial Number	555639870
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
Model	Ice Age 2000
Serial Number	2567590
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	90050221
Purchase Date	6/4/2020
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	199502211
Purchase Date	6/4/2020
Purchase Price	$700.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	9
Item Name	Dishwasher
Category	Appliances
Manufacturer	Washomatic
Model	DW19
Serial Number	195-763984
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
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
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
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
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
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.
   1. Have the select query sort the results in chronological order by purchase date.
   2. Be sure to format the Datasheet view so that all field names and data are displayed entirely.
   3. 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.
   1. Have the select query sort the results in alphabetical order by merchant name.
   2. Be sure to format the Datasheet view so that all field names and data are displayed entirely.
   3. Save the query, naming it “Appliance Purchases,” and then close the query.

Create Reports

1. Create a tabular report using the Online Purchases query.
   1. Sort the report data by purchase date.
   2. Total the items by Purchase Price.
   3. Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page.
1. Save the report, naming it “Online Purchases,” and then close the report.
1. Create a tabular report using the Appliance Purchases query.
   1. Reduce field widths so that all fields are displayed in portrait orientation.
   2. Move the Page 1 of 1 footer so that it’s centered below the report data.
   3. Delete the record count and the summary line below the Merchant Name data.
   4. Delete the date and time in the report's header (if necessary).
   5. Group the report data by merchant name.
   6. Sort the report data by purchase date.
   7. Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page.
1. 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)	Percentage of Overall Score
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.	24%
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.	8%
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.	20%
Create an Appliances select query	A select query with the designated fields and correct criteria has been created and properly sorted.	A select query with correct criteria and some of the designated fields has been created and properly sorted.	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.	16%
Create an Online Purchases select query	A select query with the designated fields and correct criteria has been created and properly sorted.	A select query with correct criteria and some of the designated fields has been created and properly sorted.	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.	16%
Create an Appliances 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.	8%
Create an Online Purchases 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.	8%

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.