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
No
te:
If you have more than
1
0 attachments, you’ll need to Win
Zip
all of the project’s 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 it’s data type Auto
Number
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 |
Appliances , Electronics , Jewelry , Other |
|||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Manufacturer |
||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Model |
||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Serial Number |
||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Purchase Date |
Date/Time (Short Date) |
|||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Purchase Price |
Currency |
|||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Merchant ID |
Number | |||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Online Purchase |
Yes /No |
|||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Credit Card Purchase |
||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Warranty Type |
Store, Manufacturer, Other |
|||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Warranty Length |
||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Repair |
||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Repair Date |
||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Comments |
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:
|
AutoNumber |
|||||||||
|
Merchant Name |
|||||||||
|
Address |
|||||||||
|
City |
|||||||||
|
State |
|||||||||
| Zip | |||||||||
|
|
Hyperlink |
||||||||
|
Website |
|||||||||
|
Telephone |
|||||||||
|
Fax |
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.
2. Create an Inventory form that looks similar to the figure below.
3. Use the Merchants form to populate the appropriate sections in the Merchants table with the records listed below.
|
Merchant Records |
|||
| 1 | |||
|
Electronics Mart |
|||
|
65 Resister Ave. |
|||
|
Blankston |
|||
|
PA |
|||
|
18454 |
|||
|
emart@blanknet.com |
|||
|
www.emart.com |
|||
|
(570) 555- 11 11 |
|||
|
(570) 555-11 12 |
| 2 | |||||||||||||||||||||
|
Appliances Inc. |
|||||||||||||||||||||
|
2020 Mechanics Road |
|||||||||||||||||||||
|
n/a |
|||||||||||||||||||||
|
(570) 555-1234 |
|||||||||||||||||||||
|
(none) |
| 3 | |||
|
Stuff Mart |
|||
|
721 Frengburg St. |
|||
|
Shopville |
|||
|
NY |
|||
|
10 022 |
|||
|
custserv@stuffmart.com |
|||
|
www.stuffmart.com |
|||
|
(212) 555-5432 |
|||
| 4 | |||
|
Phones and More |
|||
|
21 Framer Circle |
|||
|
Klossville |
|||
|
19019 |
|||
|
callus@phonesandmore.com |
|||
|
www.phonesandmore.com |
|||
|
(612) 555-9876 |
| 5 | |||
|
Jewelry Warehouse |
|||
|
24 Karat Street |
|||
|
Platoid |
|||
|
00050 |
|||
|
info@jewelware.com |
|||
|
www.jewelware.com |
|||
|
(609) 555-3344 |
|||
|
(609) 555-3345 |
| 6 | ||
|
Crazy John’s Computers |
||
|
456 Enterprise St. |
||
|
Wynnsville |
||
|
CO |
||
|
18888 |
||
|
crazy@merchandizing.net |
||
|
www.merchandizing.net/crazy |
||
|
(301) 555-9080 |
| 7 | |||
|
Collector’s Emporium |
|||
|
256 Antiques Ave. |
|||
|
Oldensurg |
|||
|
18999 |
|||
|
findit@collectibles.net |
|||
|
www.collectibles.net |
|||
|
(570) 555-1608 |
|||
|
(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 | ||||||||||||||||||||||
|
GameBox |
||||||||||||||||||||||
| Electronics | ||||||||||||||||||||||
|
Super |
||||||||||||||||||||||
|
GB928 |
||||||||||||||||||||||
|
R729-382 |
||||||||||||||||||||||
|
2/1/2017 |
||||||||||||||||||||||
|
$599.00 |
||||||||||||||||||||||
| No | ||||||||||||||||||||||
| Yes | ||||||||||||||||||||||
|
2 years, parts only |
||||||||||||||||||||||
|
packaged with one free game control |
|
Smart TV |
|
4200 |
|
1930456 |
|
2/20/2017 |
|
$499.00 |
|
90 days, parts only |
|
Laptop |
|||
|
Deluxe |
|||
|
SuperFast |
|||
|
879603-109-345 |
|||
|
2/27/2017 |
|||
|
$899.00 |
|||
|
1 year, parts and labor |
|||
|
6/1/2017 |
|||
|
keyboard replaced |
|
Bluetooth Headset |
|
Wireless |
|
BT54910 |
|
345-896-000 |
|
2/22/2017 |
|
$99.00 |
|
12 months, parts only |
|
Ink Jet Printer |
|
Pro |
|
U750 |
|
555639870 |
|
1/15/2017 |
|
$49.00 |
|
90 days, parts and tech support |
|
Refrigerator |
|||
| Appliances | |||
|
Wonderlux |
|||
|
Ice Age 2000 |
|||
|
2567590 |
|||
|
4/5/2017 |
|||
|
$999.00 |
|||
|
Manufacturer/store |
|||
|
2 years, parts and labor/5 years, refrigeration parts |
|
Washing Machine |
|
|
CL900 |
|
|
90050221 |
|
|
6/4/2017 |
|
|
$625.00 |
| 8 |
|
Clothes Dryer |
|
DR199 |
|
199502211 |
|
$700.00 |
| 9 |
|
Dishwasher |
|
Washomatic |
|
DW19 |
|
195-763984 |
|
8/12/2017 |
|
$475.00 |
| 10 |
|
Smart Phone |
|
EasyPhone |
|
17S |
|
567-39QR4512 |
|
1/30/2017 |
|
$799.00 |
|
1 year, parts only |
| 11 | |
|
Heart Pendant on Gold Chain |
|
| Jewelry | |
|
GoldPlus |
|
|
2/11/2017 |
|
|
24 karat gold, 18″ serpentine link chain |
| 12 |
|
Engagement Ring |
|
Jewelserv |
|
2/12/2017 |
|
$2,500.00 |
|
Appraisal in safety deposit box |
|
13 |
|
|
Super Hero #1 Comic Book |
|
|
Collectibles |
|
|
Funny Publishing May 1976 (publisher) |
|
|
5/25/2017 |
|
|
$39.00 |
|
|
9.4 Comic Book Grading |
|
14 |
|
Super Hero #6 Comic Book |
|
Funny Publishing November 1976 (publisher) |
|
$67.00 |
|
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.
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 it’s 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.
g. Save the report, naming it “Appliance Purchases,” and then close the report.
Scoring Guidelines
Rubric
|
SKILL/GRADING CRITERIA |
EXEMPLARY |
PROFICIENT |
FAIR |
POOR |
NOT EVIDENT |
|
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 |
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 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. |
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.