Advanced PC Applications Lesson 6

AdvancedPCApplications6.docx

Home >Applied Sciences homework help >Advanced PC Applications Lesson 6

Advanced PC Applications

Integrating Word, Excel, and Access Graded Project

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:

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

An image of a Clients form

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

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

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.

An image of letterhead for ABC Engineering Company

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.

An image of the text for an estimates letter

An image of the text for an estimates letter placed on ABC Engineering Company letterhead

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.

An image of data entered into a spreadsheet

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.

An image of data entered into a spreadsheet

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.

An image of a completed estimates letter from ABC Engineering Company

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