Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Dec 1, 2003

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

By William Pearson

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.



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM