We will begin by composing a simple SELECT query, and
then we'll gain an understanding of the dataset that it returns; our query will
focus solely on the product table in its first step.
12.
Type the
following basic SELECT query into the editor:
SELECT
ProductID, ProductName
FROM
Products
GROUP BY
ProductID, ProductName
We can run the query at this stage by selecting Query --> Run from the main menu.
13.
Save the query
as ACC0701 (for "Step One.")
14.
Select Query
--> Run from the main menu.
The resulting dataset appears as partially shown in Illustration
8.
Illustration 8: The SELECT Query Dataset
We have assembled the basic SELECT query, and run it
to obtain the resulting dataset. Our objective in doing so is to begin setup of
the first of two independent SELECT queries. The second query will "enclose"
the first as a subquery, as we shall see. In this way, we can witness the
operation of the subquery in action, after having obtained a familiarity for
the underlying data involved.
We note that the above query delivers a record set
containing seventy-seven (77) products, which gives us a feel for the product
population.
15.
Ensure that
the query is saved as ACC0701.
Let's modify the query to bring in the corresponding
supplier information for the products. We will do this via an inner join on
the Supplier table, as shown in the next step.
16.
Shift back to SQL
view, once again, if necessary.
17.
Add the
following, immediately before ProductID on the line underneath the SELECT
keyword of ACC0701:
CompanyName,
18.
Replace the
following, underneath the FROM keyword:
Products
with the following:
Suppliers
INNER JOIN Products
ON Suppliers.SupplierID = Products.SupplierID
19.
Add the
following (be sure to include comma), immediately before ProductID on
the line underneath the GROUP BY keywords:
CompanyName,
While
all queries end in a semicolon (";") in MS Access, as we have
noted in previous lessons, we need not fret its addition; MS Access will insert
it automatically at run time.
The
query should now resemble that shown in Illustration 9.
Illustration 9: The SELECT Query with Modifications
20.
Select Query
--> Run from the main menu.
The resulting dataset appears as partially shown in Illustration
10.
Illustration 10: The Dataset Retrieved by the Modified
SELECT Query (Partial View)
We
note that, while the number of records returned remains the same as the number we
saw retrieved in our last query (77), we now present the Supplier
information that is associated with the various products.
21.
Save the query
as ACC0702.
» 