Transcript Computers Merit Badge Requirement 4

Computers Merit
Badge
Requirement 4
(Microsoft Office XP)
1
Do THREE of the following four
options to Requirement 4; print out
the results for the instructor

a. Use a database manager...

Create a troop roster, providing name, rank, patrol, and telephone number of
each Scout.



Sort the register by rank, by patrol, and alphabetically by name.
b. Use a spreadsheet program...

Develop a weekend campout food budget for your patrol.


A minimum of FOUR meals and the cost share for EIGHT Scouts
c. Use a word processor...


Write a letter to parents of your troop’s Scouts, inviting them to a court of honor.
Use the mail merge feature to make a personalized copy of the letter for each
family.


A minimum of FOUR Scout records in the database
A minimum of FOUR family records in the database
d. Use a computer graphics program...

Design and draw a campsite plan for your troop.

A minimum of SIX basic elements
2
a. Use a database manager...



Create a troop roster, providing the name, rank, patrol, and telephone
number of each Scout.
Sort the register by rank, by patrol, and alphabetically by name.
You can do this in Microsoft Access, using a blank database or the
Contact Management Wizard
Create a table for the data (or edit the fields in the wizard)
 Create the data records
 Create queries to sort the data in each order needed
 Create reports to print the data by rank, by patrol, and alphabetically by
name, with your Name and Troop number in the Report Header
 Print all three reports and turn them in to the Instructor
NOTE: This option does not have to be “taught” during the class due to…
 The amount of time it takes to build anything in Access
 The prevalence of TroopMaster and ScoutMate programs
 Step-by-step instructions ARE provided here for because WE will do
this in class, then use the same database for our mail merge in 4c.


3
Create database; table wizard





Open Microsoft Access and in the dialog
“Create a new database using” select
“Access database wizards, pages, and
projects” and click OK
In the “General” tab select “Database” and
click OK
Save the file as “Troop1000.mdb” and click on
“Create”
Double-click on “Create table by using wizard”
– the Table Wizard will start – click on
“Personal”
Select the “Addresses” sample table and click
on the “>” button to add fields to the new table

Add AddressID, FirstName, LastName, Address,
City, StateOrProvince, PostalCode,
EmailAddress, HomePhone, BirthDate, and
Nickname
4
Customize table, enter data

Change the Sample Table selection to
“Plants,” add two more sample fields,
clicking on “Rename Field to rename
them as follows:






Add “Genus,” rename it to “Rank”
Add “Species,” rename it to “Patrol”
Click on “Next”, accept the table name
of “Addresses” and allow it to set a
primary key for you
Select “Enter data in a form the wizard
creates for me” and “Finish”
In the displayed form, enter the data
for at least four imaginary Scouts from
your Troop, two of them brothers
(same last name)
When finished, save changes to
Form1 and accept the form name of
“Addresses”
5
Create Queries






Click on the “Queries” panel under the
“Objects” pane
Double-click on “Create query by using
wizard”
Using the Table: Addresses, click on the
“>>” button to add all fields
Then select the AddressID field and
click on the “<“ button to remove it
Click “Next” and change the Query Title
to “Addresses by Name” and select
“Modify the query design,” then click on
“Finish”
In Design View, drag the FirstName and
LastName columns to the left
6
Continue modifying queries



Click on the “Sort” property under
LastName and set it to “Ascending”
Do the same for FirstName
Select “View – Datasheet View” from
the menu bar to see your data







The brothers should be sorted
alphabetically by last name,
THEN by first name
“File – Save” to save the changes
Click on “File – Save As” and save as
name “Addresses by Rank”, and again
as “Addresses by Patrol”
There should be three different queries
in the Queries pane; select “Addresses
by Patrol” and click the “Design” icon
Drag the Patrol column to the left side
of the form and set it to Sort Ascending
Save, and switch to the Datasheet
View to see your data sorted on Patrol,
then on LastName, then on FirstName
Next modify “Addresses by Rank”
7
Create Reports








Click on the “Reports” panel under “Objects”
Double-click on “Create report by using wizard”
Using the “Query: Addresses by Patrol,” click on
the “>>” button to add all fields, then click “Next”
In the Report Wizard, select the field “Patrol” and
click on “>” to group on it - the report preview will
show that it is grouped under Patrol
Click on “Next” and do not set a Sort Order – the
Query will already provide that
Click on “Next” and set Layout to “Align Left 2”
and Orientation to “Landscape” (see below)
Click on “Next” and set to “Corporate” style
Click on “Next” and
accept the Report Title
“Addresses by Patrol,”
then click on “Finish”
to preview the report
8
Preview the Report (by Patrol)



