Writing SELECT Queries in SQL The heart of SQL is the SELECT statement used to return a specified set of records from one or more tables. The following lines of syntax are used for an SQL SELECT statement that returns a virtual query table (called a result set, usually a Recordset object) of all or selected columns (fields) from all or qualifying rows (records) of a source table: SELECT [ALL|DISTINCT| DISTINCTROW] [TOP n [PERCENT]] select_list FROM table_names [WHERE search_criteria] [ORDER BY column_criteria [ASC|DESC]] The following list shows the purpose of the elements in this basic select query statement: SELECT is the basic command that specifies a query. The select_list parameter determines the fields (columns) that are included in the result table of the query. When you design a graphical query, the select_list parameter is determined by the fields you add to the Fields row in the Query grid. Only those fields with the Show check box marked are included in the select_list. Multiple field names are separated by commas. The optional ALL, DISTINCT, and DISTINCTROW qualifiers determine how rows are handled. ALL specifies that all rows are to be included, subject to subsequent limitation. DISTINCT eliminates rows with duplicate data in both Jet SQL and T-SQL. DISTINCTROW is a Jet SQL keyword, similar to T-SQL's DISTINCT, that eliminates duplicate rows but also enables you to change values in the query result set. T-SQL doesn't support DISTINCTROW, so the preceding is the only example in this chapter that includes this qualifier. The optional TOP n [PERCENT] modifier limits the query result set to returning the first n rows or n percent of the result set prior to the limitation. TOP and PERCENT are Jet SQL and T-SQL, not ANSI SQL, keywords. You use the TOP modifier to speed display when you want to display only the most significant rows of a query result set. TOP 100 PERCENT, which returns all rows, is required to create SQL Server views that you can sort with the ORDER BY clause. FROM table_name specifies the name or names of the table or tables that form the basis for the query. The table_name parameter is created in QBE by the selections you make in the Add Table dialog. If fields from more than one table are included in the select_list, each table should be specified in the table_name parameter. You must prepend table names to field names that are present in both tables (see the following Caution). Commas separate the names of multiple tables. WHERE search_criteria determines which records from the select list are displayed. The search_criteria is an expression with a predicate, such as LIKE or = for text fields, or a numeric operator, such as <, > or >=, for fields with numeric values. The WHERE clause is optional; if you don't add a WHERE clause, the query returns all the rows from the table specified by FROM table_name. ORDER BY column_criteria specifies the sorting order of the result set. Like the WHERE clause, ORDER BY is optional. You can specify an ascending or descending sort by the optional ASC or DESC keywords. If you don't specify a sort direction, ascending is the default.
Caution If you add fields from two or more tables and don't join the tables by a WHERE Table1.Field1 = Table2.Field2 clause or a JOIN statement, the statement returns a combination of all rows of all tables, called a Cartesian product. Executing such a statement against tables on a remote machine can generate enough traffic to bring a network to its knees. ADP datasheets have a default maximum of 10,000 rows to prevent an accidental Cartesian product from consuming all SQL Server resources for a substantial period.
Using SQL Punctuation and Symbols In addition to the comparison operators used for expressions, SQL uses commas, periods, semicolons, and colons as punctuation. The following list of symbols and punctuation is used in T-SQL, which follows ANSI standards, and Jet SQL; differences between the two SQL dialects are noted where appropriate: Commas Used to separate members of lists of parameters, such as multiple field names, as in Name, Address, City, ZIP. Square brackets Square brackets surrounding field names are required only when the field name includes spaces or other symbols including punctuation not allowed by ANSI SQL, as in [Order Details]. Square brackets also must surround names you assign in the grid to input parameters for Jet queries. Periods Separate named objects of a subordinate class. For example, if fields of more than one table are involved in the query, a period is used to separate the table name from the field name, as in [Order Details].OrderID. Four-part names of linked tables in FROM statements use the Server.Database.Schema.Table format.  For an example of linked tables and four-part names, see "Linking a Remote SQL Server Database," p. 844.
String identifiers (also called delimiters) Designate literal character values. ANSI SQL requires the single quote symbol (') to enclose literal string values. You can use the double quote (") or the single quote symbol to enclose literal values in Jet SQL statements. Using the single quote makes writing SQL statements in VBA easier. For backward compatibility with SQL Server 7.0 and earlier, T-SQL interprets the double quote as a square bracket. Wildcards Differ in Jet and ANSI SQL. ANSI SQL uses % and _ symbols as the wildcards for the LIKE statement, rather than the * (asterisk) to specify zero or more characters and ? to specify a single character in Jet SQL. The Jet wildcards correspond to the wildcards used in specifying DOS group file names. Date/time identifier Jet also requires the # symbol to enclose date/time values in expressions. ANSI SQL accepts date values in a variety of character formats enclosed by single quotes. Jet also uses the # wildcard for the LIKE statement to represent any single digit. ANSI SQL doesn't support use of the # symbol in queries. :and@identifiers for variables ANSI SQL uses : as a prefix to identify variables that receive parameter values (sometimes called host variables). T-SQL uses @ for conventional variables (including variables to receive parameter values) and @@ for variables whose values SQL Server supplies, such as @@IDENTITY, which returns the current value of the identity column of a table. Jet SQL handles input parameters by an entirely different method, and doesn't support declaring variables in SQL statements. !(the exclamation mark or bang symbol) Synonym for NOT in ANSI SQL. ANSI (not equal) operator> SQL uses != for not equal; T-SQL and Jet SQL use <>, but T-SQL also supports !=.
As the preceding list demonstrates, relatively minor differences exist in the availability and use of punctuation and symbols between ANSI and Jet SQL. These minor differences, however, can cause a major difference in the behavior of Jet and SQL Server Translating SQL Statements into QBE Designs   When you create a SELECT query in Query Design mode, Access translates the graphical query design into a Jet SQL SELECT statement. Similarly, the project designer for ADP generates T-SQL from your selections in the fields list and entries in the designer grid. The SQL translation operation is bidirectional; when you type an SQL statement into Jet's SQL window or the SQL pane of the project designer, the graphical QBE view changes. Jet queries make you change from Query Design to SQL view. Clicking the Check SQL Syntax button updates the designer's top and middle panes.
Tip If you want to compare Jet and SQL Server QBE translation directly, open two instances of Access 2003 one with Northwind.mdb as the current database and the other with NorthwindCS.adp as the current project.
If you haven't run NorthwindCS.adp to create the NorthwindCS MSDE database, see "Exploring the NorthwindCS Sample Project," p. 808.
Creating Jet Query Designs To generate Jet query designs (QueryDef objects) from SQL statements in Northwind.mdb, do this:  In the Queries page of the Database window, double-click the Create Query in Design View shortcut to open a new query in Design view.
Close the Show Table dialog, and click the SQL view button to open the Query1: Select Query SQL window. The window opens with a default SELECT; query fragment.  Type the SQL statement in the window (see Figure 21.1 and the following Jet SQL box), and select Design view to display the graphical version of the query.
Figure 21.1. Type the SQL statement in Jet's SQL window. Unfortunately, the standard font size is 8 points, which makes reading what you type difficult in high-resolution modes. You can't change the font size.   Click Run to display the result of your query in Datasheet view.
Saving the queries is optional. To generate a new query, delete the SQL text and start over.
Jet SQL The following lines are an example of a simple Jet SQL query statement using default character identifiers and the * wildcard: SELECT CompanyName, CustomerID, PostalCode FROM Customers WHERE PostalCode LIKE "9*" ORDER BY CompanyName; Jet SQL terminates statements by adding a semicolon immediately after the last character on the last line. If you don't type the semicolon, Access's query parser adds it for you. |
Note Examples of SQL statements in this book are formatted to make the examples more readable. Access doesn't format its Jet SQL statements. When you enter or edit SQL statements in the SQL window, formatting these statements so that commands appear on individual lines makes the SQL statements more intelligible. Indented lines indicate continuation of a preceding line or a clause that is dependent on a keyword in a preceding line. Use Ctrl+Enter to insert newline pairs (the carriage return and new line characters, CrLf) before SQL keywords. Jet SQL and T-SQL ignore spaces and newline pairs (called whitespace) when processing the SQL statement.
The preceding Jet SQL statement creates the query design shown in Figure 21.2. Figure 21.2. The Jet SQL statement of Figure 21.1 creates this simple query design. The Jet query parser causes changes you make in the SQL window to appear immediately in the Query Design window.  Working with SQL Server Views Views are SQL Server's most common incarnation of SELECT queries, but you also can return query result sets from in-line functions and stored procedures. To create a view from an SQL statement in the project designer for ADP, do this: In the Queries page of the Database window, double-click the Create View in Designer shortcut to open a new view. Close the Add Table dialog, and click the SQL button to add the SQL text pane. The pane contains a default SELECT...FROM fragment. Type the SQL statement in the pane. If you use the preceding Jet SQL query, the designer automatically adds the TOP 100 PERCENT to accommodate the ORDER BY clause, but it's a good practice to add the modifier yourself. You must change LIKE "9*" to LIKE '9%' to comply with T-SQL syntax (see Figure 21.3). Click Check SQL Syntax, and acknowledge the syntax verification message, to display the QBE version of the query in the top two panes (see Figure 21.4). Clicking either of the QBE panes also refreshes them. Figure 21.3. This T-SQL query requires TOP 100 PERCENT to support the ORDER BY clause and use of T-SQL compliant string identifiers and wildcards.  Figure 21.4. When you check the SQL syntax, the project designer reformats the SQL statement, adds the dbo. (schema) prefix to table name(s), and adds parentheses to the WHERE clause.  Click Run to display the result of your query in Datasheet view (see Figure 21.5). Unlike Jet, you must save the view to the SQL Server database before you can execute it. Figure 21.5. The query result set of the view design of Figure 21.4 is updatable, as indicated by the empty tentative append record. 
If you copy or type the preceding Jet SQL statement into the project designer's SQL pane without making the changes described in step 3, and then click the Check SQL Syntax button, you receive an "ADO error: Invalid column name '9*'" error message, because SQL Server's query parser interprets the double-quotes as a column identifier. Changing double- to single-quotes eliminates the error message, adds TOP 100 PERCENT, changes the statement's format, and removes the trailing semicolon. When you execute the SQL statement, the view has no rows because T-SQL doesn't recognize the * wildcard and interprets the LIKE predicate as the two-character string '9*'. Change * to %, and the view returns the expected Recordset. T-SQL Following is the T-SQL statement corresponding to the earlier Jet SQL version, with the required changes in bold type: CREATE VIEW vwTest1 AS SELECT TOP 100 PERCENTCompanyName, CustomerID, PostalCode FROM Customers WHERE PostalCode LIKE '9%' ORDER BY CompanyName The project designer's SQL pane hides the required [CREATE|ALTER] VIEW view_name AS statement. |
Using the SQL Aggregate Functions and Writing In-line Functions If you want to use the aggregate functions to determine totals, averages, or statistical data for groups of records with a common attribute value, you add a GROUP BY clause to your SQL statement. Tip T-SQL queries that use the SUM() aggregate function and GROUP BY clauses are the first step in the laborious process of emulating Jet crosstab queries with T-SQL.
For instructions on how to emulate crosstab queries with T-SQL, see "Emulating Jet Crosstab Queries with T-SQL," p. 918.
You can further limit the result of the GROUP BY clause with the optional HAVING qualifier: SELECT [ALL|DISTINCT] [TOP n [PERCENT]] aggregate_function(field_name) AS alias_name [, select_list] FROM table_names [WHERE search_criteria] GROUP BY group_criteria [HAVING aggregate_criteria] [ORDER BY column_criteria] The select_list includes the aggregate_function with a field_name as its argument. The field used as the argument of an aggregate function must have a numeric data type. The following list describes the additional required and optional SQL keywords and parameters to create a GROUP BY query: AS alias_name assigns a caption to the column. The caption is created in an Access query by the alias: aggregate_function(field name) entry in the Field row of the Query grid. GROUP BY group_criteria establishes the column(s) on which the grouping is based. In this column, GROUP BY appears in the Totals row of the Query grid. The GROUP BY clause is required for aggregate queries. HAVING aggregate_criteria applies one or more criteria to the column that contains the aggregate_function. The aggregate_criteria of HAVING is applied after the grouping is completed. The HAVING clause is optional. WHERE search_criteria operates before the grouping occurs; at this point, no aggregate values exist to test against aggregate_criteria. Access substitutes HAVING for WHERE when you add criteria to a column with the aggregate_function. The WHERE clause is optional, but seldom is missing from an aggregate query.
Jet queries and SQL Server views that use aggregate functions aren't updatable. Tip Current releases of most client/server RDBMSs support the ANSI SQL AS alias_name construct. Early versions of SQL Server, Sybase System 10+, and IBM DB2, as examples, substitute a space for the AS keyword, as in SELECT field_name alias_name, .... (SQL Server 7+ accepts either AS or a space.) Some ODBC drivers for these databases use special codes (called escape syntax) to change from Jet/ANSI use of AS to the space separator. If you use ADO with VBA or Jet SQL pass-through queries with some client/server RDBMSs, you might need to use the space separator, not the AS keyword.
Jet SQL The following Jet GROUP BY query is written in ANSI SQL, except for the # symbols that enclose date and time values: SELECT TOP 100 PERCENT ShipRegion, SUM(Freight) AS [Total Freight] FROM Orders WHERE ShipCountry='USA' AND OrderDate BETWEEN #1/1/1997# AND #12/31/1997# GROUP BY ShipRegion HAVING SUM(Freight)>=100 ORDER BY SUM(Freight) DESC The query returns a result set that consists of two columns: ShipRegion (states) and the totals of Freight for any shipping region where the total freight charges for that region is greater than or equal to 100 in 1997. The result set is sorted in descending order. The TOP 100 PERCENT modifier isn't required for Jet ORDER BY clauses, but doesn't affect performance. |
Figure 21.6 illustrates the Jet Query Design view for the preceding SQL statement. Figure 21.6. The only clue to the presence of a HAVING criterion is the >=100 criterion in the aggregate (Sum) column in Jet Query Design view.  The Jet query design of Figure 21.6 returns seven rows with ShipRegion and Total Freight columns. If you remove the HAVING expression, the result set has eight rows.  | If you encounter an Enter Parameter dialog when you attempt to run a Jet SQL query, see "Unexpected Enter Parameter Dialogs" of the "Troubleshooting" section near the end of the chapter. |
Table-valued functions can substitute for or augment views. To create an SQL Server in-line table-valued function from an SQL statement, do this: In the Queries page of NorthwindCS's Database window, click New Query to open the New Query dialog. Double-click Design In-line Function to open the Function1: Function window and the Add Table dialog. Close the Add Table dialog, and click the SQL button to add the SQL pane. Type the following T-SQL statement in the SQL pane. T-SQL You also must substitute single-quotes for the # date/time delimiters of the Jet SQL query to create the following T-SQL version: SELECT TOP 100 PERCENT ShipRegion, SUM(Freight) AS [Total Freight] FROM Orders WHERE ShipCountry='USA' AND OrderDate BETWEEN '1/1/1997' AND '12/31/1997' GROUP BY ShipRegion HAVING SUM(Freight)>=100 ORDER BY SUM(Freight) DESC |
Click the Check SQL Syntax button to verify the syntax and populate the upper two panes of the project designer (see Figure 21.7). Figure 21.7. Other than the name in the designer's title bar, in-line table-valued functions appear identical in the project designer to a view with the same SQL statement.   Click the Run button and save your function with a descriptive name, such as fn1997Freight.
Datasheet view of the result set returned by the function is identical to that of the Jet query, with the exception of the name.
Tip Use in-line table-valued functions instead of stored procedures to generate the equivalent of a parameterized view. Result sets returned by simple table-valued SELECT functions are updatable in Datasheet view. SQL Server views don't support parameters, and the Recordsets returned by stored procedures aren't updatable.
To review adding parameters to in-line functions, see "Creating a Parameterized Table-Returning Function," p. 825.
Creating Joins with SQL Joining two or more tables with the Jet query designer or the project designer uses the ANSI-92 JOIN...ON structure that specifies the table to be joined and the relationship between the fields on which the JOIN is based: SELECT [ALL|DISTINCT]select_list FROM table_name [INNER|LEFT [OUTER]|RIGHT [OUTER]]|FULL [OUTER]] JOIN join_table ON join_criteria [table_name [INNER|LEFT [OUTER]|RIGHT [OUTER]]|FULL [OUTER]] JOIN join_table ON join_criteria] [WHERE search_criteria] [ORDER BY column_criteria] The following list describes the elements of the JOIN statement: table_name [INNER|LEFT [OUTER]|RIGHT [OUTER]]| JOIN join_table specifies the name of the table that's joined with other tables listed in table_name. When you specify a self-join by including two copies of the field list for a single table, the second table is distinguished from the first by adding an underscore and a digit to alias the table name. Note One of the four types of joins, INNER, FULL, LEFT, or RIGHT must precede the JOIN statement in Jet queries, but INNER is optional in T-SQL. INNER specifies an inner join; LEFT specifies a left outer join; RIGHT specifies a right outer join. The OUTER qualifier for LEFT and RIGHT JOINs is optional. OUTER is optional for FULL joins.
ON join_criteria specifies the two fields to be joined and the relationship between the joined fields. One field is in join_table and the other is in a table that's included in table_names. The join_criteria expression usually contains an equal sign (=) comparison operator and returns a true or false value. Other comparison operators, such as If, and the value of the expression is true, the record in the joined table is included in the query.
The number of JOIN statements you can add to a query usually is the total number of tables participating in the query minus one. You can create more than one JOIN between a pair of tables, but the result often is difficult to predict. Jet SQL The following Jet SQL statement, which was created in Query Design view, defines INNER JOINs (also called natural joins) between the Orders, Order Details, Products, and Categories tables: SELECT Orders.OrderID, Products.ProductName, Categories.CategoryName FROM Categories INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Categories.CategoryID = Products.CategoryID The result set returns a list of products and their category for every order. You can copy this SQL statement from the Jet SQL window into the project designer for a view, table-valued function, or stored procedure to generate the same result set. |
If you copy the preceding SQL statement to the SQL pane of an SQL Server view or function and click Check SQL Syntax, the designer adds the dbo. prefix to all table and field references, reformats the SQL statement, and reverses the left-to-right order of the tables in the top pane. Figure 21.8 shows the T-SQL version with additional manual formatting of the JOIN statements to clarify the nesting of the JOIN and ON elements of the query. Figure 21.8. Multiple, nested JOIN statements aren't easy to write from scratch or read after you've defined them. If you omit the optional INNER prefixes, the SQL Server query parser inserts them when you test the syntax.  You can create joins in ANSI SQL with the WHERE clause, using the same expression to join the fields as that of the ON clause in the JOIN command. It's much simpler to write SQL statements using WHERE clauses to create relationships than to employ the JOIN syntax. Queries using WHERE clauses to create joins, however, aren't updatable. Jet SQL The following ANSI SQL-89 statement returns the same result set as the preceding Jet SQL statement in Figure 21.8, except that the result set isn't updatable: SELECT Orders.OrderID, Products.ProductName, Categories.CategoryName FROM Orders, [Order Details], Products, Categories WHERE Orders.OrderID = [Order Details].OrderID AND Products.ProductID = [Order Details].ProductID AND Categories.CategoryID = Products.CategoryID; In versions of SQL Server prior to 7.0, you could specify joins only with WHERE clauses. |
T-SQL If you type or copy the preceding Jet SQL statement into the project designer and then click Check SQL Syntax, the query parser insists on converting the WHERE clause to INNER JOIN structures: SELECT dbo.Orders.OrderID, dbo.Products.ProductName, dbo.Categories.CategoryName FROM INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID In this case, however, the JOINs aren't nested, which makes them easier to read. If you remove the "optional" INNER qualifiers, the query parser inserts them when you check the syntax or save the view. |
If you want to preserve WHERE clause join syntax in a T-SQL query, you must write the query in a query execution tool, such as the SQL Server Client Tools' Query Analyzer. Alternatively, you can save your query as an SQL script and execute it with MSDE's OSQL command-line tool. In this case, you must prefix the T-SQL statement with CREATE VIEW ViewName AS or CREATE FUNCTION FunctionName AS to create the view or function. You execute the view or function with a SELECT * FROM ViewOrFunctionName statement. For a brief explanation of the use of OSQL, see "Adding User Logins with the OSQL Utility," p. 778 .
Using UNION Queries  UNION 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, Customers and Orders by City, which has the special symbol of two overlapping circles, in the Database window. The NorthwindCS database doesn't include a corresponding view. You can create Jet and SQL Server UNION queries only with SQL statements. 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 number of fields in the field_list of each SELECT and UNION SELECT query must be the same. You receive an error message if the number of fields isn't the same. The sequence of the field names in each field_list must correspond to similar entities. You don't receive an error message for dissimilar entities, but the result set is likely to be unfathomable. In Jet UNION queries, the field data types in a single column need not correspond; if the column of the result set contains Jet numeric and text data types, the data type of the column is set (coerced) to Text. T-SQL doesn't support automatically coercing dissimilar data types in a single column. Only one ORDER BY clause is allowed, and it must follow the last UNION SELECT statement. You can add GROUP BY and HAVING clauses to each SELECT and UNION SELECT statement if needed.
The following SQL statement creates a UNION query combining rows from the Customers and Suppliers tables: SELECT TOP 100 PERCENT City, CompanyName, ContactName, 'Customer' AS Relationship FROM Customers UNION SELECT TOP 100 PERCENT City, CompanyName, ContactName, 'Supplier' FROM Suppliers ORDER BY City, CompanyName The preceding statement, which illustrates adding a field (Relationship) with a constant value (Customer) in each row, is valid in Jet SQL and T-SQL. Enclosing an element of a field list in single quotes defines it as a constant value. TOP 100 PERCENT in each SELECT statement is required for use of the GROUP BY clause in T-SQL. Jet SQL You can alter the preceding statement to include the CustomerID and SupplierID fields in a Jet SQL query, as follows: [View full width] SELECT TOP 100 PERCENT City, CustomerID AS Code, CompanyName, ContactName, 'Customer' AS Relationship FROM Customers UNION SELECT TOP 100 PERCENT City, SupplierID AS Code,  CompanyName, ContactName, 'Supplier' FROM Suppliers ORDER BY City, CompanyName
The syntax of the SQL statement illustrates the capability of Jet UNION queries to include values from two different field data types, Text (CustomerID) and Long Integer (SupplierID), in the single, aliased Code column. SQL Server won't execute this query. |
T-SQL You must use the T-SQL CAST() or CONVERT() function to conform dissimilar data types to a single data type for a column returned by a UNION query. Use the CAST() function as shown in the following example: SELECT TOP 100 PERCENT City, CustomerID AS Code, CompanyName, ContactName, 'Customer' AS Relationship FROM Customers UNION SELECT TOP 100 PERCENT City, CAST(SupplierID AS varchar(5)) AS Code, CompanyName, ContactName, 'Supplier' FROM Suppliers ORDER BY City, CompanyName You can't convert five-character CustomerID values to integers, but you can convert integer SupplierID values to a string. You must know the target SQL Server data type (varchar(5), a 5-character variable length character field in this example) to use CAST(). CAST() is the most common replacement for the VBA type conversion functions, such as CCur(). Open the table (Customers) in Design view to obtain the SQL Server data type for the CAST() function. |
After clicking Check SQL Syntax with a UNION query statement in the project designer's SQL pane and acknowledging the syntax check, a "Query Definitions Differ" error message appears. In some cases, the error message occurs before the syntax verification message. These error messages appear because the project designer can't represent UNION queries graphically; you can ignore them by clicking Yes in the Query Definitions Differ dialog. Figure 21.9 shows the Datasheet view of the preceding T-SQL query. Datasheet view of the equivalent Jet query is identical, except for the title bar caption. UNION queries, like queries returning SQL aggregate functions, aren't updatable. Figure 21.9. The result set for the Jet SQL and T-SQL versions of the UNION query against the Customers and Suppliers tables is the same.  Tip Use UNION queries to add (All) or other options 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 TOP 100 PERCENT 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.
For examples of using a UNION query to add an (All) item to a combo box, see "Adding an Option to Select All Countries or Products," p. 1244.
Implementing Subqueries Early versions of Access used nested queries to emulate the subquery capability of ANSI SQL. (A nested query is a query executed against the result set of another query. Similarly, a nested view executes against another view.) Jet SQL and T-SQL let 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, especially with SQL Server. The general syntax of subqueries is as follows: SELECT [TOP 100 PERCENT] field_list FROM table_list WHERE [table_name.]field_name IN (SELECT [TOP 100 PERCENT] select_statement [GROUP BY group_criteria] [HAVING aggregate_criteria] [ORDER BY sort_criteria]) [ORDER BY sort_criteria] T-SQL Following is the T-SQL statement for a sample subquery that returns names and addresses of Northwind Traders customers who placed orders between January 1, 1997, and June 30, 1997: SELECT TOP 100 PERCENT Customers.ContactName, Customers.CompanyName, Customers.ContactTitle, Customers.Phone FROM Customers WHERE Customers.CustomerID IN (SELECT Orders.CustomerID FROM Orders WHERE Orders.OrderDate BETWEEN '1/1/1997' AND '6/30/1997') ORDER BY Customers.CompanyName The SELECT subquery that begins after the IN predicate returns the CustomerID values from the Orders table against which the CustomerID values of the Customers table are compared. The only difference between Jet SQL and T-SQL syntax for subqueries is delimiter characters. |
Unlike UNION queries, Jet Query Design view and the project designer support graphical design of subqueries. 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 21.10 shows the view design generated by the preceding T-SQL statement; Figure 21.11 shows the result set. Figure 21.10. The subquery specified for the IN predicate appears in the Criteria cell of the field name designated in the WHERE clause of the project designer (shown here) and Jet's Query Design view.  Figure 21.11. This datasheet displays the result set of the view with the subquery design of Figure 21.10. Data such as ContactName is updatable, but you can't add new records to the Customers table because the primary-key field (CustomerID) isn't present.  Note The simple subquery design of Figure 21.10 offers no benefit over a conventional query that has an INNER JOIN between the Customers and Orders table and the WHERE clause applied to the query result set. Subqueries are most commonly used when the subquery's SQL statement is much more complex than that of this example.
|
