MS Access for the Business Environment: Reporting in MS Access: Grouped Transactional Report Part II

Monday Oct 6th 2003 by William Pearson

Share:

Create a transactional report that
groups at multiple levels. In the second half of a two-part article, author
Bill Pearson leads the step-by-step design and creation of a report that groups
customer transactions at multiple levels with corresponding totals.

About the Series ...

This article continues the series,
MS Access for the Business Environment. The primary focus of
this series is an examination of business uses for the MS Access relational
database management system. The series is designed to provide guidance in the
practical application of data and database concepts to meet specific needs in
the business world. While the majority of the procedures I demonstrate will be
undertaken with MS Access 2002, many of the concepts that we expose in
the series will apply to other versions of MS Access.

Introduction
to this Tutorial

This is the second half of a
two-part tutorial that explores the creation of a basic transactional report
that groups the information it presents at multiple levels, at most of which a
corresponding total will be displayed. This article continues with the
preparation we performed in Reporting
in MS Access: Grouped Transactional Report Pt.I, where we
established our data source as a query we constructed in the Northwind sample
database.

In Part I,
we discussed the requirements that the report will need to address, then listed
the general steps involved in professional report design and creation. We then
began to design and create our illustrative report in a hands-on manner,
following many key steps that are common in a successful report writing project
within a collaborative business environment. Within each step, we discussed
the details involved and the results that we sought to obtain within our
design.

The report that we began to
create in our lesson focuses on orders placed by the customers of Northwind.
It's objective is to present summary information about orders placed by
customers over year-to-date and other time intervals - intervals that can be
controlled by a given information consumer at report run time. We discussed
some of the specific business needs that the report will need to address.

In this article, we will pick up
where we left off with the common steps for successful reporting efforts,
focusing initially on "pre-setting" the sorting and grouping of datain the report. We will then select data, from the data source we created
in Part I, for inclusion in our report. After entraining the data, we
will focus upon the arrangement of labels and text in the report, the
establishment of settings based upon grouping, and the handling of other
attributes specified by the intended audience. Finally, we will briefly
discuss the review and refinement of the report based upon feedback that we
receive from information consumers, who ideally review the report at various
evolutionary stages.

A Review of the Common Steps for Successful Reporting

In Part I, we discussed
that, as diverse as they may be in scope and purpose, and as different as the
industries and sizes of the organizations for whom we are consulting to
complete them, the majority of reporting projects are composed of several
common steps that bring about a successful conclusion. Those steps, again,
include:

Gather the business requirements.

Design the report.

Locate and access the data.

Create the new report.

Establish report characteristics.

Set up sorting and grouping of datain the report.

Select data and include it in the report.

Arrange labels and text in the report.

Establish display and other settings based upon grouping and
other attributes.

Review and refine the report based upon the input of its intended
audience.

Due to the differences in each unique reporting project, the
order of the above steps may differ. In addition, there may be
different iterations of many of the steps, as the feedback we receive from the
intended audience, procured throughout the report development cycle, as opposed
to at the end only, will often drive stepping back through formerly completed
steps to make the adjustments required to meet more refined requirements. The
sooner we can identify these modifications, which often come to light only
after the information consumers are shown our evolving designs, and after they
get a feel for potential capabilities they may not have expected in the initial
requirements gathering discussions, the less rework we are compelled to
perform. Again, the key is recurring communication and synchronization of our
efforts with the needs of the intended audience.

We will rejoin the report design we began in Part I at the "Set up sorting and
grouping of data in the report" step in our procedures above.

Review and Rejoin the Project from Part I

In the present case specifically, and in a
large percentage of report drafting efforts in general, we wish to organize
data for presentation by a particular group or groups. The present reporting
objective, as we defined in Part I, is to present summary information about the
cumulative orders placed by customers. In our example report, as an illustration,
we wish to group transactions primarily by customer, based upon the draft we
completed in our early business requirements gathering stage (and depicted,
again, in Illustration 1).

