Advanced PC Applications : Integrating Word, Excel, and Access Graded Project
Lesson 6 Overview
This project involves using
mail merge to send the same
company information to
multiple prospective 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.
6.1 Construct a mail merge to send the same company information to multiple perspective clients Graded Project: Integrating Word, Excel, and Access
READING ASSIGNMENT
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
Advanced PC Applications (v2) : Lesson 6 : Page 1© 2020 Penn Foster Inc.
weeks to grade. Be sure that each of your files contains the following information:
Your name Your student ID number The lesson number (584084) Your email address
Note: If you have more than 10 attachments, you’ll need to collect all of your files into a compressed folder. To do this, follow these instructions based on your operating system:
Windows: Select the files you want to compress, right-click and select Send to and then Compressed (zipped) folder. Mac: Select the files you want to compress, control-click and select Compress.
To submit your graded project, follow these steps:
Go to http://www.pennfoster.edu (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 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’s data type
AutoNumber and that it has been selected as the primary key.
4. In Design view, add the remaining fields and corresponding data
Advanced PC Applications (v2) : Lesson 6 : Page 2© 2020 Penn Foster Inc.
http://www.pennfoster.edu
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 close the Clients table.
6. Create a Clients form that includes the following information:
Client ID
Company Name
First Name
Last Name
Job Title
Address
City
State
Zip
7. The form should look similar to the figure below.
Advanced PC Applications (v2) : Lesson 6 : Page 3© 2020 Penn Foster Inc.
[ An image of the Clients form ]
8. 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
Advanced PC Applications (v2) : Lesson 6 : Page 4© 2020 Penn Foster Inc.
Job Title President
Address 160 Egret Way
City Any Town
State FL
Zip 33441
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
Advanced PC Applications (v2) : Lesson 6 : Page 5© 2020 Penn Foster Inc.
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
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
Advanced PC Applications (v2) : Lesson 6 : Page 6© 2020 Penn Foster Inc.
9. 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 the figure.
[An image of the text entered so far]
8. Save the document, naming it “Letterhead.”
Create the Estimates Letter
Advanced PC Applications (v2) : Lesson 6 : Page 7© 2020 Penn Foster Inc.
1. With the insertion point in the blank paragraph below the
letterhead, type the text shown below, inserting merge fields where
required to fill in the client information.
¶
¶
¶
September 7, 20xx¶
¶
<
<
<
>¶<
¶
Dear <
¶
As <
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
Advanced PC Applications (v2) : Lesson 6 : Page 8© 2020 Penn Foster Inc.
idea, the worksheet below estimates your costs for three scenarios:¶
¶
¶
¶
Please feel free to contact me directly with any questions regarding
these estimates. I look forward to hearing from you in the near future.¶
¶
Regards,¶
¶
¶
¶
¶
Casey Graham
2. Your letter should look like the figure below.
Advanced PC Applications (v2) : Lesson 6 : Page 9© 2020 Penn Foster Inc.
[An image of the typed letter.]
3. On the File tab, click Save As and then save the document with the
name “Estimates.”
Embed an Excel Worksheet Object
1. Place the insertion point in the second blank paragraph after “. . .
costs for three scenarios:” and insert an Excel worksheet object to
embed a new spreadsheet.
Advanced PC Applications (v2) : Lesson 6 : Page 10© 2020 Penn Foster Inc.
2. Enter the following data. Center and bold column labels and format
Site data to display commas with 0 decimal places.
Site (sq. ft.) Equipment Labor 25% O&P Total Cost
Scenario 1 5,000
Scenario 2 10,000
Scenario 3 12,500
3. The worksheet should appear as in the figure below.
[An image of the worksheet.]
4. Format cells C2 through F4 as Currency with 0 decimal places.
5. Enter formulas in column C to calculate Equipment costs as 15
times the site size.
6. Enter formulas in column D to calculate the Labor costs as 10 times
the site size.
7. Enter formulas in column E to calculate the Overhead and Profit
(O&P) as 0.25 times the sum of equipment and labor costs.
8. Use a function in a formula to calculate the total costs in column F
for each scenario.
Advanced PC Applications (v2) : Lesson 6 : Page 11© 2020 Penn Foster Inc.
9. Size the worksheet object so only the cells with data are displayed,
as shown in the figure.
10. Save the document.
[An image of the worksheet with formula results displayed.]
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 the figure below.
Advanced PC Applications (v2) : Lesson 6 : Page 12© 2020 Penn Foster Inc.
[An image of the first letter with mail merge fields displaying merged data.]
Scoring Guidelines
Rubric
Skill/Grading Criteria
Exemplary (4)
Proficient (3)
Fair (2)
Poor (1)
Not Evident (0)
Advanced PC Applications (v2) : Lesson 6 : Page 13© 2020 Penn Foster Inc.
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 typed correctly 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 embedded
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.
Advanced PC Applications (v2) : Lesson 6 : Page 14© 2020 Penn Foster Inc.
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.
Submission Checklist
Before submitting your project, make sure you’ve correctly completed
the following:
Create a database with a table and a corresponding form.
Populate a database table by using a form.
Type and format text in a Word document.
Apply Word styles.
Use the Save As command on the File tab.
Perform the mail merge process by selecting an existing database
as the data source.
Insert merge fields in a Word letter.
Insert a new Excel worksheet object in a Word document.
Enter and format worksheet cell data.
Create formulas to calculate values in a worksheet.
Create formulas that use functions to calculate values in a
worksheet.
Size a worksheet object to display specified cells only.
Merge field data with actual data to create individual letters.
Advanced PC Applications (v2) : Lesson 6 : Page 15© 2020 Penn Foster Inc.
58411200
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at [email protected].
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at [email protected] and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at [email protected].
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.