Our
next step will be to build an independent SELECT query, which will be
destined to become the primary query that houses the SELECT query
with the TOP statement as we left it at our last step. We will first create this SELECT
query to give us a layout to meet precisely the business requirements of
the information consumers that requested it; we will then tweak it for any
refinements we deem appropriate.
20.
Close query ACC09-02.
We are
returned to the view of existing queries.
21.
Click the New
button atop the Database window
The New
Query dialog appears.
22.
Ensuring that
the Design View option is selected, click OK.
The Select
Query dialog, with the Show Table dialog appearing in front, appears
as before.
23.
Click Close
on the Show Table dialog.
The Show
Table dialog closes, leaving only the Select Query dialog present.
24.
Select SQL
View using the View Selector button once again.
The SQL view editor appears.
25.
Type the
following new query into the editor:
SELECT
CStr(Employees.EmployeeID)+" - "+LastName AS Colleague,
OrderDate
FROM
Employees INNER JOIN Orders
ON
Employees.EmployeeID = Orders.EmployeeID
ORDER BY
Employees.EmployeeID ASC, Orders.OrderDate DESC
26.
Save the query
as ACC09-03.
27.
Select Query
--> Run from the main menu.
The resulting dataset appears as partially shown in Illustration
10.
Illustration 10: The Primary Query Results Dataset
Initial (Partial View)
While we appear to have constructed a query that generates
the layout required to meet the consumers' needs, we obviously have returned
the complete population of customer orders by Colleague and Order
Date, there being no filtering whatsoever in place at this stage.
Next, we will begin the marriage of the two SELECT
queries, embedding the query with the TOP keyword we constructed first
into the primary query we have constructed and saved as ACC09-03.
28.
Save the query
again as ACC09-04, to prevent any accidental damage to ACC09-03.
29.
Select SQL
View using the View Selector button once again.
The SQL view editor appears.
30.
Type the
following new query into the editor:[lbc]
31.
Select SQL
View using the View Selector button once again.
The SQL view editor appears.
32.
Type the
following into the editor, between the
ON
Employees.EmployeeID = Orders.EmployeeID
clause and the
ORDER BY
Employees.EmployeeID ASC, Orders.OrderDate DESC
clause:
WHERE
((Orders.OrderDate)
IN
(SELECT
TOP 5 OrderDate
FROM
Orders
WHERE
Orders.EmployeeID = Employees.EmployeeID
AND Orders.OrderDate < #31-Dec-1997#
ORDER BY
OrderDate DESC))
The
query should now resemble that shown in Illustration 11.
Illustration 11: The Modified Query, Incorporating
Subquery
33.
Save the query
once again as ACC09-04.
34.
Select Query
--> Run from the main menu.
The resulting dataset appears as partially shown in Illustration
12.
Illustration 12: The Query / Subquery Results Dataset
(Partial View)
In this, our second SELECT query, we have embedded
our initial query (which we constructed in steps 12 through 17
above), as a subquery. This arrangement induces a scenario whereby the primary
query executes the nested subquery, applying it as a
filter against the primary result dataset. The presence of the subquery within
the IN clause means that the dataset returned from the subquery is
compared to the dataset from the primary SELECT statement to determine
the dataset that is returned. Our addition of the join in the WHERE
clause, which joins a field from the nested subquery with a field within the
primary query, allows our construct to return the appropriate, differing
results for each of the employees returned by the primary query.
As we discussed in Part I,
the mechanics of the process that underlies our final query might make a
construct similar to our example inefficient, within a "real world" scenario
involving a huge table. Our purpose here is simply to illustrate the operation
of a subquery in reaching a reporting objective. In this case, the subquery incorporates
the TOP keyword as a part of its operation, to give us the "top"
data relating to the members of a much larger population. While we used TOP
in our example to narrow a result dataset to the latest five dates' data for
the employee set returned by the primary SELECT query, we might just as
well have used TOP to select a specified top (or bottom) number of
occurrences based upon another value.
35.
Ensuring that
the query is saved as ACC09-04, select File --> Close.
Within
the practice examples we have explored in Parts I and II of this
lesson, we can get a good idea of the utility of subqueries in returning
datasets to meet business needs. While various considerations (such as the
overhead involved in the operation of a subquery construct) come into play in
deciding whether to deploy subquery functionality in our production environments,
there are many scenarios where they can be invaluable in helping us to support
information consumers in our organizations.
Conclusion ...
With
this lesson, we continued the examination of MS Access subqueries that we began
with Part I, extending our exploration of
their construction and use to meet additional business needs. We examined the syntax
surrounding the use of an example subquery, reviewed the expected results from
the same example and then began an illustrative, hands-on example of the use of
a subquery in a multi-step, practice exercise.
We
began with the construction of a relatively basic SELECT query, to
illustrate the underlying operation and its effects upon the data. After
evolving the initial query, we created a second SELECT query, within which
we then nested the initial query, to demonstrate the operation of a subquery,
containing the TOP keyword, in our practice exercise. Finally, we
briefly discussed various aspects of the results datasets that we obtained at
relevant points throughout the practice example.
»
See All Articles by Columnist William E. Pearson, III
» 