Print this report out for your
instructor
If you have extra time, use the
Report Wizard to create a report
based on the “Addresses by
Rank” query, Grouping on Rank,
and Sorting on Patrol
This report groups on Rank, but
ALPHABETICALLY! To do this
correctly, you would need a
query sorted on a numerical key
9
b. Use a spreadsheet program...

Develop a weekend campout food budget for
your patrol.


Include four meals
Show the following for each item


Show the following for each meal


Meal, Item name, packaging, quantity, price, total cost
Subtotal of meal cost
Show the following for the campout


Total cost of all meals
Cost share for eight patrol members
10
Create the spreadsheet

Create a new worksheet and organize the heading
using the “Merge and Center” icon on the toolbar
Troop 132 Duct Tape Patrol Menu by Joe Doe
Enter the following headings for columns and a section for the first meal
Meal
Item
Breakfast

Packaging Quantity Cost
Total
Then enter items, packaging, quantities, cost (prices)
Meal
Item
Packaging
Quantity
Cost
Total
Breakfast
Eggs
dozen
1
3.25
Bacon
1/2 lb
1
6.25
Sweet rolls
dozen
1
6.45
COFFEE
10lb Can
1
12.95
11
Set up the totals and format
the columns for currency





In cell F4 type “=D4*E4” and press Enter
Select the lower right corner of F4 and drag it down to F8 to extend the
calculation
Type “Subtotal” in cell E9
Select cell F9, click on the “Σ” on the toolbar and select cells F4 through F8
and press Enter
Select cells E4 through F9 and click on the “$” symbol on the toolbar
12
Add three more meals and total,
then calculate each share






Select cells A4 through F9 and copy, then paste to A11
Do this twice more down the sheet (at A17 and A23 in
my example), then re-label the meals Lunch, Dinner,
and Breakfast. Change the menu items appropriately.
In cell E30 type “TOTAL” and in cell F30 type
“=F9+F15+F21+F28” (or the correct cell references for
YOUR subtotals).
In cell F31 type the number of people in the patrol… “8”
and label it “Scouts or Participants” in cell E31
In cell F32 type “=F30/F31” and label it “Cost share” in
cell E32
Format cells F30 and F32 as currency.
13
Sample Food Budget
Food budget for December 2001 Campout - Geezer Patrol
Johnny Jones, Troop 999
Meal
Item
Breakfast Eggs
Bacon
Sweet rolls
COFFEE
Packaging Quantity
Cost
dozen
1 $
3.25
1/2 lb
1 $
6.25
dozen
1 $
6.45
10lb Can
1 $
12.95
Subtotal
Lunch
Dinner
Total
$
3.25
$
6.25
$
6.45
$ 12.95
$
$ 28.90
Bread, wheat
Lunch meat
Loaf
Package
1 $
2 $
4.25
6.50
6.50
Ribeye Steak
Potatoes
1 lb 2-pack
5lb Bag
5 $
1 $
4.25 $
8.22 $
$
$
Subtotal
$
21.25
8.22
29.47
dozen
1/2 lb
dozen
10lb Can
1
1
1
1
$
$
$
$
$
$
$
$
$
$
3.25
6.25
6.45
12.95
28.90
TOTAL
$
Participants
Cost Share $
93.77
8
11.72
Breakfast Eggs
Bacon
Sweet rolls
COFFEE
4.25 $
3.25 $
$
$
Subtotal
$
3.25
6.25
6.45
12.95
Subtotal



Check your
subtotals, totals,
and cost share for
math accuracy
Make sure that
you included
your Name and
Troop number
on the
worksheet
Print the final
copy of your
spreadsheet out
and turn it in to
the instructor
14
c. Use a word processor...

Write a letter to parents of your troop’s Scouts,
inviting them to a court of honor.


Create a mail merge data table to be used with the
letter.




At least two short paragraphs long.
Enter the name and address for four families in a patrol.
- or – use your existing Access Database from 4a.
Use the mail merge feature to make a personalized
copy of the letter for each family.
In this classroom we will use Microsoft Word XP and
its Mail Merge Wizard to perform these tasks
15
Using the Mail Merge Wizard







