Advanced Applications

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-in… 1/10

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 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

0 people liked this 0 discussions

Graded Project: Integrating Word, Excel, and Access 2016

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 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, 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!

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-in… 2/10

State Short Text

Zip Short Text

5. Save and then close the Clients table. 6. Create a Clients form that looks similar to the figure below.

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

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-in… 3/10

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

State FL

Zip 33434

Client ID 4

Company Name Major Paving Company

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-in… 4/10

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

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 the figure.

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-in… 5/10

8. Save the document, naming it “Letterhead.”

Create the Estimates Letter

1. With the insertion point in the blank paragraph below the letterhead, type the text shown in the figure below, inserting merge fields where indicated.

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-in… 6/10

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-in… 7/10

2. On the File tab, click Save As and then save the document with the name “Estimates.”

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 the figure below. Center and bold column labels and format Site 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.

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-in… 8/10

8. Size the worksheet object so just the cells with data are displayed, as shown in the figure.

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.

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-in… 9/10

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 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.

2/25/2020 lms.pennfoster.edu | HSE754: Advanced PC Applications

https://lms.pennfoster.edu/nodes/graded-project-integrating-word-access-and-excel/objectives/construct-a-mail-merge-to-send-the-same-company-i… 10/10

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 only specified cells. Merge field data with actual data to create individual letters.