© PENN FOSTER, INC. 2016
Graded Project
Integrating Word,
Excel, and Access 2016
© PENN FOSTER, INC. 2016 INTEGRATING WORD, EXCEL, PAGE 1
AND ACCESS 2016
Graded Project
OVERVIEW 2
INSTRUCTIONS 2
SCORING GUIDELINES 10
SUBMITTING YOUR PROJECT 11
INTRODUCTION
CONTENTS
© PENN FOSTER, INC. 2016 INTEGRATING WORD, EXCEL, PAGE 2
AND ACCESS 2016
Graded Project
GRADED PROJECT
INTEGRATING WORD, EXCEL,
AND ACCESS 2016
INTRODUCTION
This project involves using mail merge to send the same company information to multiple
perspective clients. You’ll assume the role of estimator at an engineering company. For
this project, you’ll complete a one-table database with client information, a letter that
integrates an Excel spreadsheet and Access merge fields, and final merge documents
that display the actual field values.
CREATE THE CLIENTS DATABASE
1. Start Access and create a new Blank database named “Clients”.
2. Name the new table “Clients,” and then display it in Design view.
3. Edit the ID field to be Client ID. Verify that it is data type AutoNumber and that it
has been selected as the Primary key.
4. In Design view, add the remaining fields and corresponding data types:
Field Name Data Type
Company Name Short Text
First Name Short Text
Last Name Short Text
Job Title Short Text
Address Short Text
City Short Text
State Short Text
Zip Short Text
5. Save and then close the Clients table.
© PENN FOSTER, INC. 2016 INTEGRATING WORD, EXCEL, PAGE 3
AND ACCESS 2016
Graded Project
6. Create a Clients form that looks similar to Figure 1.
Figure 1
7. Use the Clients form to populate the Clients table with the records listed below.
Client Records
Client ID 1
Company Name Martinez Development Company
First Name Chris
Last Name Martinez
Job Title President
Address 160 Egret Way
City Any Town
State FL
Zip 33441
© PENN FOSTER, INC. 2016 INTEGRATING WORD, EXCEL, PAGE 4
AND ACCESS 2016
Graded Project
Client ID 2
Company Name General Construction
First Name Tom
Last Name Lewis
Job Title Manager
Address 1336 Ocean Parkway
City Any Town
State FL
Zip 33442
Client ID 3
Company Name Ace Contractors
First Name Terrell
Last Name Jackson
Job Title Operator
Address 14998 Atlantic Ave.
City Any Town
State FL
Zip 33434
Client ID 4
Company Name Major Paving Company
First Name Avery
Last Name Collins
Job Title President
Address 2201 Congress Road
City Any Town
State FL
Zip 33441
© PENN FOSTER, INC. 2016 INTEGRATING W ORD, EXCEL, PAGE 5
AND ACCESS 2016
Graded Project
Client ID 5
Company Name
Best Builders
First Name Pat
Last Name Rivera
Job Title Owner
Address 1 Builder Way
City Any Town
State FL
Zip 33443
8. Close the Clients database.
CREATE A LETTERHEAD
1. Start Word and create a new Blank document.
2. Type “ABC Engineering Company” and then press Enter.
3. Type “1552 Carbondale Road” and then insert a bullet character.
4. Type “Any Town, FL 33334” and then press Enter.
5. Format the first line of the letterhead as Verdana 16-point bold and center the paragraph.
6. Format the second line of the letterhead as Verdana 12-point italic and center the paragraph.
7. Format the last, blank paragraph as Times New Roman 12-point with No Spacing paragraph style. Your letterhead should look similar to Figure 2.
Figure 2
8. Save the document, naming it “Letterhead”.
© PENN FOSTER, INC. 2016 INTEGRATING W ORD, EXCEL, PAGE 6
AND ACCESS 2016
Graded Project
CREATE THE ESTIMATES LETTER
1. With the insertion point in the blank paragraph below the letterhead, type the text shown in Figure 3, inserting merge fields where indicated.
¶
¶
¶
September 7, 20xx¶
¶
«First_Name» «Last_Name» ¶
«Company Name» ¶
«Address» ¶
«City», «State» «Zip» ¶
¶
As «Job_Title» of «Company_Name», you may be interested in the services ABC Engineering can provide for your next project. Our site work is top quality, and our prices are competitive. To give you a better idea, the worksheet below estimates your costs for three scenarios: ¶
¶
¶
¶
Please feel free to contact me directly with any questions regarding these estimate. I look
forward to hearing from you in the near future. ¶
¶
Regards, ¶
¶
¶
¶
¶
Casey Graham
(Continued)
© PENN FOSTER, INC. 2016 INTEGRATING W ORD, EXCEL, PAGE 7
AND ACCESS 2016
Graded Project
Figure 3
2. On the File tab, click Save As and then save the document with the name “Estimates”.
© PENN FOSTER, INC. 2016 INTEGRATING W ORD, EXCEL, PAGE 8
AND ACCESS 2016
Graded Project
Figure 4
Figure 5
EMBED AN EXCEL WORKSHEET OBJECT
1. With the insertion point in the second blank paragraph after “. . . costs for three scenarios:”, insert an Excel worksheet object to embed a new spreadsheet.
2. Enter data as shown in Figure 4. Center and bold column labels and format Sites data to display commas with 0 decimal places.3. Format cells C3 through F4 as Currency with 0 decimal places.
4. Enter formulas in column C to calculate Equipment costs as 15 times the site size.
5. Enter formulas in column D to calculate the Labor costs as 10 times the site size.
6. Enter formulas in column E to calculate the Overhead and Profit (O & P) as 0.25 times the sum of equipment and labor costs.
7. Use a function in a formula to calculate the total costs in column F for each scenario.
8. Size the worksheet object so just the cells with data are displayed, as shown in Figure 5.
© PENN FOSTER, INC. 2016 INTEGRATING W ORD, EXCEL, PAGE 9
AND ACCESS 2016
Graded Project
MERGE FIELD DATA
1. Use the appropriate command to merge the field data and create a document
containing the individual letters.
2. Save the merged document, naming it “Merged Estimates”. Your first letter should look similar to Figure 6.
Figure 6
© PENN FOSTER, INC. 2016 INTEGRATING W ORD, EXCEL, PAGE 10
AND ACCESS 2016
Graded Project
SCORING GUIDELINES
RUBRIC
Skill/
Grading
Criteria
Exemplary
(4)
Proficient
(3)
Fair
(2)
Poor
(1)
Not
Evident
(0)
Create a database
Correct field names and field types have been used to create a table and form.
Mostly correct field names and field types have been used to create a table and form.
Some correct field names and field types have been used to create a table and form.
An attempt has been made to create a table and form.
No attempt has been made to create a database with a table and form.
Create a letterhead
Letterhead text has been formatted as specified and includes a
special character.
Letterhead text has most
specified formats applied and includes a special character.
Some letterhead formats have been applied with or without a special character.
Text has been entered without any formats or special character.
No attempt has been made to create the letterhead.
Type document text
The specified letter has been correctly typed with correct spacing.
The specified letter has been typed with few mistakes.
The specified letter has been typed with many mistakes.
The specified letter has been typed with numerous mistakes and incorrect spacing.
No attempt to type the
letter has been made.
Insert merge cells
The letter contains all the correct merge fields in the correct positions.
The letter contains most of the correct merge fields in the correct positions.
The letter contains few merge fields.
The letter contains text in place of the merge fields.
No attempt has been made to place merge fields or data at the designated positions.
Create an embeded spreadsheet object
A new spreadsheet object with correct formatting and formulas has been embedded in the letter.
A new spreadsheet object with mostly correct formatting and formulas has been embedded in the letter.
A new spreadsheet object with incorrect or no data has been embedded in the letter.
A spreadsheet with correct formatting and formulas has been created and then linked to the letter.
No attempt to create a new spreadsheet object has been made.
Merge field data
Database merge fields have been merged to
create a
document with individual letters.
Database merge fields are being
previewed in the letter.
N/A
N/A
No attempt has been made to merge fields and create a document containing individual letters.
© PENN FOSTER, INC. 2016 INTEGRATING W ORD, EXCEL, PAGE 11
AND ACCESS 2016
Graded Project
SUBMISSION CHECKLIST
SUBMISSION CHECKLIST
Before submitting your project, make sure you’ve correctly completed the following:
q Create a database with a table and a corresponding form.
q Populate a database table by using a form.
q Type and format text in a Word document.
q Apply Word styles.
q Use the Save As command on the File tab.
q Perform the mail merge process by selecting an existing database as the data source.
q Insert merge fields in a Word letter.
q Insert a new Excel worksheet object in a Word document.
q Enter and format worksheet cell data.
q Create formulas to calculate values in a worksheet.
q Create formulas that use functions to calculate values in a worksheet.
q Size a worksheet object to display only specified cells.
q Merge field data with actual data to create individual letters.
SUBMITTING YOUR PROJECT
Each project is individually graded and therefore could take approximately 5–7 business days to grade. Make sure the following information is in the heading of each file:
OO
Your name
OO
Your email address
OO
Your student number
OO
Course name and number
OO
Project number (584804)
Follow this procedure to submit your assignment online:
1. Go to http://www.pennfoster.edu and log in to your student portal.
2. On your student portal, click on Take Exam.
© PENN FOSTER, INC. 2016 INTEGRATING W ORD, EXCEL, PAGE 12
AND ACCESS 2016
Graded Project
3. In the box provided, enter the examination number.
The number for this exam is 584804.
4. Click Submit.
5. On the next screen, enter your email address. (Note: This information is required for online submission.)
6. If you wish to tell your instructor anything specific regarding this assignment,
enter it in the Comments.
7. Attach your file as follows:
a.
Click on the first Browse box.
b.
Locate the file you wish to attach.
c.
Double-click on the file.
8. Click Submit.
Be sure to keep a backup copy of any files you submit to the school!