Illustration 1: Draft of the Proposed Customer Orders Report

As you recall, we created the rough
initial draft based upon our understanding of the business requirement. We might
then have presented the draft, listening closely to the feedback of the report's
intended audience, to confirm our understanding of the need. After making a
few adjustments, in our example, to the draft, we produced the rough design
shown in Illustration 1 above. The initial draft includes grouping by
customer, then by order dates of the customer, which is the focus of this
section of our current lesson.

As we will see, we can create both single
and multiple group levels within an MS Access report. Groups drive many other
facets of report layout and behavior, as we will also see, including subtotals,
grand totals, percentages of totals, summary calculations, sorting
considerations, and other attributes of the report in which they are created.
The order of the steps we undertake here can mean efficient report design and
creation - or a tedious, time consuming process that is fraught with reversals
and rework. As in many other areas of report creation, thinking ahead
usually pays large dividends.

Let's return to the report at the state in which we left it
in the first half of the lesson. We have done the majority of the preparation
for populating our report with the data that has been requested by our
information consumers. Having accomplished the preliminary layout for our
report, we will begin, in Part II of this tutorial, to bring in and
arrange the data for presentation.

1.
Open MS Access once again, and return to the Database window.

The steps for entering MS Access, as well as for navigating
to the Database window, are outlined in Part I. Once at the Database
window, our view should resemble that depicted in Illustration 2.

Illustration 2: Typical Database Window

2.
Click Reports,
under Objects in the Database
window.

The Report
window appears, similar to that shown in Illustration 3.

Illustration 3: Typical Reports Window

3.
Select the Customer
Orders Report that we created inPart Iof this lesson, highlighting it.

4.
Click the Design
button in the toolbar, as shown in Illustration 4.

Illustration 4: Select
the Report and Click Design View

The new report appears in Design View, with little in
place except the designated Page Header, Detail, and Page
Footer sections, as partially shown in Illustration 5.

Illustration 5: The
Blank Report, Design View

Having
accomplished the preliminary creation, and basic layout, for our report, we
will now "pre-set" sorting and grouping for our report, and create
the sections thereof as part of the process.

Set up Sorting and Grouping of Data in the Report

We are ready to sort our data. MS Access requires
that we do so before we can define groupings. Keep in mind that any sorting in
the source query will be irrelevant.

1.
Select View
--> Sorting and Grouping from the main menu in the Design
View.

The Sorting
and Grouping dialog appears.

2.
Click the top
row within the Field/Expression column.

3.
Click the
selector arrow that becomes visible.

A list
of fields in the Customer_Orders_Source query appears.

4.
Select the CustomerID
field in the list.

5.
Leave the Sort
Order column setting at the default of Ascending.

6.
Click the Group
Header field below the Field/Expression and Sort Order grid
in the dialog (in the Group Properties section of the dialog).

7.
Using the down
arrow in the Group Header field, select Yes to group on CustomerID.

8.
In the same
manner, select Yes in the Group Footer field immediately underneath
the Group Header field.

9.
Leave the
remaining Group Properties at default.

10.
Select the OrderDate
field for the Field/Expression column of the next row.

11.
Leave the Sort
Order column setting, again, at the default of Ascending.

12.
Click the Group
Header field below the Field/Expression and Sort Order grid
in the dialog.

13.
Using the down
arrow, select Yes to group on OrderDate.

14.
In the same
manner, select Yes in the Group Footer field immediately below.

15.
Leave the
remaining group properties at default.

The Sorting
and Grouping dialog appears as shown in Illustration 6.

Illustration 6: Sorting
and Grouping Dialog, with Settings

16.
Close the Sorting
and Grouping dialog.

The
report again appears in an empty state, although our recent settings have been
applied. The telling difference, however, is the appearance, as seen in Illustration
7 (partial view), of the Header / Footer sections for the CustomerID
and ShippedDate fields.

Having
accomplished the preliminary group and sort criteria, we will begin the process
of bringing data into our currently blank report.

