Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Oct 6, 2003

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

By William Pearson

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.

Illustration 38: The Directional Keys Act as Excellent Placement Tools for Selected Controls

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:


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.


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

» See All Articles by Columnist William E. Pearson, III

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM