. 6
( 14)


Tip You can move the Field List window by simply clicking on the title bar and dragging it to a new
Chapter 8 ¦ Understanding and Creating Access Reports 197

Selecting the fields for your report
Selecting a field in the Report field list is the same as selecting a field in the Query field list.
The easiest way to select a field is simply to click it. When you click a field, it becomes
highlighted. After a field is highlighted, you can drag it to the Report window.
You can highlight contiguous (adjacent) fields in the list by following these steps:
• Click the first field you want in the field list.
• Move the mouse pointer to the last field you want from the list.
• Hold down the Shift key and click the last field you want.
The block of fields between the first and last field you selected is displayed in reverse video,
indicating it is selected. You can then drag the block of fields to the Report window.
You can highlight noncontiguous fields in the list by clicking each field while holding down
the Ctrl key. Each selected field will be displayed in reverse video; then you can drag the
fields as a group to the Report Design window.

Unlike the Query field list, you cannot also double-click a field to add it to the Report window.

You can begin by selecting the tblProducts fields for the detail section. To select the fields
needed for the detail section of the Product Display report, follow these steps:
1. Click the chrProductID field.
2. Hold down the Shift key and click the curSalePrice field.
The block of fields from chrProductID to curSalePrice should be highlighted in the
Field List window, as shown in Figure 8-20.
3. Hold down the Ctrl key and click the memFeatures field and the olePicture field.
Holding down the Ctrl key lets you select noncontiguous fields. You should have
two blocks of field1.

Dragging fields onto your report
After you select the proper fields from the tblProducts table, all you need to do is drag them
to the detail section of your report. Depending on whether you choose one or several fields,
the mouse pointer changes shape to represent your selection. If you select one field, you see
a Field icon, which shows a single box with some unreadable text inside. If you select
multiple fields, you see a set of three boxes. These are the same icons you saw when you
were using the Query Design screens.
To drag the selected tbProducts table fields into the detail section of the Report Design
window, follow these steps:
1. Click within the highlighted block of fields in the Field List window. You may need
to move the horizontal scroll bar back to the left before starting this process.
Part I ¦ Getting Functional with Office 2003

2. Without releasing the mouse button, drag the mouse pointer into the detail
section; place the icon under the 1½-inch mark on the horizontal ruler at the top
of the screen and next to the ½-inch mark of the vertical ruler along the left edge
of the screen.
3. Release the mouse button.
The fields appear in the detail section of the report, as shown in Figure 8-21. Notice that for
each field you dragged onto the report, there are two controls. When you use the drag-and-
drop method for placing fields, Access automatically creates a label control with the field
name attached to the text control to which the field is bound.

Notice the Bound Object Frame control for the field named Picture. Access always creates a
Bound Object Frame control for an OLE-type object found in a table. Also notice that the detail
section automatically resizes itself to fit all the controls. Above the Bound Object Frame control
is the control for the memo field Features.

You also need to place the desired field controls for the customer information you need in
the page header section. Before you do this, however, you need to resize the page header
frame to leave room for a title you will add later.

Resizing a section
To make room on the report for the title information in the page header, you must resize it.
You can resize a section by placing the mouse pointer at the bottom of the section you want
to resize. The pointer turns into a vertical double-headed arrow; drag the section border up
or down to make the section smaller or larger.
Resize the page header section to make it larger by following these steps:
1. Move the mouse pointer between the bottom of the page header section and the top
of the detail section.
2. When the pointer is displayed as a double-sided arrow, hold down the left mouse
3. Drag the page header section border down until it intersects the detail section™s
ruler at the ¾-inch mark.
4. Release the button to enlarge the page header section.
The page header section expanded to fit the fields that were dragged into the section. All the
fields needed for the Product Display report are now placed in their appropriate sections.

Working with unattached label controls and text
When you drag a field from the Field List window to a report, Access creates not only a data
control but also a label control that is attached to the data control. At times, you will want to
add label controls by themselves to create headings or titles for the report.
Chapter 8 ¦ Understanding and Creating Access Reports 199

Creating unattached labels
To create a new, unattached label control, you must use the Toolbox (unless you copy
an existing label). The next task in the current example is to add the text headers Product
Display and Access Auto Auctions to your report. This task demonstrates adding and
editing text.
To begin creating an unattached label control, follow these steps:
1. Display the Toolbox.
2. Click the Label tool in the Toolbox.
3. Click near the top-left edge of the page header at about the 1/8-inch mark on the
ruler; then drag the mouse pointer downward and to the right to make a small
rectangle about 2½ inches wide and ½-inch high.
4. Type Product Display.
5. Press Enter.
Repeat the process for the label Access Auto Auctions and place it just below the
Product Display label, as shown in Figure 8-22. As you create these label rectangles, it
may make the Page Header section expand.

Tip To create a multiple-line label entry, press Ctrl+Enter to force a line break where you want it in
the control.

If you want to edit or enter a caption that is longer than the space in the property window, the
contents will scroll as you type. Otherwise, open a Zoom box that gives you more space to type
by pressing Shift+F2.

Modifying the appearance of text in a control
To modify the appearance of the text in a control, select the control by clicking its border
(not in the control itself). You can then select a formatting style to apply to the label by
clicking the appropriate button on the Formatting toolbar.
To make the titles stand out, follow these steps to modify the appearance of label text:
1. Click the newly created report heading label Product Display.
2. Click the Bold button on the Formatting toolbar.
3. Click the arrow beside the FontSize drop-down box.
4. Select 18 from the FontSize drop-down list box.
5. Repeat for the Access Auto Auctions label, using a 12 pt font and Bold.
Figure 8-22 shows these labels added, resized, and formatted.
Part I ¦ Getting Functional with Office 2003

Currently, the label rectangles are much large than their displayed text. To tighten the
display or to display all the text when a label rectangle isn™t big enough, you can simply
double-click the bottom left corner handle to resize it (which you will learn more about later
in this chapter).

Figure 8-22: Adding unbound labels to the report.

Working with text boxes and their attached
label controls
So far, you have added controls bound to fields in the tables and unbound label controls used
to display titles in your report. There is another type of text box control that is typically
added to a report: unbound text boxes that are used to hold expressions such as page
numbers, dates, or a calculation.

Creating and using text box controls
In reports, text box controls serve two purposes. First, they enable you to display stored data
from a particular field in a query or table. Second, they display the result of an expression.
Expressions can be calculations that use other controls as their operands, calculations that
use Access functions (either built-in or user-defined), or a combination of the two. You have
learned how to use a text box control to display data from a field and how to create that
control. Next, you learn how to create new text box controls that use expressions.
Chapter 8 ¦ Understanding and Creating Access Reports 201