Select Data and Include It in the Report

Recall that, in Part I, Create the New Report section, we bound our new
report object to the source query via the Choose The Table or Query where
the Object's Datacomes from selector within the New Report
dialog, Design View (our selection of the query initialized the Report
Designer, from which we first saw the Header / Footer layout
in its basic, default form.) Our objective, at this step in our general
report-building procedure, is to select the desired data controls, objects that
represent the fields of the data source, and to include them by dragging the
selected controls into our report via the Report Design window.

Populating the Detail Section

First, let's summon the Field List that MS Access
provides as a "control palette" (my metaphor, not Microsoft's), so
that we can see the fields represented.

1.
Select View
--> Field List from the main menu in the Report
View.

2.
Select Field
List from the menu that appears.

The Field
List appears, as depicted in Illustration 8.

Illustration 8: The
Field List Appears

We can
now begin dragging controls, and thus the data fields they represent, into our
report. We will populate the Detail section, which, in our report,
represents the transactional level, first, and then handle the group headers
and footers next. (Our focus in this lesson is the inclusion and presentation
of the data. We will not spend a great deal of time on formatting, report
titles, and other such cosmetics, so as to allow maximum coverage of actual
reporting concepts.)

3.
In the Field
List, click the ProductID field.

4.
Hold down the CTRL
key, to allow the selections of non-contiguous fields.

5.
Click each of
the following to select them simultaneously with the ProductID field.

ProductName

UnitPrice

Quantity

Discount

NetOrder

The Field
List with selected fields appears as shown in Illustration 9.

Illustration 9: The
Field List with Our Selections

6.
Click some
point within the fields highlighted (left-mouse).

7.
Drag the
fields to the Detail section.

8.
Drop the
fields / controls at a point in the upper left corner of the Detail
section, at approximately zero (0) inches on the horizontal (across the top of
the Design View) ruler. Make the drop point as close to flush to the
top of the Detail section as possible

The
controls appear, still selected as a group, as shown in Illustration 10.

Illustration 10: Dropping
the Data Fields / Controls

At
this juncture, we will discuss working with the controls we have just put in
place. Actually, a pair of controls appears for each field we dragged
off the field list. The control to the left labels the control with
which it is paired (hence we refer to the control on the left as a label
control). The control to the right is known as a text control; it
is the actual representative of the underlying data field to which it is
attached, or "bound," in the data source.

To
move a control pair (actually termed a compound control), we select the
pair by clicking either control. Next, glide the mouse cursor over either of
the controls, until the cursor becomes a hand, as shown in Illustration 11.
Once the hand appears, simply click and drag to move the compound controls
together.

Illustration 11: Preparing
to Move the Compound Control

To
move only one of the two controls within the pair, drag it by its Move
handle, the large black box in the upper left corner of the control selected.
A hand with a pointing finger appears when the cursor is in position to move
the single control (as depicted in Illustration 12), at which point we
simply click and drag, to move the single control as required.

Illustration 12: Preparing
to Move the Single Control

The
smaller squares are Sizing handles for the selected control. Selection itself
is indicated by the appearance of Sizing handles: they are visible
only for the selected control. A summary diagram appears in Illustration 13.

Let's
practice the selection, moving and sizing techniques in the Details
section of our report, where we dropped the six fields we dragged from the Field
List above.

9.
Click the ProductID
compound control at any point.

The Sizing and Move handles appear for the ProductID
control pair.

10.
Hover the
cursor over the area to the immediate right of the second Move handle.

The cursor becomes a hand image.

11.
Click and drag
the ProductID compound control to a position approximately 1/4-inch on
the horizontal (across the top of the Design View) ruler; keep the
control as close to flush to the top of the Detail section as possible.

12.
Click the ProductName
compound control at any point.

The Sizing and Move handles appear for the ProductName
control pair.

13.
Hover the
cursor over the area to the immediate right of the second Move handle.

The cursor becomes a hand image.

14.
Click and drag
the ProductName compound control to the immediate right of, and aligned
with, the ProductID compound control.

15.
Drop the ProductName
compound control, as shown in partial view of the Detail section in Illustration
14.

Illustration 14: New
Placement of the ProductName Compound Control

16.
Click the
label control (the left control in the pair) for ProductID.

17.
Press the Delete
key.

The ProductID label disappears. We will likewise
remove the label controls from each of the fields we dragged into the Detail
section, as we do not want to show these in the report.

18.
Delete the
label controls for each of the remaining control pairs in the Detail
section of the report:

ProductName

UnitPrice

Quantity

Discount

NetOrder

The Detail
section of the report appears as shown in Illustration 15.

Illustration 15: The
Detail Section Controls - sans Labels

19.
Resize the ProductID
text control to have the right border touch the 1-1/4 inch horizontal
marker line (as we proceed, ensure that all Detail section controls are
placed snug against the top of the Detail section).

20.
Move the ProductName
control to align with ProductID vertically, with the ProductName
left border at the 1-1/4 inch horizontal marker line.

21.
Resize the ProductName
control, if required, so that its right border touches the 2-1/2 inch
horizontal marker line.

22.
Move the UnitPrice
control to touch ProductName's right border at the 2-1/2 inch horizontal
marker line.

23.
Resize the UnitPrice
control, as necessary, so that its right border touches the 3-inch horizontal
marker line.

24.
Position the Quantity
control so that its left edge touches the 3-1/4 inch horizontal marker line.

25.
Resize the Quantity
control, as necessary, so that its right border touches the 3-3/4 inch
horizontal marker line.

26.
Position the Discount
control so that its left edge touches the 4-inch horizontal marker line.

27.
Resize the Discount
control, as necessary, so that its right border touches the 4-1/2 inch
horizontal marker line.

28.
Position the Net
Order control so that its left edge touches the 4-3/4 inch horizontal
marker line. (The page will automatically widen as required.)

29.
Resize the Net
Order control, as necessary, so that its right border touches the 5-1/2
inch horizontal marker line.

Now let's
size the Detail section to ensure that our detail transactions are
presented in compact fashion.

30.
Move the
pointer to the bottom of the Detail section, placing it between the Detail
section and the OrderDate footer section.

The pointer becomes a "double-headed arrow,"
indicating that it is in position for dragging the Detail section border
to the desired position.

31.
Hold down the
left button on the mouse, as soon as the arrow image appears.

32.
Drag the Detail
section border up, until a tight fit is achieved for the controls we have
included.

33.
Release the
button to fix the border in place.

The Detail
section of the report now appears as depicted in Illustration 16.

We can
perform an ongoing preview of our evolving report through the use of the
Preview feature. Let's do so now, to get a feel for the effectiveness of our
design work thus far.

34.
Click the Print
Preview button (shown in Illustration 17) on the main toolbar.

Illustration 17: The
Print Preview Button

We are
prompted for an "As Of" date, via an Enter Parameter Value
dialog that appears, and which is initialized because of the logic we built
into our underlying data source, a query that we constructed in the first half
of this lesson. Recall that our intent with this query is to enable an
information consumer to generate the customer order information found in this
report "as of" any given date in time (or at least "as of"
any date that is included within the data itself). For more information
regarding the setup or reasoning behind the prompt, see Part I.

35.
Type the
following (a date which we know to be preceded in time with transactions,
within our database) into the AsOfDate box:

The
report executes, returning data that appears similar to that partially shown in
Illustration 19.

Illustration 19: The
Report Preview - Partial Detail Section

We
can see that the Detail section of the report is shaping up nicely and
that the data is appearing in accordance with our alignment and placement
intentions. The spaces that appear between the detail sections are due to the
placement of our header / footer sections, which are currently empty. We will
see in the next section how to populate, as appropriate, these sections.

37.
Click the Close
button to close the Preview window.

38.
Save your work
as desired, as a safety measure.

Working with the OrderDate Header and Footer Sections

In our report, we will use the group header sections to
present the order date and customer information that is contained within the Detail
section. The Detail section of this report is "home" to the
customer order information; the OrderDate header and footer sections
will serve as "containers" from which we can present the OrderDate
groups to which each set of details belongs.

1.
Re-enter the Design
View for the Customer OrdersReport, if necessary.

2.
Select OrderDate
from the Field List.

3.
Drag the OrderDate
compound control to the OrderDate header section, dropping it into the
upper left corner of the section.

4.
Reposition the
compound control to be flush to the top of the OrderDate header section,
and at approximately the 1/4-inch point on the horizontal ruler.

5.
Click the text
control (the right control in the pair) for OrderDate, to select it.

6.
Drag the text
control to align to the right of the OrderDate label, at approximately
the 7/8-inch point on the horizontal ruler, flush to the top of the OrderDate
header section.

12.
Select the OrderDate
compound control by clicking the label control, holding down the SHIFT
key, and then clicking the text control.

The compound control appears as selected.

13.
Click the Bold
button (Illustration 22) in the main toolbar.

Illustration 22: The
Bold Button

14.
Move the
pointer to the bottom of the OrderDate header section, placing it
between the OrderDate header section and the Detail
section.

15.
Hold down the
left button on the mouse, as soon as the "double-arrow" image
appears.

16.
Drag the Detail
section border up, until a tight fit is achieved for the controls we have
included.

17.
Release the
button to fix the border in place.

The relevant portion of the Design View should appear
similar to Illustration 23.

Illustration 23: The
OrderDate Header

The relatively simple setup for the header is complete.Next, we can consider the OrderDate
footer section. The fact that it should be essentially the same as the header
setup will allow us a welcome shortcut, as we shall see in the steps that
follow.

18.
With the OrderDate
compound control selected, press the CTRL-C key combination.

This
copies the compound control into the notepad area.

19.
Click inside
the OrderDate footer section.

20.
Press the CTRL-V.

This
pastes the compound control, together with the adjustments and formatting that
we have already performed in the OrderDate header, into the footer. We
are thus spared at least the tasks of resizing, formatting and re-setting the
alignment property for the OrderDate compound control. We have only to
arrange it as we need it in the footer section, as well as to handle any
differences in its use.

21.
Using the
methods we have outlined previously, move the OrderDate compound control
so that its left border is aligned with the 1/4-inch point on the horizontal
ruler.

This
should align the OrderDate compound control in the footer with the
compound control from which it was cloned in the OrderDate header. We
want to make one alteration, however; a modification to the existing text in
the label control to make it more meaningful in its context within the footer.

22.
Right-click
the OrderDate label control in the OrderDate footer.

23.
Select Properties
from the context menu that appears.

24.
Select the
Format tab of the Properties sheet, as necessary.

25.
Modify the Caption
field at the top of the Format tab to read as follows:

Totals for:

The Properties sheet - Format tab for the OrderDatelabel controlappears as depicted in Illustration 24.

The
report executes, returning data that appears similar to that partially shown in
Illustration 26.

Illustration 26: The
Report Preview - Partial Sections

Let's
take a look at the overall page layout from a somewhat "higher" level;
while it might make the specific data harder to see, the zoom option for
Preview is certainly useful in getting a feel for the overall page
layout.

The
report appears in reduced magnification mode, actually "zooming out,"
affording us a general "page" view of overall layout of the report,
as shown in Illustration 27.

Illustration 27: The
Report Preview - Zoomed Layout View

Again,
we see that the report is developing according to our objectives.

36.
Click the Close
button to close the Preview window.

37.
Save your work
as desired, as a safety measure.

Next,
we will move into the CustomerID header and footer sections.

Working with the Customer Header and Footer Sections

The next header and footer sections with which we will deal
in the Customer OrdersReport will act to group order information
by customer. The Customer header and footers will enclose the OrderDate
group, within which lies the Detail information. The steps involved in
setting up this part of our report will be quite similar to those of the OrderDate
sections that we assembled above, once again occurring within the Design
View for the report.

1.
In the Field
List, click the CustomerID field.

2.
Hold down the SHIFT
key, to allow the simultaneous selection of contiguous fields.

3.
Click the CompanyName
field.

4.
Drag the
fields together to the CustomerID section.

5.
Drop the
fields / controls at a point in the upper left hand corner of the CustomerID
header.

6.
Align the
compound control approximately with the 1/8-inch tick on the horizontal ruler,
as flush to the top of the CustomerID section as possible.

7.
Right-click
the CustomerID label control.

The Properties
page for the CustomerID label control appears, with the Format
tab as the default view.

8.
Modify the Caption
to read as follows:

Customer:

9.
Change Font
Weight to Bold.

The Properties sheet - Format tab for the CustomerID
label controlappears as depicted in Illustration 28.

12.
Pass the
cursor over the Move handle for the control CustomerID text
control, until the pointing finger image appears.

13.
Click and drag
the control to the immediate right of the CustomerID label control.

14.
Click the CustomerID
label control to select it.

15.
Resize the CustomerID
label control to fit the new caption (the right border of the control will
align approximately with the 3/4-inch point on the horizontal ruler).

16.
Move the CustomerID
text control again to adjust its position to the immediate right of the CustomerID
label control.

17.
Resize the CustomerIDText control so that its right border aligns approximately with the
1-1/4 inch point on the horizontal ruler).

