Writing UNION Queries and SubqueriesUNION queries and queries that include subqueries require you to write Jet SQL statements. Union is one of the three choices of Query Design view's Query, SQL Specific menu. There is no menu choice for subquery. The following sections provide general syntax examples for writing UNION and subqueries, and provide simple Jet SQL examples. The general syntax examples use the same format as those of Chapter 21, "Moving from Jet Queries to Transact-SQL," for T-SQL statements. Using UNION Queries to Combine Multiple Result SetsUNION queries let you combine the result set of two or more SELECT queries into a single result set. Northwind.mdb includes an example of a UNION query, which has the special symbol of two overlapping circles, in the Database window. You can create UNION queries only with SQL statements; if you add the UNION keyword to a query, the Query Design Mode button on the toolbar and the query design choices of the View menu are disabled. The general syntax of UNION queries is as follows: SELECT select_statement UNION SELECT select_statement [GROUP BY group_criteria] [HAVING aggregate criteria] [UNION SELECT select_statement [GROUP BY group_criteria] [HAVING aggregate criteria]] [UNION. . .] [ORDER BY column_criteria] The restrictions on statements that create UNION queries are the following:
The sample Customers and Suppliers by City query is a UNION query that combines rows from the Customers and Suppliers tables. When you open a UNION query, Query Design view is disabled. To create a new UNION query, click the Database window's New button, select Design view, close the Show Table dialog, choose Query, SQL Specific, Union query to open the SQL window, and type the SQL statement.
You also can use UNION queries to add (All) or other explicit options to a query result set when populating combo and list boxes. As an example, the following SQL statement adds (All) to the query result set for a combo box used to select orders from a particular country or all countries: SELECT Country FROM Customers UNION SELECT "(All)" FROM Customers ORDER BY Country; The parentheses around (All) causes it to sort at the beginning of the list; the ASCII value of "(" is 40 and "A" is 65. Automatic sorting of combo and list box items uses the ASCII value returned by the VBA Asc function.
To create a query that returns all rows from joined tables, see "Using the tblShipAddress Table in a Query," p. 506.
To update a table by substituting a string for a specified value, see "Using the tblShipAddress Table with UNION Queries," p. 508.
For examples of using a UNION query to add an (All) item to a drop-down list, see "Adding an Option to Select All Countries or Products," p. 1244. Implementing SubqueriesAccess traditionally has used nested queries to emulate the subquery capability of ANSI SQL, because early Jet versions didn't support subqueries. Access 2003 lets you write a SELECT query that uses another SELECT query to supply the criteria for the WHERE clause. Depending on the complexity of your query, using a subquery instead of nested queries often improves performance. The general syntax of subqueries is as follows: SELECT field_list FROM table_list WHERE [table_name.]field_name IN (SELECT select_statement [GROUP BY group_criteria] [HAVING aggregate_criteria] [ORDER BY sort_criteria]);
Unlike UNION queries, you can create a subquery in Query Design view. You type IN, followed by the SELECT statement as the criterion of the appropriate column, enclosing the SELECT statement within the parentheses required by the IN predicate. Figure 11.74 shows the query design with part of the IN (SELECT...) statement in the Criteria row of the Customer ID column. Figure 11.75 shows the result set returned by the SQL statement and the query design. Figure 11.74. You can create the base query in Access's Query Design view, but you must type the IN predicate and the subquery's SELECT statement in the Criteria row of the grid.
Figure 11.75. This datasheet displays the result set of the subquery design of Figure 11.74.
|