Advanced PC Applications Project: Microsoft Access 2016
READING ASSIGNMENT
Your project must be submitted as a Microsoft Access document (.accdb). The 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 (######00)
· Your email address
Note: If you have more than 10 attachments, youll need to WinZip all of the projects associated files along with all documentation using the WinZip software program.
Instructions
Create a Database
1. Start Access and create a Blank database, naming it Inventory.
2. On the Home tab, click View and then name the new table Inventory.
3. In Design view, change the field name ID to Item ID and verify that its data type AutoNumber and has been selected as the primary key.
4. 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
5. Save and then close the Inventory table.
6. Create a second table in Design view. Name the table Merchants.
7. Add the following fields and corresponding data types. Be sure Merchant ID is the primary key:
Field Name
Data Type
Description
Merchant ID
AutoNumber
Merchant Name
Short Text
Address
Short Text
City
Short Text
State
Short Text
Zip
Short Text
Hyperlink
Website
Hyperlink
Telephone
Short Text
Fax
Short Text
8. Save and then close the Merchants table.
9. 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 that looks similar to the figure below. An image of a Merchants form
2. Create an Inventory form that looks similar to the figure below. An image of an Inventory form
3. 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
emart@blanknet.com
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
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
custserv@stuffmart.com
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
callus@phonesandmore.com
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
info@jewelware.com
Website
www.jewelware.com
Telephone
(609) 555-3344
Fax
(609) 555-3345
Merchant ID
6
Merchant Name
Crazy Johns Computers
Address
456 Enterprise St.
City
Wynnsville
State
CO
Zip
18888
crazy@merchandizing.net
Website
www.merchandizing.net/crazy
Telephone
(301) 555-9080
Fax
n/a
Merchant ID
7
Merchant Name
Collectors Emporium
Address
256 Antiques Ave.
City
Oldensurg
State
PA
Zip
18999
findit@collectibles.net
Website
www.collectibles.net
Telephone
(570) 555-1608
Fax
(570) 555-1609
4. 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/2017
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/2017
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/2017
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/2017
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/2017
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/2017
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/2017
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/2017
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/2017
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/2017
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/2017
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/2017
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/2017
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
Super Hero #1 Comic Book
Category
Collectibles
Manufacturer
Funny Publishing May 1976 (publisher)
Model
n/a
Serial Number
n/a
Purchase Date
5/25/2017
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
Super Hero #6 Comic Book
Category
Collectibles
Manufacturer
Funny Publishing November 1976 (publisher)
Model
n/a
Serial Number
n/a
Purchase Date
5/25/2017
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.
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
1. Create a tabular report using the Online Purchases query.
a. Sort the report data by purchase date.
b. Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page, as shown in the figure.
An image of the print preview of the Online Purchases report
c. Save the report, naming it Online Purchases, and then close the report.
2. 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 its centered below the report data.
c. Delete the record count and the summary line below the Merchant Name data.
d. Group the report data by merchant name.
e. Sort the report data by purchase date.
f. Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page, as shown in the figure.
An image of the print preview for the Appliance Purchases report
g. 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.
Create forms
Forms corresponding to tables have been created.
N/A
N/A
An attempt has been made to create forms, but they dont 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 few of the designated fields has been created.
A select query that doesnt 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 thats 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.
Submission Checklist
Before submitting your project, make sure youve 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.
Applied Sciences
Architecture and Design
Biology
Business & Finance
Chemistry
Computer Science
Geography
Geology
Education
Engineering
English
Environmental science
Spanish
Government
History
Human Resource Management
Information Systems
Law
Literature
Mathematics
Nursing
Physics
Political Science
Psychology
Reading
Science
Social Science
Home
Blog
Archive
Tags
Essay
Reviews
Contact
google+twitterfacebook
Copyright © 2019 HomeworkMarket.com