Entering an expression in a text control
A function is a small program that, when run, returns a single value. The function can be one
of many built-in Access functions or it can be user-defined. For example, to facilitate page
numbering in reports, Access has a function called Page that returns the value of the current
report page. The following steps show you how to use an unbound text box to add a page
number to your report:
1 Click in the middle of the page footer section, resize the page footer so that it is ½-
inch in height, and then create a text box about three-quarters of the height of the
section and about ½-inch wide by resizing the default text box control.
2. Select the Text Box tool on the Toolbox.
3. Scroll down to the page footer section by using the vertical scroll bar.
4. Click the label control to select it. (It should say something similar to Text38.)
5. Click the beginning of the label control text, drag over the default text in the label
control, and type Page: or double-click the text to highlight it and then replace it.
6. Click twice on the text box control (it says “Unbound”); type =Page and press
Enter. (Notice that the Control Source property changes on the data sheet of the
Property window to =[Page]. If the Property window is not open, you may want to
open it to see the change.)
7. Click the Page label control™s Move handle (upper-left corner); move the label
closer to the =[Page] text box control until the right edge of the label control
touches the left edge of the text box control.
Although this is a good exercise for creating labels and text boxes, a better way to add a
page number in the Page Footer section is to use the automatic Page numbers dialog box. To
do this, follow the steps below:
1. Delete the text box you created in the last example from the Page Footer section.
2. Select Insert_Page Numbers¦ from the main menu.
The Page Numbers dialog box is displayed
3. Change the Format to Page N of M.
4. Change the Position to Bottom of Page [Footer].
5. Change Alignment to Right.
Format lets you choose between the final text Page N, where N is the page
number, or Page N of M, where N is the current page number and M is the total
number of pages in the report. It is recommended to always use Page N of M to
make sure the report isn™t missing any pages (or the last page). Position lets you
determine if the page number expression is created in the Page Header or Page
Footer. Alignment lets you determine if the text will be left, right, or centered
aligned. Because this text expression is going to be placed at the bottom right
corner of the report, the Right alignment is preferred. There is also a check box
Part I ¦ Getting Functional with Office 2003

that can be unchecked and lets you eliminate the page number from the first page
(if it were to be used as a cover page).
The completed text box expression looks like this:
=”Page “ & [Page] & “ of “ [Pages]
This would display Page 5 of 25 if page 5 was the current page and there were 25
pages in the report.
The = sign begins an expression. The & symbol (known as concatenation) joins
keywords, fields, or other expressions to a text string. Text strings are surrounded
by double quotes. [Page] and [Pages] are keywords and are surrounded (known as
delimited) by braces ([ ]). Notice the “Page “ text contains a trailing space. This is
done so that there will be a space between the text Page and the current page
number. Notice that there are both leading and trailing spaces in the text string “of.”
Again, this separates the page numbers by a space from the word “of.”

Tip You can always check your result by clicking the Print Preview button on the toolbar and zoom-
ing in on the page footer section to check the page number.

Sizing a text box control or label control
You can select a control by simply clicking it. Depending on the size of the control, from
three to seven sizing handles will appear ” one on each corner except the upper-left corner
and one on each side. When you move the mouse pointer over one of the sizing handles, the
pointer changes into a double-headed arrow. When the pointer changes, click the control and
drag it to the size you want. Notice that, as you drag, an outline appears; it indicates the new
size that the label control will be when you release the mouse button.
If you double-click any of the sizing handles, Access resizes a control to the best fit for the
text in the control. This feature is especially handy if you increase the font size and then
notice that the text is cut off, either on the bottom or to the right. Note that for label controls,
this best-fit sizing resizes both vertically and horizontally, though text controls can resize
only vertically. The reason for this difference is that in the report design mode, Access
doesn™t know how much of a field you want to display; the field name and field contents
might be radically different. Sometimes label controls are not resized correctly, however,
and have to be adjusted manually.

Changing the size of a label control
Earlier in this chapter (in the steps that modified the appearance of label text), you changed
the characteristics of the Product Display label; the text changed, but the label itself did not
adjust. The text no longer fits well within the label control. You can resize the label control,
however, to fit the enhanced font size by following these steps:
1. Click the Product Display label control.
2. Move your mouse pointer over the control. Notice how the pointer changes shape
over the sizing handles.
Chapter 8 ¦ Understanding and Creating Access Reports 203

3. To size the control automatically, double-click one of the sizing handles. The label
control size may still need to be readjusted.
4. Place the pointer in the bottom-right corner of the label control so that the diagonal
double-arrow appears.
5. Hold down the left mouse button and drag the handle to resize the label control
until it correctly displays all of the text (if it doesn™t already).

You can also select Format_Size_To Fit to change the size of the label control text

Before continuing, you should check how the report is progressing. You should do this
frequently as you create a report. You should also save the report frequently as you make
changes to it. You can send a single page to the printer or view the report in print preview.
Figure 8-25 is a zoomed print preview of how the report currently looks. The customer
information is at the top of the page; the pet information is below that and offset to the left.
Notice the title at the top of the page. You can see the page number at the bottom if you click
the magnifying glass button to zoom out and see the entire page. Only one record per page
appears on the report because of the vertical layout. In the next section, you move the fields
around and create a more horizontal layout.

Figure 8-23: A print preview of the report.
Part I ¦ Getting Functional with Office 2003

Deleting and Cutting attached labels from text controls
In order to create the report shown in Figure 8-19, you must remove the label controls from
several of the text box controls and place the label controls in the page header section.
It™s very easy to delete one or more attached controls in a report. Simply select the desired
controls and press Delete. However, if you want to move the label to the page header
section, you can cut the label instead of deleting it. When removing attached controls, there
are two choices:
• Delete only the label control.
• Cut the label control to the clipboard.
• Delete or cut both the label control and the field control.
If you select the label control and press Cut (Ctrl-X) or the Delete key, only the label control
is removed. If you select the field control and press Cut or Delete, both the label control and
the field control are removed. To cut an attached label control (in this case, the Product ID
controls and their attached label), follow these steps:
1. Click the Close button on the toolbar to exit print preview mode. Select the Product
ID label control only in the detail section.
2. Press Ctrl-X (Cut).
After you have cut the label, you may want to place it somewhere else. In this
example, you will want to place it into the Page Header section.

Pasting labels into a report section
It is probably just as easy to cut labels from controls placed in the detail section and paste
them into the Page Header as it is to just delete the labels and create new ones in the Page
Header. Regardless, you will now paste the label you have cut in the previous steps:
1. Click anywhere in or on the Page Header section.
2. Press Ctrl-V (Paste).
The Product ID label appears in the Page Header.
3. Repeat for the Description, Category, and Quantity in Stock labels.
4. Delete the remaining label controls in the detail section, leaving all of the text box
If you accidentally selected the data field control and both controls are cut or deleted,
press the Undo toolbar button to undo the action.

If you want to delete only the field control and keep the attached label control, first select the
label control and then select Edit_Copy. Next, to delete both the field control and the label
control, select the field control and press Delete. Finally, select Edit_Paste to paste only the
copied label control to the report.
Chapter 8 ¦ Understanding and Creating Access Reports 205

Moving label and text controls
Before discussing how to move label and text controls, it is important to review a few
differences between attached and unattached controls. When an attached label is created
automatically with a text control, it is called a compound control. In a compound control,
whenever one control in the set is moved, the other control moves as well. With a text
control and a label control, whenever the text control is moved, the attached label is also
moved. Likewise, whenever the label control is moved, the text control is also moved.
To move both controls in a compound control, select one of the pair by clicking the control.
Move the mouse pointer over either of the objects. When the pointer turns into a hand, click
the controls and drag them to their new location. As you drag, an outline for the compound
control moves with your pointer.
To move only one of the controls in a compound control, drag the desired control by its
Move handle (the large square in the upper-left corner of the control). When you click a
compound control, it looks like both controls are selected, but if you look closely, you see
that only one of the two controls is selected (as indicated by the presence of both moving
and sizing handles). The unselected control displays only a moving handle. A pointing
finger indicates that you have selected the Move handles and can now move only one
control. To move either control individually, select the control™s Move handle and drag it to
its new location.

Tip To move a label that is not attached, simply click any border (except where there is a handle)
and drag it.

