intergrating, word, excel and access

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

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

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.

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 below, inserting merge fields where required to fill in the client information.

¶

¶

¶

September 7, 20xx¶

¶

<<First_Name>> <<Last_Name>>¶

<<Company Name>>¶

<<Address>>¶

<<City>>, <<State>> <<Zip>>¶

¶

Dear <<First_Name>> <<Last_Name>>,¶

¶

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 estimates. I look forward to hearing from you in the near future.¶

¶

Regards,¶

¶

¶

¶

¶

Casey Graham

2. Your letter should look like the figure below.

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.

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.

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.

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

10. Save the document.

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.

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