18.
Click the
label control (once again, the left control in the pair) for CompanyName.

19.
Press the Delete
key.

The CompanyName
label disappears, leaving only the associated text control.

20.
Click the CompanyName
text control to select it.

21.
Move the CompanyName
text control to the immediate right of the CustomerID text control
(aligning it approximately with the 1-1/4 inch point on the horizontal ruler,
and to the top of the CustomerID section).

We
need, once again, to size the section to ensure that our CustomerID
header is presented in compact fashion.

22.
Move the
pointer to the bottom of the CustomerID header section, placing it
between the CustomerID header section and the OrderDate header
section.

The pointer becomes a "double-headed arrow,"
indicating that it is in position for dragging the CustomerID header
section border to the desired position.

23.
Hold down the
left button on the mouse, as soon as the "double-arrow" image
appears.

24.
Drag the CustomerID
header section border up, until a tight fit is achieved for the controls we
have included.

25.
Release the
button to fix the border in place.

The straightforward
setup for the CustomerID header section is complete.Next, we will consider the CustomerID footer
section. We will take advantage of the shortcut we used earlier in "cloning"
a large part of the text we need, as we did earlier, to save time and effort.

26.
In the CustomerID header
section, select the CustomerID label control (now captioned "Customer:"),
and, pressing the SHIFT key, select the other two controls (all three
controls in the header should be selected).

27.
Press the CTRL-C key
combination.

28.
Click inside the CustomerID
footer section.

29.
Press the CTRL-V key
combination.

This pastes the controls,
together with the adjustments and formatting that we have already performed in
the CustomerID header, into the footer. We will next arrange it as needed
in the footer section, as well as to handle any required modifications.

30.
Using the methods we have outlined
earlier, move the compound controls so that they align with the controls from
which they were cloned in the CustomerID header. (This
can most easily be accomplished by selecting all simultaneously, then dragging
and dropping so that the left border of the CustomerID label control
approximately aligns with the 1/8 - inch mark on the horizontal border.)

We have an alteration
here, similar to the one we made in the OrderDate footer earlier: We
again want to modify the text in the footer to indicate it is a "totals"
line.

31.
Right-click the CustomerID
label control in the CustomerID footer.

32.
Select Properties from
the context menu that appears.

33.
Select the Format tab of
the Properties sheet.

34.
Modify the Caption field
at the top of the Format tab to read as follows:

Totals for:

The
Properties sheet - Format tab for the CustomerIDlabel
controlappears as depicted in Illustration 29.

The
report executes, returning data that appears similar to that partially shown in
Illustration 31.

Illustration 31:
The Report Preview - Partial Sections

43.
Click the Close
button to close the Preview window.

44.
Save your work
as desired, as a safety measure.

The
basic ingredients of the report are now in place. We next need to add totals
to the footers to round out the basic functionality of the report.

Working with Totals

Let's
revisit the footers we have created long enough to add totals. We have aligned
our various levels of grouping adequately to make this an efficient exercise.
The totals that we insert will be group summaries, and, as such, are contextually
sensitive to the section into which we insert them. The expression in a
field that yields a summary at a given group level will also yield the correct
summary when placed in another group level (respective of the level in which it
is placed); it "knows" its location and, as we shall see, behaves
accordingly.

We
will start with the OrderDate footer section then work our way outward
in summary creation, just as we did in the creation of the groups themselves.

The
cursor becomes a "+" ("crosshairs") sign, to assist
us in placing the new control.

4.
Click at the
point of insertion of the top left corner of the control.

5.
Try to place
the control under the NetOrder field just above the OrderDate
footer, by drawing a rectangular outline approximately its size under the NetOrder
field.

6.
Click the Label
control that has appeared to the left of the new text control.

7.
Press the Delete
key.

The Label
control disappears.

8.
Adjust the
alignment of the new Text control as closely as possible with the NetOrder
field by resizing and dragging as appropriate.

9.
Right-click
the new Text control.

10.
Select Properties.

