MS Access for the Business Environment: Access Query Techniques: Subqueries, Part 1 - Page 4
December 1, 2003
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.
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:
18. Replace the following, underneath the FROM keyword:
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:
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.
20. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 10.
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.