MS Access for the Business Environment: Reporting in MS Access: Grouped Transactional Report Part I - Page 3

September 2, 2003

We will perform some added steps to make the query a more useful reporting data source at this stage.

16.  Move the Shipped Date column to the far left of the columns, by click-selecting it, then dragging and dropping it into position to the left of the Customer ID column.

The Shipped Date column is now the left most column in the display.

17.  Move the Order Date column to the immediate right of the Shipped Date column, so that it rests between the Shipped Date and Customer ID columns.

18.  Click the Criteria field for the Shipped Date column, to place the cursor into the field.

19.  Type the following expression into the Criteria field:

Between CDate("01-Jan-"+CStr(DatePart("yyyy",[AsOfDate]))) And [AsOfDate]

The purpose of this expression is to create a prompt for the "as of" date for which the report is to be generated. While not specifically requested by the information consumers, this feature is a common enough enhancement to those of us that are "old hands" at reporting. The ability to prompt for "as of" date means we can run the report not only for the date at which we physically generate it, but we can run it as of any other point in time and (assuming that we have data in place to select), and see the results as of the date we select at run time.

This expression also performs another function: it creates a date range that includes all dates from the beginning of the year (the year being based upon the "as of" date we supply at the prompt) up to the "as of" date; the time range that results will effectively act as a "year to date' range, as we shall see.

20.  Click the top ("Field") field of the first empty column to the right of the columns we have occupied in the query design grid (this should be the column to the immediate right of the Discount column), to place the cursor into the field.

21.  Type the following expression into the top field:

CCur([Order Details]![UnitPrice]*[Order Details]![Quantity]*(1-[Order Details]![Discount]))

The purpose of this column is to generate, via the calculation above, a Net Order value for each of the shipping transactions in the query. This value will serve as the basis for some of the details values and totals that we display in the report.

Let's save our work at this juncture.

22.  Select File -> Save As.

The Save As dialog appears.

23.  Type the following into the "Save Query 'Query1' to:" box:


The Save As dialog should now appear as shown in Illustration 6.

Illustration 6: The Save As dialog, with New Query Name

The Select Query dialog should now appear as partially shown in Illustration 7 (only enhanced / added fields depicted, to save space).

Click for larger image

Illustration 7: The Select Query Dialog (Alterations Only)

Now, let's run the query and examine the result set that it returns.

24.  Select Query --> Run from the main menu.

As soon as we kick off the report, we are prompted, via the Enter Parameter Values dialog that appears, for the "AsOf" date that we built into the selection Criteria field for the Shipped Date above.

25.  Type the following into the AsOf Date box:


The Enter Parameter Value dialog appears as depicted in Illustration 8.

Illustration 8: The Enter Parameter Value Dialog with our Response

26.  Click OK.

The Enter Parameter Value dialog closes and the query executes. The query returns its data set, whose size is 1,042 rows, as partially displayed in Illustration 9.

Illustration 9: The Data Set Returned by Our New Query (Partial View)

Scrolling through the result data set, we can see that the entire year of 1997 appears to be represented within its rows.

We have now created and saved a query in our Access database. This query will serve as the data source for the Customer Orders report that we will build in following sections to demonstrate the steps involved.

27.  Click File --> Close to close the Customer_Orders_Source query.

28.  Click Yes, if prompted to save the layout of query Customer_Orders_Source.

We are returned to the Database window.