From the Word Menu: “Tools – Letters and
Mailings – Show Mail Merge Toolbar”
From the Word Menu: “Tools – Letters and
Mailings – Mail Merge Wizard”
From the Mail Merge Pane (Step 1 of 6):
Select document type of “Letters” and Click
“Next: Select document type” to continue.
From the Mail Merge Pane (Step 2 of 6):
Select starting document “Start from a
template” and Click Select Template.”
Select Template
“Contemporary Merge
Letter” and Create New
Document.
From the Mail Merge pane
Click “Next: Select
recipients” to continue.
(Step 3 of 6) Select
“Use an existing list”
and Click on “Browse”
16
Select an existing
Access Database





From Select Data Source set
“Files of type:” to “Access
Databases.”
Select the troop2003.mdb file
you created for requirement
4a.
Select Table “Addresses by
Name” from the list.
Mail Merge Recipients, keep
all records selected and Click
on “OK.”
In the Mail Merge Pane it will
show “Currently, your recipients
are selected from: [Addresses
by Name] in *.mdb”
17
Write your Letter







From the Word Menu: “Tools – Letters and
Mailings – Show Mail Merge Toolbar”
From the Word Menu: “Tools – Letters and
Mailings – Mail Merge Wizard”
From the Mail Merge Pane (Step 1 of 6):
Select document type of “Letters” and Click
“Next: Select document type” to continue.
From the Mail Merge Pane (Step 2 of 6):
Select starting document “Start from a
template” and Click Select Template.”
Select Template
“Contemporary Merge
Letter” and Create New
Document.
From the Mail Merge pane
Click “Next: Select
recipients” to continue.
(Step 3 of 6) Select
“Use an existing list”
and Click on “Browse”
18
Create the Database





In the Create Data Source dialog
box, select the field in “Field names
in header row:” and click on the
“Remove Field Name” button to
remove JobTitle, Company,
Country, HomePhone, and
WorkPhone from the table
Save the document as Troop
List.doc
Next, click on Edit Data Source
In the Data Form, add the Title
“Parents of” (to all records) and fill
in a complete name and address
for yourself, then click on OK
Click on “Add New” to add three
more records for imaginary boys in
your troop (use the same Title:
“Parents of” in each record)
19
Add merge fields to the Letter


In the Letter, change the “Company Name Here”
heading to be “Troop (your Troop number)” and fill in
the line that reads “[Click here and fill in return
address]” to your own or your troop’s address
Click on the “[Click here and type recipient’s
address]” line and using the Mail Merge toolbar
“Insert Merge Fields” pull-down, add the fields:








Title space FirstName space LastName
Address1
Address2
City comma space State space PostalCode
Change the line that reads “[Click here and type your
name]” to your full name
Change the line that reads “[Click here and type job
title]” to your Troop position, such as “Troop Scribe”
Change the line “[Click here and type slogan]” to
THE slogan
The result should look like the example shown to
the right
20
Do the Merge!






Test the merge fields by clicking on
the <<ABC>> button in the Mail
Merge Toolbar and using the arrows
to move through the records; the
merged address should display:
SAVE THE DOCUMENT
Click in the Mail Merge Toolbar on
the “Check for Errors” button, select
“Complete the merge, pausing to
report each error as it occurs.” and
click on OK
Review the new document created
as “Form Letters1” that should have
four separate pages, one addressed
to each family.
SAVE, THEN PRINT THIS
DOCUMENT
Turn a complete set of merged
letters in to the Instructor
21
The
Resulting
Merged
Letters
should
look a lot
like
this…
22
d. Use a computer graphics
program…

Design and draw a campsite plan for your
troop.

Show the major elements of a campsite:






Tents
Cooking areas
Wood lot (Axe yard)
Water source
Terrain features
Entrance, etc.
23
Primitive Example with Paint

Simple, but it
shows all of the
major elements
of a campsite:
 Wood lot
 Water pipe
 Cooking
area and fire
ring
 Bridge over
creek
 Forest
 Tents
 Entrance

ADD YOUR
TROOP # AND
NAME TO IT
24
Set to Landscape and Print






Did you remember to add your Troop
Number and Name to the drawing???
From the menu bar select “File – Print”
In the Print dialog box select the “Layout” tab
Change the “Orientation” to “Landscape”
Click on the “Print” button
Turn the printout in to your instructor
25
Campsite
Plan
done in
Microsoft
Visio
Tree
Tree
Tree
Tree
Tree
Tree
Tree
Tree
Tiger Patrol
Geezer Patrol
Park
Park
Duct Tape Patrol
Park
Cooking Area
Axe Yard
TROOP 132 BSA
N
Jamie Jones
Troop 132
26