MS Access for the Business Environment: Access Query Techniques: Subqueries, Part 1 - Page 4

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.

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.

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.

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.

CompanyName,

Products

Suppliers
      INNER JOIN Products 
         ON Suppliers.SupplierID = Products.SupplierID

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.

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.