The Properties
sheet appears, defaulted to the Format tab.

11.
Select Currency
in the Format field.

The Properties
sheet - Format tab for the new Text control appears as depicted
in Illustration 34.

Illustration 34: Properties sheet
- Format Tab for the New Text Control

NOTE:The
new Text control, like all new controls, is assigned an identifying name
by MS Access upon creation ("Text21" in Illustration 34). In
general, we would assign logical names to the controls based upon corporate or
project standards that made sense to us at the point of creation. We will
leave these identifiers at default for purposes of this lesson, in the interest
of brevity, but proper naming is, without doubt, an important attribute in
organizing and maintaining the object set in any real world project.

12.
Click the Data
tab.

13.
Type the
following into the Control Source field:

=Sum([NetOrder])

The Properties
sheet - Data tab appears as shown in Illustration 35.

Illustration 35: Properties sheet
- Data Tab for the New Text Control

14.
Close the Properties
sheet for the new Text control.

15.
On the Controls
Toolbox, click the Line button, shown in Illustration 36.

Illustration 36: The Line Button
in the Controls ToolBox

16.
Place the
mouse cursor within the OrderDate footer.

As we
saw with the Text control in the immediately preceding steps, the cursor
becomes a "+" sign, to assist us in placing our control.

17.
Click at the
point of insertion, this time at the top left corner of the recently placed Text
control (our summary control).

18.
Place the Line
control above the new summary Text control field, by dragging the line
to a length approximately the size of the NetOrder field.

The
approximate desired effect is depicted in Illustration 37.

Illustration 37: Line Placement
above the new Summary Text Control

19.
Click the Line
control to select it.

20.
Hold the SHIFT
Key.

21.
Click the new
summary Text control to select it simultaneously with the Line control.

22.
Use the "up,"
and other arrow keys, as shown in Illustration 38, to adjust the
positioning of the controls to align them with the NetOrder column to
which they relate.

NOTE:Multiple
visits to the report Preview might be in order to allow us to perfect the
alignment of the manually placed controls to the NetOrder controls. (If you accidentally
push the control pair into the section above, "back out" with the UNDO
(CTRL + Z) key combination.)

23.
Readjust the
footer borders as necessary to maintain the "tight fit" we had
previously established.

24.
Preview the
report, using 16-Oct-1996 as the "as of" date
again, to review the outcome.

We are
now ready to insert a summary control in the CustomerID footer. Let's
take a "shortcut" similar to one we took with titles in this footer
above. This time, we will leverage the operation to demonstrate another
feature about our summary control.

