A Review of Query Types

While you may think of queries as tools for extracting information from one or more databases, there is a phenomenal amount of diversity in how they can accomplish this task. In addition, queries can perform other kinds of functions—especially if you include the data definition features of SQL and linking to remote data sources. This section surveys many query options that Access 2000 supports. This review of queries will equip you with the information you need to make an optimal choice about which type of query to use in a given situation.

Select Queries

Select queries let you create a subset of the information in one or more database tables. Developers have a rich array of options including selecting and processing data in any of several different ways. Use these options to determine the column and field values in the return set from a query. Select queries make good record sources for forms, reports, and web pages. Therefore, mastering the basics of select queries will enhance your work with these other application development components.

A query that selects a subset of the fields from a table is one of the most basic select queries. This type of query is convenient when you want to display just a few columns from a table that has many columns. Extracting some but not all the columns in a table can speed the operation of the query. Since Access computes a select query each times it runs, you will always deliver the most recent data to a user. When an application should not expose the most current data in a table, consider using criteria to restrict the row values that a query returns. Alternatively, you can use another type of query to create a new temporary table that contains a subset of the columns at a specific moment in time.

Setting criteria

Whether or not a select query chooses a subset from a data source, it can restrict the rows in the return set. You can specify which rows a query returns by setting its criteria. Use the Criteria row in the query design grid to set criteria. Programmatic options for setting criteria include the WHERE and HAVING clauses of a SQL statement. The WHERE clause applies to individual records in the record source for a query, but the HAVING clause can restrict return sets based on the values of a GROUP BY clause.

Select queries can return row sets that match a particular value or range of values. When using a table of geographic data, a select query can specify a zip code to return the corresponding city and state. If the records in a data source contain a Date/Time field, such as an order or invoice date, then you can select those on a specific date. You can also use comparison operators, such as greater than (>), less than (<), or not equal to (<>), to select a range of rows. You can match to a particular string value or to a range of values by using wild card parameters. Use special wildcard characters in criteria to designate a range of string values. For example, the criteria S*l matches Sal and Saul, because the * character matches any number of intervening characters. In contrast, the ? character matches just one missing character. Therefore, the criterion S?l matches Sal, but not Saul.

Using aggregate functions

Developers can use aggregate functions with SQL to summarize the records in a data source. Typical SQL aggregate functions count, sum, or average the records in a data source for a select query. You can additionally use aggregate functions to compute the variability of a record source with functions such as StDev or VarP. You can even compute the minimum or the maximum of a set of values in a field with aggregate functions.

Select queries can also apply SQL aggregate functions in combination with GROUP BY clauses. This enables a query to compute aggregate data for grouped records within a data source. Using aggregate functions along with a GROUP BY clause, an application can return the number of line items per order in a select query.

Using joins

You can base select queries on a single table, two or more tables, another query, or any combination of tables and select queries. When you select from more than one table, your application will typically link the tables on several common fields. If your database links tables via referential integrity settings, Access will automatically detect this and join them when you bring them together as the record source for a query. Access also detects common fields in other situations, allowing you to retain, modify, or replace automatically detected relationships between tables.

The default way to join tables is by combining all the records between two tables that have a matching value on a field. Joining the Northwind Orders and Orders Details tables in a simple select query can return all the line items for each order. In this situation, you only want line items that match orders. It is possible for related tables to have field values without matching entries in a joined table (for example, you can receive data from a legacy database that does not enforce referential integrity). Your application can detect unmatched records by forcing all the records for one of two joined tables into a return set. Then, all those combined records with a value from one table but a missing join field from the other table will point to unmatched records.

The result of any pair of joined recordsets can join with another table or query. If you are facile at SQL, you can tap several tables and queries with complex join relationships in a single SQL statement to develop a return set. Those learning SQL may find it safer to build queries graphically in the query design grid between pairs of tables or other queries. When database designers take this preliminary step, they can verify the result of joining two tables before combining the return set with another table or query.

Updating source data