To make a group selection, click with the mouse pointer anywhere outside a starting point
and drag the pointer through (or around) the controls you want to select. A gray, outlined
rectangle is displayed that shows the extent of the selection. When you release the mouse
button, all the controls that the rectangle surrounds are selected. You can then drag the group
of controls to a new location.

The global option Tools_Options ” Forms/Reports tab ” Selection Behavior is a property
that controls the enclosure of selections. You can enclose them fully (the rectangle must com-
pletely surround the selection) or partially (the rectangle must only touch the control), which is
the default.

Make sure you also resize all of the controls as shown in the figure. The memo field
memFeatures and the OLE picture field olePicture must also be changed in both size
and shape.
Place all of the controls in their proper position to complete the report layout. You want this
first pass at rearranging the controls to look like the example shown in Figure 8-24. You will
make a series of block moves by selecting several controls and then positioning them close
to where you want them. Then, if needed, you fine-tune their position. This is the way most
reports are done.
Part I ¦ Getting Functional with Office 2003

Follow Figure 8-24 to begin placing the controls where they should be. You may want to
notice that the control labels in the Page Header section have been underlined. Also notice
the new label Cost/Retail/Sale Prices in the Detail section.

Figure 8-24: Rearranging the controls on the report.

At this point, you are about halfway done. The screen should look like the one shown in
Figure 8-24. (If it doesn™t, adjust your controls until your screen matches the figure.)
Remember that these screen pictures are taken with the Windows screen driver set at 1024 x
768. If you are using 800 x 600, 640 x 480, or large fonts, you™ll have to scroll the screen to
see the entire report.
These steps complete the rough design for this report. There are still properties, fonts, and
sizes to change. When you make these changes, you™ll have to move fields around again.
Use the designs in Figure 8-19 only as a guideline. How it looks to you, as you refine the
look of the report in the Report window, determines the real design.

Modifying the appearance of multiple controls
The next step is to format all the label controls in the Page Header section directly above the
section separator to be underlined. The following steps guide you through modifying the
appearance of text in multiple label controls:
1. Select all label controls in the bottom of the Page Header section by individually
clicking them while holding down the Shift key. There are four label controls to
select, as shown in Figure 8-24.
You could also have placed your cursor in the vertical ruler at about 1.25 inches
and, when it changed to a right-pointing bold arrow, clicked the mouse to select all
the controls in that horizontal area of the report.
2. Click the Underline button on the toolbar.
Chapter 8 ¦ Understanding and Creating Access Reports 207

You could also have selected all the label controls in the preceding steps by using the drag-and-
surround method.

After you make the final modifications, you are finished, except for fixing the picture
control. To do this, you need to change properties, which you do in the next section. This
may seem to be an enormous number of steps because the procedures were designed to show
you how laying out a report design can be a slow process. Remember, however, that when
you click away with the mouse, you don™t realize how many steps you are doing as you
design the report layout visually. With a WYSIWYG (What You See Is What You Get)
layout like that of the Access report designer, you may need to perform many tasks, but it™s
still easier and faster than programming. Figure 8-24 shows the final version of the design
layout as seen in this chapter.

