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 2

By William Pearson

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 in Part I of 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.

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