One common outcome from a select query is a dynaset. (A dynaset is a recordset that stores primary keys instead of actual data.) A select query permits an application to modify the fields in records behind the query. Sometimes the fields behind a select query are not updateable. For select queries built on a single table or on two tables in a one-to-one relationship, you can always update the underlying records (unless another user locks them). Tables in a one-to-many relationship are usually updateable.

View the status bar of the Datasheet view for a query to determine if a field is updateable. If the field is not updateable and you need to be able to change it, consider directly accessing the table containing the field or redesigning the query to make it updateable.

Saving queries

When a query is particularly complex, it can be useful to save it and then refer to it in other queries. An example of this may involve a select query that joins two or more other queries and then applies SQL aggregate functions to generate data summaries. Save a complex query and reference it in other queries to help avoid syntax errors. Sometimes when you combine one or more queries to form another, your query logic can become too complex for a database engine to compute. In this situation, save one or more or your input queries as temporary tables. Then, use these tables as surrogates for the queries. Recompute the temporary tables whenever the input data to the original tables changes.

Action Queries

Instead of returning a set of rows like a select query, action queries perform a task against one or more tables. Action queries make it easy to start automating an application. You can design action queries graphically, yet they perform the essential functions of adding, deleting, and updating records. Because action queries use SQL to perform tasks, they can be used as sophisticated table commands for manipulating records. Experienced Access developers are probably wondering about the fourth Access action query—namely, the make-table query. This action query enables data definition as well as data manipulation. The make-table query is the sole focus of the section "Make-Table Queries."

Append queries

Append queries enable a developer to add records to one table based on those in a second table. Use the append query when your application acquires records from an exogenous source, such as a table from another computer. The source table must be in, or at least linked to, the current database. The destination table can be in the current database or in another database file. The field names in the two tables can differ, but you must have matching data types to append values from a field in one table to a matching field in another. The source table does not require all the fields to be in the destination table.

Several situations can generate errors with append queries. You should not generally attempt to append values for an AutoNumber field since these fields populate automatically. If you attempt to add a record with a duplicate primary key, your application will generate a key violation error. Access will not let you append records if their addition violates the table's validity or referential integrity rules.

Delete queries

Delete queries enable an application to automate the removal of records from a table. Use criteria to designate which records to delete. You can delete two or more records by using a single delete query. When your database schema specifies cascading deletes, a delete query will delete matching records from both the one side and the many side of a one-to-many relationship.

If you need to delete records from just the one side of the relationship, eliminate cascading deletes from the referential integrity settings. If you have to eliminate all the records on the many side of a one-to-many relationship with referential integrity, turn off the relationship. Then, execute the delete query. Finally, restore the referential integrity settings.

Update queries

The update action query does not operate on whole records, as do the delete and append queries. Instead, this action query revises values in selected fields. Designate particular rows by setting criteria. You can use this type of query to compute new prices when items increase by a constant percent. If prices increase for just one category of product, denote that product category in the criteria settings.

Other Types of Queries

A number of special queries further extend the usefulness of queries. These queries typically, but not always, extend or complement select and action queries.

Parameter queries

Parameter queries are a special type of query that can return rows or perform actions. At run time, a parameter query can prompt the user for input that controls how it performs. You can prompt for one or more inputs by using different data type specifications. You tell the parameter query what to do by inputting values to its prompts or by setting its parameters with VBA code before executing the query to control the return set or action that it performs. This allows the designation of a customer ID value at run time to determine the customer about which a select query returns information.

As an alternative to a parameter query, your application can reference a SQL string for a select or action query with string variables. Before executing the SQL statement in an ADO command, assign the string variables specific values. This can provide more flexible results than a parameter query since you can actually alter whole clauses in the SQL statement for a query. For certain cases, parameter queries offset these benefits with data typing and built-in prompts for values. In addition, parameter queries eliminate the need to refine string concatenation statements as you refine your query's SQL statement.

Union queries

The union query is a type of SQL query. The union query is especially interesting because of its novel behavior (it places tables one behind the other instead of side by side). Union queries bring together the fields from two or more tables. Instead of joining the records from tables in a side-by-side fashion, union queries append the records from one table directly after another. You must construct union queries by using SQL statements. You cannot design them graphically in the query design grid. Input the SQL for the union query directly into the query's SQL view. This sets union queries apart from all other queries discussed so far because you can create any of them graphically. With union queries, you gain the full power of the SQL language for such capabilities as specifying criteria and designating sort orders.