25.
Select both
the line and the summary control we created in the OrderDate footer
above. (Hint: Use the SHIFT key).

26.
Press the CTRL
+ C key combination to copy the selection.

27.
Place the
cursor in the CustomerID footer.

28.
Press the CTRL
+ V key combination to paste the selection.

Our newly
pasted control pair will typically not appear conveniently below the last
summary control we created. It will appear "pre-selected,"
however, wherever it "lands," meaning that we can easily align it
with the precision of the directional keys we used above.

29.
Position the
new control pair within the NetOrder "column" (underneath the
last summary control we created).

30.
Select the new
summary control alone.

31.
Right-click
the control, to display the context menu.

32.
Select Properties
from the context menu.

The Properties
sheet appears, defaulted to the Format tab.

33.
In the Font
Weight field, select Bold.

34.
Close the Properties
sheet.

We are
returned to the Design View.

Now, let's
preview the report again (same "as of" date at the prompt, for
consistency's sake), to observe the behavior of the new control.

35.
Click the Print
Preview button.

36.
Type the
following into the AsOfDate box that appears:

16-Oct-1996

37.
Click OK
to apply the date.

The
report executes, returning data that appears similar to that partially shown in
Illustration 39.

Illustration 39:
The Report Preview - Partial Sections

Taking
CustomerID BERGS as an example, as shown above, we note that the new
total appears (in bold, as we intended), but not only do we note it's
appearance; we notice that it is context sensitive to its position in the CustomerID
footer of the report. The identical expression that we input into the OrderDate
footer takes on a new meaning based upon its location in the CustomerID
footer. It is because of this fact that we can benefit by the use of the "shortcut"
we have illustrated.

38.
Click the Close
button to close the Preview window.

39.
Save your work
as desired, as a safety measure.

We now
have all the control functionality in place to deliver the requirements of the
intended audience of the report. Moreover, the prompt feature of the report
means our totals are the totals for each customer "as of" the
date we plug into the mechanism at runtime. I typically prefer to get all data
elements of the report in general order before beginning formatting; to begin
earlier than that is almost certain to mean rework, if virtually any element or
elements change; data changes can ripple through the entire report, causing
the need to realign and myriad other such tweaks.

We
will not go further with formatting, as that could fill a lesson in itself.
Feel free to experiment with borders, colors and other formatting functionality
to make the report attractive and easy for the information consumers to read.
In addition to formatting, we would no doubt want to create page headers and
footers appropriate to the report. Particularly attractive features might
include display of the "as of" date in the header, as part of the
report title; page numbers and a host of other options can be added, as you can
see through experimentation on your own.

40.
Close MS
Access, when desired.

Review and Refine the Report Based Upon the Input of Its Intended Audience

At this stage in our report construction, we might present
our work to members of the intended audience to obtain their confirmation of its
meeting their expectations. We might also obtain feedback as to further
adjustments to make, based not only upon deficiencies, but, as is often the
case, upon ideas generated by the review process for further enhancements and
added functionality. Everyone wins in this environment of continuous
improvement, and we meet the challenges with ready confidence.

Conclusion...

With this lesson, we continued a
two-part tutorial surrounding the creation of a transactional report that
groups and summarizes the information it presents at multiple levels. We resumed
where we left off in Part I, reviewing the
common steps for successful reporting efforts, as well as an illustrative business
requirement that we had been given by our information consumers in the previous
half of the lesson. We then focused initially on "pre-setting" the sorting
and grouping of datain the report. After establishing our grouping and
sorting criteria, we selected data from the query data source we created in Part I, for inclusion in our report. Moreover,
with the introduction of each data element, we focused upon the arrangement of
labels and text in the report, the establishment of settings based upon
grouping, and the handling of other attributes expected by the intended
audience. Finally, we briefly discussed the need for review and refinement of
the report, based upon feedback that we receive from information consumers, who
ideally scrutinize the report design at various evolutionary stages.