Graded Project Integrating Word
with Access and
Excel PREPARING THE DATABASE 2 CREATING THE MAIN DOCUMENT 4 Letterhead
Main Text 4
4 ADDING PLACEHOLDERS 6 EMBEDDING A WORKSHEET 7 MERGING DATA TO A NEW DOCUMENT 8 SUBMITTING YOUR FILES FOR GRADING
Inspecting Your Completed Files
Submitting Your Files 11
11
11 C
o
n
t
e
n
t
s
iii Integrating Word with
Access and Excel
After you've completed all of the assigned reading,
activities, and exercises in the study unit Integrating Word
with Access and Excel and you've taken the examination
for that study unit, you'll be ready to complete your graded
project. The project requires you to use your skills in Word,
Excel, and Access to create a proposal with an embedded
worksheet for the ABC Engineering Company. Since the
proposal is to be distributed to five clients, you'll also
create an address list in an Access database and merge it
with the proposal to create a personalized mailing for each
client.
Here's the order in which you'll complete this project:
1. Prepare a five-name database in Access.
2. Create a letterhead and the main document
for the proposal.
3. Add placeholders to the main document.
4. Embed an Excel worksheet into the main document.
5. Merge all form letters into one final five-page document.
When you complete the entire project, you'll submit it to
the school for grading. You're expected to submit three
files: one for the database, one for the main document
with the embedded worksheet and placeholders, and one
for the merged documents. To guide you as you complete
your project, you may refer to the instructions in your
study unit Integrating Word with Access and Excel.
If you're ready, let's get started by creating the database
for your mailing. 1 PREPARING THE DATABASE
Use the following procedure to create the database for your
mailing:
1. Choose the Blank Desktop Database option in
Access and create a new database file. Name the
file List.
2. Design a table with the following ten fields, as shown
in Figure 1.
• Customer ID
• Company
• Contact First Name
• Contact Last Name
• Billing Address
• City
• State
• Postal Code
• Phone Number
• Fax Number
3. Name the table List Table. FIGURE 1—Create a table containing all the fields you need for your form letter. 4. Create a new form based on the List table. Name
the form List Form. Compare your form with
Figure 2.
5. Insert the information from Figure 3 into five separate
forms in your database. Be sure to save the
information when you're finished. Allow Access to set
the customer ID automatically.
6. Save the forms and return to the table. 2 Integrating Word with Access and Excel 7. Arrange the addresses in
alphabetical order according to
last name.
• Double-click inside the Contact Last Name box
of any of the records in the file.
• Select the Contact Last Name arrow and click
Sort A to Z. FIGURE 2—Create a form. Austin's Automotive Repair Shop
Troy Austin 121 Bay Street
Cornwall, NY 12518
(914) 535-6549 (phone)
(914) 535-1234 (fax) Majestic Landscaping
Mark Florey
22 Canyon Blvd.
Boulder, CO 18888
(303) 535-2254 (phone)
(303) 535-0000 (fax) FIGURE 3—Enter
these five names
and addresses
into your
database. Terry's Photo Processing
Rounds' Fire Restoration
Terry Springer
Michele Rounds
15 Spring Lake Avenue Mifflintown,621 17777 Park Ave. Seaside Park, NJ 00050 (609) 222-1111 (phone)
KS Seaside
(851) 436-5698 (phone)
(609) 222-3333 (fax)
(851) 436-5666 (fax) McOlley's Garden Supply
Elmer McOlley 8 Lowell Street
Bloomfield, NJ 00007
(973) 225-1324 (phone)
(973) 225-5555 (fax) Integrating Word with Access and Excel 3 8. If you have a printer, print a hard copy of your table
and check your work for errors. If you don't have a
printer, check your work by reviewing it line by line
on your com- puter screen. (Check your work
carefully. Your project grade will be based on the
accuracy of your work.)
9. Correct any errors you find.
10. Save the file (List.accdb) to your computer and to
your graded project disk. CREATING THE MAIN
DOCUMENT
Now that your mailing list is complete, you need to create a
proposal letter to merge with the database. You'll prepare
the proposal document just like a standard form letter. Letterhead
Using Word, prepare the letterhead shown in Figure 4.
1. Type the company name (ABC Engineering
Company) in Times New Roman, 22-point, bold,
italic.
2. Select the heading and use the Alphabet feature
to create two 2¼-point horizontal lines.
3. Below the lines, type the company address
(1552 Carbondale Road – Mechanicsburg,
PA – 17055) in Times New Roman, 14-point,
bold, italic.
4. Save the file as proposal.docx. Main Text
Using Arial 12-point, single spaced, type the body of the
letter, also shown in Figure 4.
1. After the copy for the letterhead, press Enter
three
times.
2. Type the current date.
3. Press Enter two times.
4. Enter the main text from Figure 4. 4 Integrating Word with Access and Excel FIGURE 4—This is what your letterhead and main text should look like. 5. Save the document (proposal.docx).
6. If you have a printer, print a hard copy of your document and check your work for errors. If you don't have
a printer, check your work by reviewing it line by line
on your computer screen. (Your project grade will be
based on how accurately you input information and
how closely you followed the instructions.)
7. Correct any errors you find.
8. Save the file to your computer and/or to your
graded project disk. Integrating Word with Access and Excel 5 ADDING PLACEHOLDERS
Use the following procedure to merge the field information
from List.accdb into proposal.docx:
1. Use the Mail Merge feature to place the merge fields
in the same locations as those shown in Figure 5. (If
neces- sary, use your study unit as a reference.) Note:
To locate the merge fields from your database, click the
More but- ton in step 4 of the mail merge. Use
commas and spaces where necessary. Be sure to use
single spacing. FIGURE 5—This is the main document with placeholders. 6 Integrating Word with Access and Excel 2. When you've finished entering
the merge fields, print your
letter (if you have a printer)
and check your work for errors
against Figure 5. If you don't
have a printer, check your
work carefully by reviewing it
line by line on your computer
screen.
3. Correct any errors you find.
4. Save your document before going on. EMBEDDING A WORKSHEET
Make sure that you've saved your document
(proposal.docx) before you embed the worksheet. Then,
use the following steps to embed an Excel worksheet into
your main docu- ment. Use Figure 6 as a reference. FIGURE 6—Enter this data directly into your worksheet. The remaining
cells (range C3:F5) will contain formulas. 1. Click the insertion point between the first and
second paragraphs. Press Enter three times.
2. Press the Up Arrow key once.
3. Insert an Excel worksheet between the first and
second paragraphs of the letter.
4. Enter data into the worksheet as follows:
a. Using Figure 6 as a reference, enter the data in
range B1:F2 in bold type, centered in each
column.
b. Referring to Figure 6, enter the data in range A3:B5.
5. Format cells in range C3:F5 for Accounting Number
style with no decimal places. Integrating Word with Access and Excel 7 6. Insert formulas in range C3:F5 as follows:
a. In the Materials column (C), enter a formula to calculate material cost
at 15 times the job size (column B).
b. In the Labor column (D), enter a formula to calcu- late labor cost at 10
times the job size (column B).
c. In the Overhead and Profit (O&P) column (E), enter a formula to calculate
O&P at 0.25 times the sum of materials and labor. Hint: The formula for
cell E3 should be =.25*(C3+D3).
d. In the Total column (F), enter a formula to calculate total cost as the sum
of columns C, D, and E.
7. Resize the worksheet to five rows and six columns, and remove the gridlines.
Compare your screen with Figure 7. Remove extra spaces if your letter extends
to two pages.
8. If you have a printer, print a hard copy of your docu- ment and check your
work for errors. If you don't have a printer, check your work by reviewing it line
by line on your computer screen.
9. Correct any errors you find.
10. Save the file (proposal.docx) to your computer and to your graded project
disk. MERGING DATA
TO A NEW
DOCUMENT
You've prepared your database. You've input the main document and embedded the worksheet. Now all you have to do
is merge the database with the document to create five
sepa- rate personalized letters.
Use the following procedure to merge your data to a
new document:
1. Use the Mail Merge feature to create a new five-page
document containing your personalized letters. (A
copy of the proposal for Troy Austin is shown in Figure
8.) 8 Integrating Word with Access and Excel FIGURE 7—The embedded worksheet as it appears in your form letter. 2. Save this document as
Final.docx. Remember: When
you merge database
information to create a
personalized mailing, it's
usually best to create a new
document to contain the
merged letters.
Integrating Word with Access and Excel 9 FIGURE 8—Compare your screen with the personalized letter to Troy Austin. 3. If you have a printer, print a hard copy of your
document and check your work for errors. You should
have five sepa- rate letters in your document, one for
each of the persons listed in Figure 3. If you don't have
a printer, check your work by reviewing it line by line
on your computer screen. 10 Integrating Word with Access and Excel 4. Correct any errors you find.
5. Save the file again
(Final.docx). Your
integrated form letter is
now complete. SUBMITTING YOUR
FILES FOR
GRADING
Congratulations! Now that you've completed the project
for Integrating Word with Access and Excel, you're ready
to apply what you've learned about integration in your
own enterprises.
All you have left to do is to inspect your completed files one
last time and return them to the school for grading. Inspecting Your Completed Files
1. Proofread your work.
a. If you have a printer, check a hard copy of your
work for errors. If you don't have a printer, open
the files on your disk and review them line by line
on your computer screen.
b. Since part of your project grade is based on the
accuracy of the information you entered, make
sure your final document is free of errors.
2. Check the worksheet.
a. Make sure you've entered the numbers correctly.
b. Check your formulas to see that they
correctly calculate the amounts shown in
Figure 7.
3. Make the necessary corrections and then save
your corrected files to your hard disk and to your
graded project disk. Submitting Your Files
Follow this procedure to submit your project online:
1. Log onto the Penn Foster website and go to
"My Courses." Integrating Word with Access and Excel 11 2. Click on Take Exam for this graded project.
3. Attach your files as follows:
a.Click the Browse box.
b. Locate the file you wish to attach.
c. Double-click the file.
d. Click Upload File.
e.Since you have more than one file to attach, click
the Browse box again, and repeat steps b, c, and d
for each file.
4. Enter your e-mail address in the box provided. (Note: This information is
required for online submission.)
5. Click Submit File. 12 Integrating Word with Access and Excel
with Access and
Excel PREPARING THE DATABASE 2 CREATING THE MAIN DOCUMENT 4 Letterhead
Main Text 4
4 ADDING PLACEHOLDERS 6 EMBEDDING A WORKSHEET 7 MERGING DATA TO A NEW DOCUMENT 8 SUBMITTING YOUR FILES FOR GRADING
Inspecting Your Completed Files
Submitting Your Files 11
11
11 C
o
n
t
e
n
t
s
iii Integrating Word with
Access and Excel
After you've completed all of the assigned reading,
activities, and exercises in the study unit Integrating Word
with Access and Excel and you've taken the examination
for that study unit, you'll be ready to complete your graded
project. The project requires you to use your skills in Word,
Excel, and Access to create a proposal with an embedded
worksheet for the ABC Engineering Company. Since the
proposal is to be distributed to five clients, you'll also
create an address list in an Access database and merge it
with the proposal to create a personalized mailing for each
client.
Here's the order in which you'll complete this project:
1. Prepare a five-name database in Access.
2. Create a letterhead and the main document
for the proposal.
3. Add placeholders to the main document.
4. Embed an Excel worksheet into the main document.
5. Merge all form letters into one final five-page document.
When you complete the entire project, you'll submit it to
the school for grading. You're expected to submit three
files: one for the database, one for the main document
with the embedded worksheet and placeholders, and one
for the merged documents. To guide you as you complete
your project, you may refer to the instructions in your
study unit Integrating Word with Access and Excel.
If you're ready, let's get started by creating the database
for your mailing. 1 PREPARING THE DATABASE
Use the following procedure to create the database for your
mailing:
1. Choose the Blank Desktop Database option in
Access and create a new database file. Name the
file List.
2. Design a table with the following ten fields, as shown
in Figure 1.
• Customer ID
• Company
• Contact First Name
• Contact Last Name
• Billing Address
• City
• State
• Postal Code
• Phone Number
• Fax Number
3. Name the table List Table. FIGURE 1—Create a table containing all the fields you need for your form letter. 4. Create a new form based on the List table. Name
the form List Form. Compare your form with
Figure 2.
5. Insert the information from Figure 3 into five separate
forms in your database. Be sure to save the
information when you're finished. Allow Access to set
the customer ID automatically.
6. Save the forms and return to the table. 2 Integrating Word with Access and Excel 7. Arrange the addresses in
alphabetical order according to
last name.
• Double-click inside the Contact Last Name box
of any of the records in the file.
• Select the Contact Last Name arrow and click
Sort A to Z. FIGURE 2—Create a form. Austin's Automotive Repair Shop
Troy Austin 121 Bay Street
Cornwall, NY 12518
(914) 535-6549 (phone)
(914) 535-1234 (fax) Majestic Landscaping
Mark Florey
22 Canyon Blvd.
Boulder, CO 18888
(303) 535-2254 (phone)
(303) 535-0000 (fax) FIGURE 3—Enter
these five names
and addresses
into your
database. Terry's Photo Processing
Rounds' Fire Restoration
Terry Springer
Michele Rounds
15 Spring Lake Avenue Mifflintown,621 17777 Park Ave. Seaside Park, NJ 00050 (609) 222-1111 (phone)
KS Seaside
(851) 436-5698 (phone)
(609) 222-3333 (fax)
(851) 436-5666 (fax) McOlley's Garden Supply
Elmer McOlley 8 Lowell Street
Bloomfield, NJ 00007
(973) 225-1324 (phone)
(973) 225-5555 (fax) Integrating Word with Access and Excel 3 8. If you have a printer, print a hard copy of your table
and check your work for errors. If you don't have a
printer, check your work by reviewing it line by line
on your com- puter screen. (Check your work
carefully. Your project grade will be based on the
accuracy of your work.)
9. Correct any errors you find.
10. Save the file (List.accdb) to your computer and to
your graded project disk. CREATING THE MAIN
DOCUMENT
Now that your mailing list is complete, you need to create a
proposal letter to merge with the database. You'll prepare
the proposal document just like a standard form letter. Letterhead
Using Word, prepare the letterhead shown in Figure 4.
1. Type the company name (ABC Engineering
Company) in Times New Roman, 22-point, bold,
italic.
2. Select the heading and use the Alphabet feature
to create two 2¼-point horizontal lines.
3. Below the lines, type the company address
(1552 Carbondale Road – Mechanicsburg,
PA – 17055) in Times New Roman, 14-point,
bold, italic.
4. Save the file as proposal.docx. Main Text
Using Arial 12-point, single spaced, type the body of the
letter, also shown in Figure 4.
1. After the copy for the letterhead, press Enter
three
times.
2. Type the current date.
3. Press Enter two times.
4. Enter the main text from Figure 4. 4 Integrating Word with Access and Excel FIGURE 4—This is what your letterhead and main text should look like. 5. Save the document (proposal.docx).
6. If you have a printer, print a hard copy of your document and check your work for errors. If you don't have
a printer, check your work by reviewing it line by line
on your computer screen. (Your project grade will be
based on how accurately you input information and
how closely you followed the instructions.)
7. Correct any errors you find.
8. Save the file to your computer and/or to your
graded project disk. Integrating Word with Access and Excel 5 ADDING PLACEHOLDERS
Use the following procedure to merge the field information
from List.accdb into proposal.docx:
1. Use the Mail Merge feature to place the merge fields
in the same locations as those shown in Figure 5. (If
neces- sary, use your study unit as a reference.) Note:
To locate the merge fields from your database, click the
More but- ton in step 4 of the mail merge. Use
commas and spaces where necessary. Be sure to use
single spacing. FIGURE 5—This is the main document with placeholders. 6 Integrating Word with Access and Excel 2. When you've finished entering
the merge fields, print your
letter (if you have a printer)
and check your work for errors
against Figure 5. If you don't
have a printer, check your
work carefully by reviewing it
line by line on your computer
screen.
3. Correct any errors you find.
4. Save your document before going on. EMBEDDING A WORKSHEET
Make sure that you've saved your document
(proposal.docx) before you embed the worksheet. Then,
use the following steps to embed an Excel worksheet into
your main docu- ment. Use Figure 6 as a reference. FIGURE 6—Enter this data directly into your worksheet. The remaining
cells (range C3:F5) will contain formulas. 1. Click the insertion point between the first and
second paragraphs. Press Enter three times.
2. Press the Up Arrow key once.
3. Insert an Excel worksheet between the first and
second paragraphs of the letter.
4. Enter data into the worksheet as follows:
a. Using Figure 6 as a reference, enter the data in
range B1:F2 in bold type, centered in each
column.
b. Referring to Figure 6, enter the data in range A3:B5.
5. Format cells in range C3:F5 for Accounting Number
style with no decimal places. Integrating Word with Access and Excel 7 6. Insert formulas in range C3:F5 as follows:
a. In the Materials column (C), enter a formula to calculate material cost
at 15 times the job size (column B).
b. In the Labor column (D), enter a formula to calcu- late labor cost at 10
times the job size (column B).
c. In the Overhead and Profit (O&P) column (E), enter a formula to calculate
O&P at 0.25 times the sum of materials and labor. Hint: The formula for
cell E3 should be =.25*(C3+D3).
d. In the Total column (F), enter a formula to calculate total cost as the sum
of columns C, D, and E.
7. Resize the worksheet to five rows and six columns, and remove the gridlines.
Compare your screen with Figure 7. Remove extra spaces if your letter extends
to two pages.
8. If you have a printer, print a hard copy of your docu- ment and check your
work for errors. If you don't have a printer, check your work by reviewing it line
by line on your computer screen.
9. Correct any errors you find.
10. Save the file (proposal.docx) to your computer and to your graded project
disk. MERGING DATA
TO A NEW
DOCUMENT
You've prepared your database. You've input the main document and embedded the worksheet. Now all you have to do
is merge the database with the document to create five
sepa- rate personalized letters.
Use the following procedure to merge your data to a
new document:
1. Use the Mail Merge feature to create a new five-page
document containing your personalized letters. (A
copy of the proposal for Troy Austin is shown in Figure
8.) 8 Integrating Word with Access and Excel FIGURE 7—The embedded worksheet as it appears in your form letter. 2. Save this document as
Final.docx. Remember: When
you merge database
information to create a
personalized mailing, it's
usually best to create a new
document to contain the
merged letters.
Integrating Word with Access and Excel 9 FIGURE 8—Compare your screen with the personalized letter to Troy Austin. 3. If you have a printer, print a hard copy of your
document and check your work for errors. You should
have five sepa- rate letters in your document, one for
each of the persons listed in Figure 3. If you don't have
a printer, check your work by reviewing it line by line
on your computer screen. 10 Integrating Word with Access and Excel 4. Correct any errors you find.
5. Save the file again
(Final.docx). Your
integrated form letter is
now complete. SUBMITTING YOUR
FILES FOR
GRADING
Congratulations! Now that you've completed the project
for Integrating Word with Access and Excel, you're ready
to apply what you've learned about integration in your
own enterprises.
All you have left to do is to inspect your completed files one
last time and return them to the school for grading. Inspecting Your Completed Files
1. Proofread your work.
a. If you have a printer, check a hard copy of your
work for errors. If you don't have a printer, open
the files on your disk and review them line by line
on your computer screen.
b. Since part of your project grade is based on the
accuracy of the information you entered, make
sure your final document is free of errors.
2. Check the worksheet.
a. Make sure you've entered the numbers correctly.
b. Check your formulas to see that they
correctly calculate the amounts shown in
Figure 7.
3. Make the necessary corrections and then save
your corrected files to your hard disk and to your
graded project disk. Submitting Your Files
Follow this procedure to submit your project online:
1. Log onto the Penn Foster website and go to
"My Courses." Integrating Word with Access and Excel 11 2. Click on Take Exam for this graded project.
3. Attach your files as follows:
a.Click the Browse box.
b. Locate the file you wish to attach.
c. Double-click the file.
d. Click Upload File.
e.Since you have more than one file to attach, click
the Browse box again, and repeat steps b, c, and d
for each file.
4. Enter your e-mail address in the box provided. (Note: This information is
required for online submission.)
5. Click Submit File. 12 Integrating Word with Access and Excel
No comments:
Post a Comment