Special Select Queries

Much of the query design discussion to this point has focused on the basic elements of design for select queries. This subsection presents three extensions to select queries that significantly enhance the flexibility and adaptability of your applications. First, you will learn about parameter queries. Many developers relish parameter queries because they make applications interactive. Others like them because of their exceptional ease of use. Second, union queries allow you to form data in ways that joining just cannot accomplish. When you need to concatenate two or more recordsets with common fields, union queries may be your most efficient tool. Union queries, unfortunately, require a working knowledge of SQL. The last section in this chapter includes some review of SQL syntax and many samples demonstrating the application of the language. Third, subqueries are the tool of choice when you require intelligent criteria that adapt to the values in your database. This technique for creating dynamic criteria that change along with the entries in your database will often benefit from a medium to advanced knowledge of SQL.

Parameter Queries

Parameter queries get their name because they require the input of a parameter at run time before they can complete. When you create a parameter query in Design View, you can designate the prompt for the input parameter in the Criteria row. Use the prompt in the criterion statement as a variable that the user will set at run time. The user interface also enables you to declare a data type for each input parameter. Chapter 2 demonstrates how to code a parameter query in ADO. This section describes how to manage the same task through the user interface.

Figure 4-16 shows a parameter query design along with its built-in prompt dialog box. Notice that the prompt in the criteria statement for the UnitPrice field is enclosed within brackets. The preceding less than (<) symbol is used to set an upper limit for the return set's UnitPrice field values. If the user enters 35 to the parameter query prompt and clicks OK, the query responds with a list of all the products that have a price less than 35 dollars. The bottom window in Figure 14-6 displays the first several products satisfying the criteria as well as the total number of records meeting the criterion (56).

Figure 4-16. A parameter query and its return set.

The users of your applications will not typically run parameter queries from a query's Design view as was done in Figure 4-16. Figure 4-17 shows how the query looks when run from the database container. This particular query returns the customers in the UK, Germany, and France whose orders exceed the value specified by the user. It is this ability for users to control the return set that makes parameter queries highly interactive and dynamic.

Figure 4-17. A parameter query run from the database window.

Figure 4-18 shows the previous query in Design view. This query has two criteria. The first uses the In keyword to select customers that have headquarters in the UK, France , or Germany. This eliminates the need to specify three separate rows with a different country name in each row. The prompt in the Extended Price column follows a great than (>) symbol. Therefore, the query returns all records that have an extended price greater than the amount specified by the user.

Figure 4-18. The Design view of the query in Figure 4-17.

The query in Figure 4-18 accomplishes other tasks as well. It computes and sums extended price by company and sorts its return set alphabetically by country and in descending extended price order. Its sorting design causes the biggest customers to appear at the top of the customer list for each country.

Union Queries

Union queries are unique from several perspectives. First, they dramatically simplify the task of concatenating two or more recordsets. (Recall that joining two recordsets brings them together in a side-by-side rather than one-after-the-other fashion.) Second, you can only define union queries with SQL. The only two views for union queries are Datasheet and SQL views; they have no Design view at all. Third, you cannot directly update the field values in a union query's Datasheet view. Many select queries, which appear identical to union queries in Datasheet view, are dynasets, which let a user revise the tables behind a recordset. If you must edit the data resulting from a union query, you should use a make-table query to construct a copy that you can edit. This will still not change the original inputs to the union query, but it will at least allow changes to the copy of the resulting data.

Figure 4-19 shows a very simple union query that combines the FamilyMembers and AdditionsToFamilyMembers tables. If you feel uncomfortable about developing with SQL, you can always keep your union queries this simple for the basic design. Make the first statement a simple SELECT statement that lists the field names and the record source for the fields in the FROM clause. Your references to all remaining record sources must start with UNION SELECT, but you still list the field names and the record source. It is possible to do more advanced operations, but that is not essential. You can always combine the record sources just this simply and reserve more sophisticated manipulation of the combined record sources to another select query that relies on the union query output. You can perform these more sophisticated operations in Design view.

Figure 4-19. A union query in SQL view.

Notice from the SELECT and UNION SELECT statements that I am concatenating tables with the same field data types. This is a requirement for union queries. The tables, or at least the fields that you combine, need to have the same number, order, and types of columns. While you cannot update the source from the Datasheet view, the query recomputes each time you open it with the most recent data from its inputs. Notice that Access disables the Design view icon for the union query. You cannot examine a union query in Design view. You must design or edit it in SQL view, and you examine the impact of your SQL statements in Datasheet view.

Figure 4-20 reveals the operation of the union query in Figure 4-19. The FamilyMembers and AdditionsToFamilyMembers tables are on the left in Figure 4-20; the output of the union query is the datasheet on the right. As you can see from the sequencing of the FamID field, the query creates a new datasheet that appends the two rows in AdditionsToFamilyMembers table to the FamilyMembers table.

Figure 4-20. The two datasheets on the left are the inputs to the union query in Figure 4-19. The datasheet on the right is the output of the union query.

Subqueries

A subquery is a select query inside of another query. The subquery must always be a SELECT statement, but you can use it inside of any other query, such as a select or an action query. There are a few extra keywords that can moderate the behavior of the standard SQL SELECT keyword in a subquery. Because the SELECT statement returns values, the subquery represents an important option for building dynamic criteria in queries that use them.

The embedded SELECT statement on the Criteria row for the Lname column in the bottom window in Figure 4-4 is a subquery.

Figure 4-21 shows a query that returns the nonduplicated Lname values returned by a union query named qunFamilyMembersAdditionsMoreAdditions. The union query concatenates three separate record sources. The lower left window in Figure 4-21 is the return set from the union query. Notice that there is only one nonduplicated Lname value, namely Edelstein. The lower right window is the return set from the select query.

Figure 4-21. The top window is a query that finds non-duplicated field values in a record source. The bottom left and right windows are the input and output datasheets from the query.

The top window in Figure 4-21 is the subquery design for finding non-duplicated Lname values. Notice that it is similar to the query in Figure 4-4. Aside from the fact that it searches a different record source, its sole distinction is that the subquery starts with Not In instead of In. The expression containing Not In returns nonduplicated names while the one with In returns duplicated names.

NOTE

---

While you can combine the return sets from queries like those in Figures 4-4 and 4-21 to return all distinct names, Access offers more direct graphical and expression-based routes to the same result. More consideration of the expression-based approach appears later in this chapter. The graphical approach involves setting the Unique Values property of a query to Yes.