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:
NetOrder:
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:
Customer_Orders_Source
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).
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:
31-Dec-1997
The Enter
Parameter Value dialog appears as depicted in Illustration 8.
Illustration 8: The
Enter Parameter Value Dialog with our Response
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.
» 
Comment and Contribute