Changing label and text box control properties
To change the properties of a text or label control, you need to display the control™s property
sheet. If it is not already displayed, perform one of these actions to display it:
¦ Double-click the border of the control (anywhere except a sizing handle or Move
¦ Click the Properties button on the toolbar.
¦ Select View_Properties.
¦ Right-click the mouse and select Properties.
The property sheet enables you to look at a control™s property settings and provides an easy
way to edit the settings. Using tools such as the formatting windows and text-formatting
buttons on the Formatting toolbar also changes the property settings of a control. Clicking
the Bold button, for example, really sets the Font Weight property to Bold. It is usually
much more intuitive to use the toolbar (or even the menus), but some properties are not
accessible this way. In addition, sometimes objects have more options available through the
property sheet.
The Size Mode property of an OLE object (bound object frame), with its options of Clip,
Stretch, and Zoom, is a good example of a property that is available only through the
property sheet.
The Image control, which is a bound object frame, presently has its Size Mode property set
to Clip, which is the default. With Clip, the picture is displayed in its original size and may
be too large to fit in the frame. In this exercise, you will change the setting to Stretch so that
the picture is sized automatically to fit the picture frame.
To change the property for the bound object frame control that contains the picture, follow
these steps:
1. Click the frame control of the picture bound object.
2. Click the Size Mode property.
Part I ¦ Getting Functional with Office 2003

3. Click the arrow to display the drop-down list box.
4. Select Stretch.
These steps complete the changes so far to your report. A print preview of the first few
records appears in Figure 8-25. If you look at the pictures, notice how the picture is properly
displayed and the Features field now appears across the bottom of the detail section. The
labels are all underlined.

Figure 8-25: The report print preview.

Growing and shrinking text box controls
When you print or print-preview fields that can have variable text lengths, Access provides
options for enabling a control to grow or shrink vertically, depending on the exact contents
of a record. The option Can Grow determines whether a text control adds lines to fit
additional text if the record contains more lines of text than the control can display. The
option Can Shrink determines whether a control deletes blank lines if the record™s contents
use fewer lines than the control can display. Although these properties are usable for any
text field, they are especially helpful for memo field controls like the Features control.
Table 8-3 explains the acceptable values for these two properties.
Chapter 8 ¦ Understanding and Creating Access Reports 209

Table 8-3
Text Control Values for Can Grow and Can Shrink
Property Value Description
Can Grow Yes If the data in a record uses more lines than the control is defined
to display, the control resizes to accommodate additional lines.
Can Grow No If the data in a record uses more lines than the control is defined
to display, the control does not resize; it truncates the data
Can Shrink Yes If the data in a record uses fewer lines than the control is
defined to display, the control resizes to eliminate blank lines.
Can Shrink No If the data in a record uses fewer lines than the control is
defined to display, the control does not resize to eliminate blank

To change the Can Grow settings for a text control, follow these steps:
1. Select the Features text box control.
2. Display the Property window.
3. Click the Can Grow property; then click the arrow and select Yes.

Note The Can Grow and Can Shrink properties are also available for report sections. Use a section™s
property sheet to modify these values.

The report is starting to look good, but you may want to see groups of like data together and
determine specific orders of data. In order to do this, you will use sorting and grouping.

Sorting and grouping data
Sorting enables you to determine the order in which the records are viewed in a datasheet,
form, or report, based on the values in one or more fields. This order is important when
you want to view the data in your tables in a sequence other than that of your input. For
example, new products are added to the tblProducts table as they are needed on an
invoice. The physical order of the database reflects the date and time a product is added.
Yet, when you think of the product list, you probably expect it to be in alphabetical order
by Product ID, and you want to sort it by Description of the cost of the product. By
sorting in the report itself, you don™t have to worry about the order of the data. Although
you can sort the data in the table by the primary key or in a query by any field you want, it
is more advantageous to do it in the report. This way, if you change the query or table, the
report is still in the correct order.
You can take this report concept even further by grouping ” that is, breaking related records
into groups. Suppose that you want to list your products first by Category and then by
Part I ¦ Getting Functional with Office 2003

Description within each Category group. To do this, you must use the Category and
Description fields to sort the data. Groupings that can create group headers and footers are
sometimes called control breaks because changes in data trigger the report groups.
Before you can add a grouping, however, you must first define a sort order for at least one
field in the report using the Sorting and Grouping dialog box, which is shown completed in
Figure 8-26. In this example, you use the Category field to sort on first and then the
Description field as the secondary sort.
To define a sort order based on Category and Description, follow these steps:
1. Click the Sorting and Grouping button on the toolbar to display the Sorting and
Grouping box.
2. Click in the first row of the Field/Expression column of the Sorting and Grouping
box. A downward-pointing arrow appears.
3. Click the arrow to display a list of fields in the tblProduct table.
4. Select chrCategory in the field list. Notice that Sort Order defaults to Ascending.
5. Click in the second row of the Field/Expression column.
6. Click the arrow to display a list of fields in the tblProduct table.
7. Select chrDescription in the field list. Notice that Sort Order defaults to Ascending.

Figure 8-26: The Sorting and Grouping box completed.

To see more of the Field/Expression column, drag the border between the Field/Expression and
Sort Order columns to the right.
Chapter 8 ¦ Understanding and Creating Access Reports 211

You can also drag a field from the Field List window into the Sorting and Grouping box Field/
Expression column rather than enter a field or choose one from the field list in the Sorting and
Grouping box Field/Expression column.

Although in this example you used a field, you can alternatively sort (and group) by using
an expression. To enter an expression, click in the desired row of the Field/Expression
column and enter any valid Access expression, making sure that it begins with an equal sign,
as in =[curRetailPrice]-[curCost].
To change the sort order for fields in the Field/Expression column, simply click the Sort
Order column and click the down arrow to display the Sort Order list; then select

Creating a group header or footer
Now that you have added instructions to sort by the Category and Description, you will also
need to create a group header for Category to group all of the products by category. You
don™t need a group footer in this example because there are no totals by category or other
reasons to use a group footer.
To create a group header that enables you to sort and group by the chrCategory field, follow
these steps:
1. Click the Sorting and Grouping button on the toolbar if the Sorting and Grouping
box is not displayed. The field chrCategory should be displayed in the first row
of the Sorting and Grouping box; it should indicate that it is being used as a sort
in Ascending order.
2. Click on the chrCategory row in the Field/Expression column.
3. Click the Group Header property in the bottom pane; an arrow appears.
4. Click the arrow on the right side of the text box; a drop-down list appears.
5. Select Yes from the list. (A header section bar appears on the report.)
After you define a header or footer, the row selection bar changes to the grouping symbol
shown in Figure 8-26. This is the same symbol as in the Sorting and Grouping button on the
toolbar. Figure 8-26 shows both the grouping row symbol and the newly created report
section. The chrCategory header section appears between the page header and detail
sections. If you define a group footer, it appears below the detail section. If a report has
multiple groupings, each subsequent group becomes the one closest to the detail section. The
groups defined first are farthest from the detail section.
The Group Properties pane (displayed at the bottom of the Sorting and Grouping box)
contains these properties:
¦ Group Header. Yes creates a group header. No removes the group header.
¦ Group Footer. Yes creates a group footer. No removes the group footer.
Part I ¦ Getting Functional with Office 2003

¦ Group On. Specifies how you want the values grouped. The options you see in
the drop-down list box depend on the data type of the field on which you™re
grouping. If you group on an expression, you see all the options. Group On has
more choices to make.
For Text data types, there are two choices:
¦ Each Value. The same value in the field or expression.
¦ Prefix Characters. The same first n number of characters in the field.
For Date/Time data types, there are additional options:
¦ Each Value. The same value in the field or expression.
¦ Year. Dates in the same calendar year.
¦ Qtr. Dates in the same calendar quarter.
¦ Month. Dates in the same month.
¦ Week. Dates in the same week.
¦ Day. Dates on the same date.
¦ Hour. Times in the same hour.
¦ Minute. Times in the same minute.
Currency, or Number data types provide three options:
¦ Each Value. The same value in the field or expression.
¦ Interval. Values falling within the interval you specify.
¦ Group Interval. Specifies any interval that is valid for the values in the field or
expression you™re grouping on.
The Group Interval has its own options which include:
• Keep Together. This option controls what™s known as widows and orphans in the
word processing world so that you don™t have a header at the bottom of a page
with no detail until the next page.
• Whole Group. Prints header detail and group footer on one page.
• With First Detail. Prevents the contents of the group header from printing
without any following data or records on a page.
• No. Do not keep together.

After you create the Category group header, you are done with the Sorting and Grouping box for
this report on the CD-ROM that accompanies the Access 2003 Bible. You may need to make
additional changes to groupings as you change the way a report looks; the following three
sections detail how to make these changes. You should not make any of these changes, how-
ever, if you are following the examples or you should press the Save icon now to save the form
in the current state and then discard the changes done to this form after this point.
Chapter 8 ¦ Understanding and Creating Access Reports 213

Changing the group order
Access enables you to easily change the Sorting and Grouping order without moving all the
individual controls in the associated headers and footers. Here are the general steps to
change the sorting and grouping order:
1. Click the selector bar of the field or expression you want to move in the Sorting and
Grouping window.
2. Click the selector again and hold down the left mouse button.
3. Drag the row to a new location.
4. Release the mouse button.

Removing a group header or footer
To remove a page or report header/footer section, use the View_Page Header/Footer and
View_Report Header/Footer toggles. To remove a group header or footer while leaving the
sorting intact, follow these steps:
1. In the Sorting and Grouping window, click the selector bar of the field or expres-
sion that you want to remove from the grouping.
2. Click the Group Header text box.
3. Change the value to No.
4. Press Enter.
To remove a group footer, follow the same steps, but click Group Footer in Step 2.
To permanently remove both the sorting and grouping for a particular field (and thereby
remove the group header and footer sections), follow these steps:
1. Click the selector of the field or expression you want to delete.
2. Press Delete. A dialog box appears asking you to confirm the deletion.
3. Click OK.

Hiding a section
Access also enables you to hide headers and footers so that you can break data into groups
without having to view information about the group itself. You can also hide the detail
section so that you see only a summary report. To hide a section, follow these steps:
1. Click the section you want to hide.
2. Display the section property sheet.
3. Click the Visible property™s text box.
4. Click the drop-down list arrow on the right side of the text box.
5. Select No from the drop-down list box.
Part I ¦ Getting Functional with Office 2003

Sections are not the only objects in a report that can be hidden; controls also have a Visible
property. This property can be useful for expressions that trigger other expressions.

Sizing a section
Now that you have created the group header, you might want to put some controls in the
section, move some controls around, or even move controls between sections. Before you start
manipulating controls within a section, you should make sure the section is the proper height.
To modify the height of a section, drag the border of the section below it. If, for example,
you have a report with a page header, detail section, and page footer, change the height of
the detail section by dragging the top of the page footer section™s border. You can make a
section larger or smaller by dragging the bottom border of the section. For this example,
change the height of the group header section to 3/8 inch with these steps:
1. Move your mouse pointer to the bottom of the chrCategory section. The pointer
changes to a horizontal line split by two vertical arrows.
2. Select the top of the detail section (which is also the bottom of the chrCategory
Header section).
3. Drag the selected band lower until three dots appear in the vertical ruler (3/8”).
The gray line indicates where the top of the border will be when you release the
mouse button.
4. Release the mouse button.

Moving controls between sections
You now want to move the chrCategory control from the Detail section to the chrCategory
Header section. You can move one or more controls between sections by simply dragging
the control with your mouse from one section to another or by cutting it from one section
and pasting it to another section. Follow the instructions below to move the chrCategory
control from the Detail section to the chrCategory section:
1. Select the chrCategory control in the Detail section.
2. Drag the chrCategory control up to the chrCategory Header section and drop it
close to the vertical ruler, as shown in Figure 8-27.
3. Release the mouse button.
4. Press the Underline button to underline the chrCategory control to further
highlight it as a group header. Sometimes, you might want to bold it or even
increase the font size.
You should now do the following steps to complete the report design:
1. Delete the Category label from the Page Header.
2. Move the chrProductID control and its associated label after the chrDescription
control and its associated label, as shown in Figure 8-27.
Chapter 8 ¦ Understanding and Creating Access Reports 215

3. Move the chrDescription control and its associated label to the left so that it starts
just to the right of the start of the chrCategory control in the chrCategory Header
By offsetting the first control in the Detail section slightly to the right of the start of
the control in the Group Header section, you show the hierarchy of the data
presented in the report. It now will show that each group of products is for the
category listed in the group header.
4. Lengthen the chrDescription control so that it approaches the chrProduct ID
When you are done, the report design should look like the one shown in Figure 8-27.
Figure 8-27 shows this property window and the completed report design.

Figure 8-27: Completing the Group Header section and setting a Page Break.

Adding page breaks
Access enables you to add page breaks based on group breaks; you can also insert forced
breaks within sections, except in page header and footer sections.
In some report designs, it™s best to have each new group begin on a different page. You can
achieve this effect easily by using the Force New Page property of a group section, which
enables you to force a page break every time the group value changes.
Part I ¦ Getting Functional with Office 2003

The four Force New Page property settings are listed below:
¦ None. No forced page break (the default).
¦ Before Section. Starts printing the current section at the top of a new page every
time there is a new group.
¦ After Section. Starts printing the next section at the top of a new page every time
there is a new group.
¦ Before & After. Combines the effects of Before Section and After Section.
To create the report you want, you will force a page break before the chrCategory group by
using the Force New Page property in the chrCategory header. To change the Force New
Page property on the basis of groupings, follow these steps:
1. Click anywhere in the chrCategory header.
2. Display the Property window.
3. Select the Force New Page property.
4. Click the drop-down list arrow on the right side of the edit box.
5. Select Before Section from the drop-down list box.

Tip Alternatively, you can create a Group footer and set its Force New Page property to After

Sometimes, you don™t want to force a page break on the basis of a grouping, but you
still want to force a page break. For example, you may want to split a report title across
several pages. The solution is to use the Page Break tool from the Toolbox; just follow
these steps:
1. Display the Toolbox.
2. Click the Page Break tool.
3. Click in the section where you want the page break to occur.

Be careful not to split the data in a control. Place page breaks above or below controls; do not
overlap them.

Making the Report Presentation Quality
As you near completion of testing your report design, you should also test the printing of
your report. Figure 8-28 shows a print preview of the first page of the Product Display
report. You can see six records displayed. There are a number of things still to do to
complete the report.
Chapter 8 ¦ Understanding and Creating Access Reports 217

Obviously, the Picture needs to be changed so that it displays all of each car. Currently, the
default Clip view is set. You will need to change that. But that is not the major problem. The
report is very boring, plain, and not something you want to give to anyone else. If your goal
is to just look at the data, this report is done. However, you need to do more before you are
really done.
Although the report has good data that is well organized, it is not of professional quality. To
make a report more visually appealing, you generally add some lines and rectangles,
possibly some special effects such as shadows or sunken areas if you have a background on
the report. You want to make sure sections have distinct areas separate from each other using
lines or color. Make sure controls aren™t touching each other (because text may eventually
touch if a value is long enough). Make sure text is aligned with other text above or below
and to the right or left.
In Figure 8-28, you can see some opportunities for professionalism.

Figure 8-28: Print previewing the data.
Part I ¦ Getting Functional with Office 2003

Adjusting the Page Header
In the Page Header are several large labels. They are too far apart. The column headers
are too small and just hanging there. They could be underlined and made one font size
larger. Access generally creates controls with 8 point fonts. These are great for screens but
awful for people to view in a hard copy report. When you create a Word document, the
default font size is 10 point. Most people change their default font size to 12 point
because it is more easily readable. You should look at your hard copy report and decide if
you need to issue magnifying glasses to people over 40. If so, you might want to enlarge
some of your fonts.
Column headers should also be underlined and the entire Page Header should be separated
from the Detail section by a line.
If you wanted to add some color to your report, you could make the report name a different
color. Be careful not to use too many colors unless you have a specific theme in mind. Most
serious business reports use one or two colors, and rarely more than three with the exception
of graphs and charts.
Figure 8-29 shows these changes. The Product Display label has been changed to a
reverse video blue background color with white foreground text. This is done by first
selecting the control and then selecting Blue for the background. They have also been
placed under each other and left aligned. The rectangle around each of the controls was
also properly sized by double-clicking on the controls lower-right corner (or by selecting
Format_Size_To Fit).
The column labels have been changed to 11 point text, bolded, and underlined. They were
also moved to be above the controls for which they are the column headers.
The next step is to add a nice thick line separating the Page Header section from the
chrCategory Group Header section. To draw this line, follow the steps below:
1. Select the Line tool in the toolbox.
2. Start the cursor near the far left side of the Page Header, just to the right and above
of the 1 inch mark on the vertical toolbar, as shown in Figure 8-29.
3. Hold down the Shift key and then hold the left mouse button down and drag the
mouse across the Page Header, releasing it just to the left of the 7 ½ inch mark.
The Shift key is held down in order to draw a perfectly horizontal line.
4. Select the line and select the number 2 pt line thickness from the line thickness icon
on the toolbar, or select the 2 pt Border Width property from the line™s Property
The line thickness icon should be next to the Border icon on the formatting toolbar.
Chapter 8 ¦ Understanding and Creating Access Reports 219

Figure 8-29: Adjusting controls in the Page Header.

Creating an expression in the Group Header
Figure 8-29 also shows that the chrCategory field has been replaced by an expression. If you
just place the value of the category in the Group Header section, it looks out of place and
may not be readily identifiable. Most data values should have some type of labels to identify
what they are.
The expression =”Category: “ & [chrCategory] will display the text
Category: followed by a space and then followed by the data value of the chrCategory
field. The & symbol (known as the concatenation symbol) joins a string to a data field.
Make sure you leave a space after the colon or the value will not be separated from the label.
The text control has been bolded, underlined, and the font point size increased as well.
There is one more very important task to complete. If you simply changed the chrCategory
text box to the expression and displayed the report, you would have seen an error in the Group
Header where the category expression would be. You must rename the control to something
other than the original name of the data field. The original control name was chrCategory and
that was also the control name. Under standard naming conventions, the control should have
been renamed txtCategory, but this may not have been done. When you create an expression
using the original text box control and you use the field name in the control, you will cause an
error. You cannot name a control the same name as any data field used within the expression
itself. This is a limitation of Access. See the Caution below for more information.

When you create a bound control, it often uses the name of the data field as the control name.
If you then change the control to an expression using the data field in the expression without
changing the name of your control, you will get a #Name or #Error when you display the control
on a form or report. You must rename the control to something other than the original field name.
Part I ¦ Getting Functional with Office 2003

Follow the steps below to complete the expression and rename the control:
1. Select the chrCategory control in the chrCategory Group Header section.
2. Display the Property window for the control.
3. Change the Control Source property to =”Category: “ & [chrCategory].
4. Change the Name property to txtCategoryDisplay.

Changing the picture properties and the Detail section
The Detail section is in fairly good shape. Make sure the Description control is slightly
indented from the Category expression in the Group Header. A label should be created, as
shown in Figure 8-30, which identifies the values in the Cost, Retail Price, and Sale Price
A line is also good to add to this Detail section to separate one record from another. This is
often done when there are multiple lines of a record displayed.
The next step is to add a nice thick line separating each record. Because you don™t want two
lines at the bottom of each page (you™ll be adding a line to the Page Footer next), you will
put this line at the top of the Detail section. To draw this line, follow the steps below:
1. Select the Line tool in the toolbox.
2. Start the cursor near the far left side of the Detail section, just to the right and above
the 1/8 inch mark on the vertical toolbar, as shown in Figure 8-30.
You may have to first move all of the controls down in the Detail section to do this.
3. Hold down the Shift key and then hold the left mouse button down and drag the
mouse across the Page Header, releasing it just to the left of the 7 ½ inch mark.
The Shift key is held down in order to draw a perfectly horizontal line.
4. Select the line and select the number 2 pt line thickness from the line thickness icon
on the toolbar or select the 2 pt Border Width property from the line™s Property
Normally, numeric fields are right aligned. Because they are next to each other horizontally
and not above each other vertically, they can be left aligned. Though the repeating groups of
records are above each other, they are separated by a wide space and left alignment is okay
One task to complete is to change the Picture control to make the picture fit within the
control and to add a shadow to dress up the picture and give it some depth. Follow the steps
below to complete these tasks:
1. Select the olePicture control in the Detail section.
2. Display the Property window for the control.
3. Change the Size Mode property to Stretch.
4. Select Shadowed from the Special Effect window.
Chapter 8 ¦ Understanding and Creating Access Reports 221

Creating a standard page footer
The Page Footer currently contains a page number control that you created earlier in this
chapter. A standard page footer is one that contains things you place at the bottom of all
your reports and that your users come to expect.
Although a Page n of m control is at the bottom, a date and time control would be nice as
well. Many times, you print off a copy of a report and then discover some bad data. You
correct the values, print off another copy, and discover you can™t tell them apart. Having a
print date and time solves this problem.
To create a date/time control, follow the steps below:
1. Select the TextBox control in the Toolbox.
2. Select the Page Footer section and create a text box control near the left edge.
A text box control should appear with an attached label.
3. Delete the attached label.
4. Display the property window for the control.
5. Enter =Now() into the text box™s Control Source property.
This displays the current date and time when the report is run. If you use the Date()
keyword, you would only get the current date and not the current time.
6. Select General Date from the control™s Format property.
7. Select Align Left text from the formatting toolbar for this control.
This control should have its text left aligned, but make sure the page number
control contains right-aligned text.
The last step is to move the controls down a little from the Page Footer section band and add
a line between the Page Header section band and these controls:
1. Select both the date and page number controls and move them down 1/8 inch.
2. While they are selected, press the Italic icon on the formatting toolbar.
An italicized page footer looks more professional.
3. Select the Line tool in the toolbox.
4. Start the cursor near the far-left side of the Page Footer, just to the right and above
the 1/8-inch mark on the vertical toolbar, as shown in Figure 8-30.
5. Hold down the Shift key and then hold the left mouse button down and drag the
mouse across the Page Header, releasing it just to the left of the 7 ½-inch mark.
The Shift key is held down in order to draw a perfectly horizontal line.
6. Select the line and select the number 2 pt line thickness from the line thickness
icon on the toolbar or select the 2 pt Border Width property from the line™s
Property window.
Part I ¦ Getting Functional with Office 2003

Your screen should look like the one shown in Figure 8-30. The Print Preview for
this report is shown in Figure 8-31.

Figure 8-30: Adjusting controls in the Detail and Page Footer sections.

If every even-numbered page is blank, you accidentally widened the report past the 8-inch
Caution mark. If you move a control to brush up against the right page-margin border or exceed it, the
right page margin increases automatically. When it is past the 8-inch mark, it can™t display the
entire page on one physical piece of paper. The blank page you get is actually the right side of
the preceding page. To correct this, make sure that all your controls are within the 8-inch right
margin; then drag the right page margin back to 8 inches.

Saving your report
After all the time you spent creating your report, you™ll want to save it. It is good practice to
save your reports frequently, starting as soon as you create them. This prevents the
frustration that can occur when you lose your work because of a power failure or human
error. Save the report as follows:
1. Select File_Save. If this is the first time you have saved the report, the Save As
dialog box appears.
2. Type a valid Access object name. For this example, type rptProductDisplayFinal.
3. Click OK.
Chapter 8 ¦ Understanding and Creating Access Reports 223

If you already saved your report, Access saves your file with no message about what it is up to.

Figure 8-31: Print Preview of the Final Products Summary Report.

¦ ¦ ¦

and Integrating . . . .

with Microsoft In This Part

Office 2003 Chapter 9
Building Integrated

Chapter 10
Integrating Outlook
with Other Applications

Chapter 11
his part is comprised of chapters that enable users of one
Comments, Reviewing, and
particular Office 2003 application to more effectively
Editing Control in Word
collaborate and integrate their efforts with coworkers and/or other
Chapter 12
Sharing Excel Data with
Other Applications

Chapter 13
Team Collaboration
on a Draft PowerPoint

Chapter 14
Integrating FrontPage with
Other Office Applications

Chapter 15
Exchanging Access Data
with Other Office

Chapter 16
on a Network

Chapter 17
Windows SharePoint
Services with Office System

. . . .

Documents . . . .

In This Chapter

Inserting objects from
other applications

E ach Office application is so powerful in its own right that you
Working with
can usually find some way to make it do whatever you want
embedded objects
it to. Forcing Excel to print a letter, however, or trying to make a
Word table work like a spreadsheet isn™t very efficient. That™s
Working with linked
where linked and embedded objects come in: You can use them to
create an Office document in one application that contains objects
you created in other applications. Not only that, you can configure
Other methods of
Office so that changes made to objects in their original
sharing data
applications are automatically reflected in the document in which
they all appear together.
Sharing data with XML
First, a couple of definitions:
¦ A linked object is one that appears in your Office
. . . .
document but isn™t really part of it: It™s stored somewhere
else. All that™s really included in your document is the
object™s name and location; when you display or print the
page that includes the linked object, Office fetches the
object from wherever it is and dutifully includes it. One
advantage of linking over embedding is that any changes
made to the object in the original program (e.g., Excel or
Word) will automatically be reflected in the Office
document in which it is included.
¦ An embedded object is created and edited with another
program, but all the data for it is contained within your
publication. Whereas a linked object has little effect on
the amount of disk space your publication takes up, an
embedded object may have a much greater effect.
Part II ¦ Collaborating and Integrating with Office 2003

Inserting Objects from Other Applications
There™s more than one way to insert an object created in another application into your current
Office document.

Copy and paste
One simple method to move an object from application to application is simply to copy and
paste it. For example, if you highlight a range of cells in an Excel spreadsheet, select
Edit_Copy, go to Word, and select Edit_Paste, the spreadsheet will be pasted into Word as a
Word table. The trouble with this is that you don™t actually have an Excel spreadsheet in the
Word document, which means you can™t manipulate the information in that object the way
you could before.

In Word, the Standard toolbar includes a button for creating an Excel spreadsheet. Click it and
Tip choose the number of rows and columns you want it to display, just as if you were adding a
Word table. (It™s really a full spreadsheet, by the way; if you decide later you need more rows
and columns, you can simply drag its corner or side handles to reveal more.)

Using Paste Special in Word
A better choice is to select Edit_Paste Special in Word. This opens the dialog box shown in
Figure 9-1. Choose the format in which you want to paste the object from the clipboard, and
then click OK. By default, Paste Special creates an embedded object, but you can make it a
linked object by choosing Paste Link.

Figure 9-1: The Paste Special dialog box lets you choose how an object created in
another application is pasted into the current one.
Chapter 9 ¦ Building Integrated Documents 229

Choosing a paste method
You have several ways to paste your copied object into the new application:
¦ Object. This creates an embedded or linked object, depending on whether you have
the Paste or Paste link radio button selected. If you want to be able to edit the object
using the tools of the application that created it, this is the choice to make.
¦ Text. You can insert many objects as either formatted (RTF) or unformatted text. If
it™s primarily the words in the object you™re interested in, choose one of these
¦ Picture. You can insert the object as a high-quality picture ” the equivalent of a
screenshot ” of itself, in Picture (Windows Metafile) (the best choice for high-
quality printers and also the one that takes up the least disk and memory space),
Bitmap, or Picture (Enhanced Metafile) format. The only editing you™ll be able to
do to the object if you make this choice is the kind of editing you can do to an
inserted piece of clip art: resizing, recoloring, and so on.
¦ HTML. This inserts the object in HTML format ” extremely useful if you™re
building a Web page.

Using the Insert Object command
You can also insert objects into Office applications by choosing Insert_Object from the
menu. This opens a dialog box similar to the one shown in Figure 9-2, from Word.

Figure 9-2: The Insert_Object command lets you insert a variety of objects created in
other programs into an Office application.
Part II ¦ Collaborating and Integrating with Office 2003

By default, the Create New tab is selected. Choose the type of object you want to insert
from the Object type list. Check the Display as icon box if you want to indicate the object
with an icon (which users must double-click in order to view the object). When you™ve
made your selection, click OK, and a new object of the type specified is embedded in your
Office document.
Clicking the Create from File tab changes the look of the dialog box to that shown in
Figure 9-3.

Figure 9-3: Use these tools in your Office application to create an embedded or linked
object that already exists as a separate file elsewhere.

Click Browse to locate the file you want to insert as a new object. By default, this will create
an embedded object, but you can make it a linked object by checking the Link to file box.
Although Paste Special and Insert Object can be used to accomplish the same ends, Insert
Object has the advantage of being able to create new objects of specific types as well as
create objects from existing files without your having to first open those files and copy their
contents, as Paste Special requires.

Working with Embedded Objects
Once you™ve inserted an embedded object into an Office document, it appears to be part of
the document. But there™s a big difference: If you click the object once, you can move it
around and possibly resize it, but you can™t edit it. To do that, you have to double-click it.
When you do, the menus and controls of the current application change to those of the
application that created the object, so you can use the controls of the object™s native
application to edit it.
Chapter 9 ¦ Building Integrated Documents 231

Figure 9-4 and 9-5 illustrate this concept. Figure 9-4 shows an embedded object, part of an
Excel worksheet, as it looks embedded in a Word document; Figure 9-5 shows what it looks
like when you double-click the embedded worksheet to edit it.

Figure 9-4: This embedded Excel spreadsheet looks pretty much like an ordinary
Word table . . .
Part II ¦ Collaborating and Integrating with Office 2003

Figure 9-5: . . . but double-clicking it reveals its Excel roots ” and Excel controls.

Working with Linked Objects
Linked objects, like embedded objects, look like they™re part of your Office document ” but
they really aren™t. They™re simply displayed in it. They really still live somewhere else,
associated with the program that created them. (They™re a bit like graphics displayed on a
Web page in that regard; what you really see is a graphic that™s been called up from a different
location, not something that™s an integral part of the Web page, which, after all, is really only
a text file marked with HTML tags.)
If you™re working with dynamic data that changes all the time, linked objects are great,
because it doesn™t matter if someone changes some figures in the Excel spreadsheet you™ve
linked to on page three of your report ” the link, which, by default, is updated every time
you open the document, ensures that your report reflects those changes.

Linked objects require two documents in two different files ” the source document and the
destination document. If you want to send a document containing linked objects to someone
else, you also have to send the source document for those objects ” and make sure that the
recipient stores the source document in exactly the same drive and file folder as you had it
stored. If the source document isn™t where the destination document expects it to be, the link
won™t work.
Chapter 9 ¦ Building Integrated Documents 233

Moving and resizing linked objects
You can move or resize a linked object just as you can move or resize an embedded object.
You can also edit it in its source application by double-clicking it, with one difference: When
you double-click an embedded object, the menus and toolbars of the originating program are
displayed in the destination document™s application. Double-clicking a linked object opens
the source document in the originating application: In the case of the previous example, it
would open the source document in Excel in a new window.

Editing and updating links
If you have a lot of linked objects in the same document, the easiest way to work with them is
to choose Edit_Links. This opens a dialog box similar to the one shown in Figure 9-6. (Its
appearance varies slightly among the various Office applications.)

Figure 9-6: Edit your links using these controls.

The list box includes all the linked objects in the current document (in this case, only one).
Down the right side are additional controls:
¦ Update Now updates the linked object in the destination document to match the
¦ Open Source opens the source file in its originating application.
¦ Change Source lets you browse your computer for a different source file. Obviously,
changing source files is likely to completely change the appearance of your current
document. You can also use Change Source to find a source file that has been
relocated, thus repairing the severed link.
¦ Break Link turns the linked object into a picture, severing its connection with the
source file.
You can also choose to either automatically update the linked object whenever you open the
destination document or whenever the source file changes, or you can choose to update the
linked object only when you click Update Now.
Part II ¦ Collaborating and Integrating with Office 2003

Using the Locked and Save picture options
Some applications include two additional options in this dialog box: Locked and Save Picture
in Document. If Locked is available, you can select it to deactivate the Update Now button
and prevent the linked object from being updated automatically. You might do this to freeze
the data in your document at a particular point in time.
Save picture in document is normally checked. If you uncheck it, you can save a graphic as a
linked object instead of inserting it into your document. This can save disk space.

Other Methods of Sharing Data
The four main Office applications (Word, Excel, PowerPoint, and Access) offer
additional ways to share data. You™ll look at collaborating on a network (including the
Internet) in a separate chapter, but there are several other ways in which Office
applications work together.

For a full explanation of how you can collaborate on a network with Office applications, see
Chapter 16 of this Super Bible eBook.

Sending a Word document to PowerPoint
Word lets you send the currently active document to PowerPoint as the basis of a new
presentation. It automatically turns each paragraph of the document into a new PowerPoint
slide (see Figures 9-7 and 9-8), which you can then edit and format as you wish in
PowerPoint. To send a document to PowerPoint, choose File_Send To_Microsoft
You can reduce the amount of formatting you™ll have to do in PowerPoint by using styles.
PowerPoint will interpret each Heading 1 style as a title slide, each Heading 2 style as the
next level of text, and so on. For that reason, a Word outline actually makes a better
PowerPoint presentation than a Word document consisting of long paragraphs of text.
Chapter 9 ¦ Building Integrated Documents 235

Figure 9-7: This ordinary Word document can be sent to PowerPoint . . .

Figure 9-8: . . . where it becomes a presentation in which each paragraph forms a new
slide (although obviously some formatting work is needed!).
Part II ¦ Collaborating and Integrating with Office 2003

Analyzing Access data in Excel
Access is a great application for storing and retrieving, but when you want to analyze data,
Excel wins out. For that reason, Office makes it easy to analyze Access databases in Excel.
To do so, open the Access table you want to analyze, and then choose Tools_Office
Links_Analyze it with MS Excel. Excel opens the table and converts it into a spreadsheet,
where you can play with the data to your heart™s content.

Publishing Access reports with Word
Access has a disadvantage when it comes to designing reports for its data: Its tools can seem
awkward if you aren™t thoroughly familiar with it. But one advantage of Office™s integration
is that you can usually use data from any application in another application with which you™re
more comfortable. For that reason, Access also makes it easy to publish reports in Word.
Open the report you want to publish in Word in Access, and then choose Tools_Office
Links_Publish it with MS Word. Access opens Word and converts the report into a new
document in RTF format.

Merging Access data in Word
Access also lets you easily merge data from a database table with a Word document.
To do so:
1. In Access, open the table you want to merge, and then choose Tools_Office
Links_Merge it with Microsoft Word. This opens the wizard shown in Figure 9-9.

Figure 9-9: Use this wizard to merge Access data in Word.

2. Choose either to link your data to an existing Microsoft Word document ” a form
letter, for instance ” or to create a new document and then link the data to it. If you
choose to use an existing document, you™ll be asked to select it.
Chapter 9 ¦ Building Integrated Documents 237

3. Access opens Word and either displays the existing document you chose or a blank
document that you can create and format. You can™t see it, but the Word document
and the Access document are linked.
4. From here on, the process of using the Access data is the same as creating any other
mail-merged document in Word.

Sending a PowerPoint presentation to Word
Just as you can turn a Word document into the basis of a PowerPoint presentation, you can
turn a presentation into a Word document which you can then edit and format. This can be a
great way to create a hard-copy version of it.
To do so, open the presentation you want to turn into a Word document, and choose
File_Send To_Microsoft Word. This opens the dialog box shown in Figure 9-10.

Figure 9-10: Turn your PowerPoint presentation into a Word document, laid out just the
way you like it.

Choose how you want to lay out the pages (you can position slides two to a page, with notes
or blank lines beside them; one to a page, with notes or blank lines below them; or send the
outline only, without any slide images), and whether you want to paste (embed) the
presentation into Word or paste it as a linked object.
Click OK. PowerPoint creates a new document in Word and pastes the presentation into it.
Part II ¦ Collaborating and Integrating with Office 2003

Sharing Data with XML
As has been pointed out several times already in this book, Office 2003 offers XML
(eXtensible Markup Language) as a native file format ” meaning you can save your files as
XML files instead of as Office files.
XML is described in greater detail elsewhere, but it™s worth reiterating what is likely the
clearest definition of differences between HTML (the markup language used to create Web
pages) and XML: XML was designed to describe data, focusing on what data is, whereas
HTML was designed to display data, focusing on how data looks.
That makes XML an ideal format in which to exchange data between applications,
especially between Office and non-Office applications (provided they, too, support XML to
the extent Office does).
However, because Office applications do a fine job of interacting with each other with their
standard file formats, there™s no particular reason to use XML instead when sharing data
between them ” unless you™re also planning to share that data with non-Office applications.
In which case you™ll find the techniques for inserting linked and embedded files work with
Office documents saved in XML format just as they do for Office documents saved in their
standard formats.

In this chapter, you learned ways to build documents using more than one Office application
at a time. Key points included the following:
¦ There™s more than one way to insert an object from one application into another.
You can copy it and select Paste Special, choose Insert_Object from the menu, or,
in some applications, use built-in tools.
¦ When you use Paste Special, you can choose to insert an object in a number of
formats, which vary depending on what kind of object you copied. Typical options
include inserting the object as text, as a picture, as a linked or embedded object, or
as HTML.
¦ Embedded objects can be edited using the program that created them by double-
clicking them.
¦ Linked objects can be edited in the same way. The difference is that linked objects
are created from a source file, and if that source file is changed in the originating
program, the display in the destination document also changes. This is useful for
keeping documents up-to-date when data is changing rapidly.
¦ You can edit all the linked objects in your document by choosing Edit_Links. You
can choose to update links automatically or manually.
Chapter 9 ¦ Building Integrated Documents 239

¦ Other ways to share data in Office include sending Word documents to PowerPoint
presentations (and vice versa) and sending Access data to Excel for analysis or to
Word for publication or mail merging.
¦ You can integrate Office documents saved in XML format exactly the same way as
those saved in standard Office formats ” useful if you need to keep your
documents in XML format for sharing with non-Office users.
¦ ¦ ¦

with Other . . . .

Applications In This Chapter

Integrating Outlook
with Office

Creating a mail merge

Sending an e-mail
omputers are wonderful and complex tools. Unlike a simple
from an application
tool such as a hammer, a computer is intended to handle
many very different tasks. This versatility is the result of the broad
Importing and
range of software that is available for modern computers.
exporting data
In all likelihood, your copy of Outlook came as a part of Microsoft
Office. But even if it did not, you probably have software that
provides word processing functions, other software that manages . . . .
database information, and software that handles calculations. You
probably have many other applications on your computer, too. All
these different pieces of software may seem totally independent of
each other, but as you learn in this chapter, you may want to use
some of them to complement each other. You might, for example,
want to use the contact information that you have in Outlook to
help you create perfectly addressed letters using your word
processor. You might also want to send a spreadsheet file that
you™re working on as an e-mail message. These are just a few of
the benefits you can gain from integrating Outlook with some of
the other applications on your computer.

Integrating Outlook with Office
As you would probably expect, Outlook works very well with the
other applications that are a part of Microsoft Office. If you want
to use your Outlook Contacts list to create a mail merge in
Microsoft, you™ll find a command right on the Outlook menu to
Part II ¦ Collaborating and Integrating with Office 2003

begin the process (Tools _ Mail Merge). In fact, if you want to share information between
applications, Outlook is ready both to provide information to other programs and to use
information that is provided by other programs.
Much of this two-way data sharing can be thought of as common to many different
programs. It™s often quite easy to share data between programs provided by different
software manufacturers. You don™t have to use Word, Excel, or Access to share information
with Outlook. Of course, because Microsoft would like you to use their products, they™ve
made it just a bit easier to share information between the programs of Microsoft Office than
with other programs.
One way to share information between programs is to use linking or embedding to place an
object from one program into a document in another program. Linking places a link in your
document so that changes in the original object are reflected in your document. Embedding
places a static copy of the object into your document. Linking offers the advantage of smaller
document size and always up-to-date content, but embedding offers the advantage of having
everything combined into a single package.
You might include a chart from an Excel worksheet in an e-mail message to show your team
members how expenses have really increased over the past year. Or you might use a
Microsoft Visio image to illustrate an important point about how your new building proposal
will fit in with the existing structures in the neighborhood.
Here™s a quick example of how you might place an Excel chart into an e-mail message:
1. Create the chart in an Excel worksheet.
2. Select the object that you want to use in your e-mail message. In this case, select the
chart of monthly expense.
3. Select Edit _ Copy to copy the object to the Office Clipboard.
4. Switch back to Outlook. If the taskbar is visible, you can click the Outlook icon on
the taskbar, or you can use Alt+Tab to switch between applications.
5. Click the Mail Button Bar icon and then click the New Mail Message button to
display a new Message form.
6. Choose Format _ Rich Text.
7. Enter the addresses and subject line.
8. Type your message.
9. Select Edit _ Paste Special to display the Paste Special dialog box. You could
simply choose Edit _ Paste, but this won™t enable you to choose the link option.

A link option sends only a link, not actual data.

10. Choose Paste to embed the object.
Chapter 10 ¦ 243
Integrating Outlook with Other Applications

11. After you have selected how you want to paste the object, you may be able to select
the object type. Generally the types shown at the top of the list will remain the
closest to the object™s original appearance.
12. Click OK to paste the object as shown in Figure 10-1.

Figure 10-1: Inserted objects become a part of your document.


. 6
( 14)