Crosstab queries

Crosstab queries accept a table or query as a data source and return sums, averages, or counts for one field based on two other categorical fields. This type of query requires a field that your application can count, sum, or average. In addition to specifying a field in which to present aggregate results, developers must designate row and column category fields. These must have discrete values that can serve as categories for reporting aggregation results. Access supports the creation of crosstab queries with a wizard and with Jet SQL. In addition, you can manually tweak a crosstab query design in its Design and Datasheet views.

Subqueries

A SQL subquery is a SQL SELECT statement nested inside another select or action query. Use the nested SELECT statement as an expression in a criterion for a field. The nested SELECT statement returns a value that, in turn, can act as a criterion for another query. Although you must write the subquery in SQL, you can nest the SQL in the query design grid. Furthermore, you can use another query to confirm that you properly designed your nested statement. Use the nested SQL statement just like any other criterion to specify a value or range of values for a return set.

Data Definition Operations

The SQL Data Definition Language (DDL) enables developers to create tables with SQL statements. The make-table query uses DDL to automate the creation of tables. One big attraction of make-table queries is that you can design them graphically. It is common to launch the design of a make-table query as a standard select query. After refining the select query to return precisely what you seek, convert the query type to a make-table.

Use SQL DDL to manually design tables with SQL statements. If you are comfortable with SQL this is a powerful and fast way to design tables. SQL DDL is an alternative to ADO. There are selected tasks, such as setting the step value and initial value for the AutoNumber data type or turning Unicode compression on and off, that Jet SQL exclusively provides. Of course, one major use for SQL statements by Access developers is to set the CommandText property of an ADO Command object. If your CommandType property is adCmdText, you must use a SQL statement to set the CommandText property. Invoke the Execute method for the command to launch the SQL statement.

Working with Remote Data Sources

When working with remote data sources, such as a Microsoft SQL Server or Oracle database, there are special rules for working with the data source and for optimizing your query performance. Four basic routes to remote data include attached tables, SQL pass-through queries, ODBCDirect, and OLE DB.

With Access 2000, you will mostly avoid access via ODBCDirect. Learning and using ADO with OLE DB providers is preferable since ADO/OLE DB is part of the Microsoft Universal Data Access strategy. ODBCDirect is on the way to becoming an obsolete technology that OLE DB will replace.

SQL pass-through queries specify queries based on a remote database engine's native SQL syntax. This type of query allows you to work directly with tables on the remote server instead of linking them. Since you can achieve the same result by using ADO with a more transferable syntax, pass-through queries are also obsolete.

Working with linked tables can enable your application to have cached access to the records in a remote data source. While this may slow the opening of an application, it can speed queries that have linked data sources, especially when the data are not changing frequently.

As you design your queries for remote data sources, it is important to plan your queries so that they perform as much of the query as possible on the computer running the remote database engine, which is generally vastly more powerful than a typical desktop computer. In addition, you reduce network traffic when you run the query on the server and pass just a small subset of data across the network. Tips for optimizing queries with remote data sources include the following:

• Restricting SQL aggregate functions to just those supported by the remote database engine (typically these are COUNT, SUM, MIN, MAX, and AVG)
• Setting the adLockBatchOptimistic lock type and invoking the UpdateBatch method to optimize working with a remote data source on a disconnected basis
• Avoiding the use of custom functions since they require local processing
• Using criteria that specify a fixed range, such as Between 100 And 1000 vs. open-ended criteria, such as >100
• Following general rules for query optimization, such as sort, search, and join on indexed fields, and using wildcard parameters with LIKE operators only at the end of a string search criterion

NOTE

---

SQL Server uses % instead of * for wildcard searches with the LIKE operator. If your query runs on a SQL Server database engine, including the new Microsoft Data Engine, be sure and use the new, alternative wildcard symbol in your queries.