Chapter 11 - Working with Advanced Queries

3 4

As you saw in Chapter 9, "Using Queries to Select Data," and Chapter 10, "Using Action Queries," select queries retrieve data and evaluate expressions based on that data, whereas action queries enable you to modify data as needed. In addition to these two important query types, Microsoft Access 2002 includes some advanced queries that enable you to carry out a variety of different tasks. Specifically, this chapter will explore the following types of queries:

• Parameter queries. A parameter query can be a select query or an action query. In either case, the query prompts the user for criteria before running.
• SQL-specific queries. In the simplest terms, a SQL-specific query is a query you use by running a SQL statement. Technically, all Access queries run SQL statements, but you use the design grid to create most of them. You must write a SQL-specific query in SQL form in the SQL window; the design grid doesn’t support SQL-specific queries.

SQL-specific queries include the following:

• Union. These queries combine data from tables or queries that have identical structures.
• Pass-through. These queries send uninterpreted SQL statements to an ODBC (open database connectivity) database server.
• Data-definition. These queries create, delete, and alter tables, and also create indexes in a database.
• Subquery. This query is a SQL SELECT statement inside another select query or action query.

In general, specialized queries offer functionality you just can’t get using a select query or an action query. In fact, you can’t even create a SQL-specific query in the query design window; instead, you must create the SQL string in the SQL window. With the exception of parameter queries, you might not use specialized queries often, but when the need arises, you might find that one of these queries is the only solution. For example, you might use a union query to combine several tables of historical data. Or you might use a few data-definition queries to programmatically create a new table or alter an existing one.

A subquery isn’t a type of query in the same sense as the other three advanced query types. Rather, subqueries provide a more efficient structure and often replace multiple nested queries. Be prepared to spend a little time testing, however. Subqueries don’t always perform better than nested queries (one query based on the results of another query), and they’re harder to work with because you can’t create them directly in Design view.