USING ADVANCED QUERY OPERATIONS
Select and Action queries are quite useful and can answer many problems you face. Often, however, you must summarize records from a table in order to provide a reasonable amount of information to show on a form or report. By nesting queries atop each other, using subselect statements, and capitalizing on some of the more advanced features of Access, you can really tap the power of queries.
Some Summary Queries
Summary queries (often called total queries) allow you to total groups of records and do some statistics on the values. To create a summary query, use the View menu's Totals command while in the query design grid.
Note
You can combine total queries with Append or Make Table queries. They can't be used with Update, Delete, or crosstab options in the same query.
Table 8.3 summarizes the functions available when a summary query is run. Sum() and Count() solve most of the problems you'll encounter.
In Figure 8.15, you can see a regular Select query that displays the subtotal and tax for each customer. Figures 8.16 and 8.17 show the Sum() and Count() functions. (These functions don't include records with a null in the field.) See the later section “Solving Problems with Queries” for information on nesting summary queries to perform complex query operations.
Figure 8.15. These records are the base, ready to be summarized by a totals Select query.
Figure 8.16. Here's the Design view of a summary query, with the Sum() and Count() functions being used.
Individual or All Choices Using Union Queries
You use a union query to combine two tables that have the same structure, with all the records from each table included. One way to use a union query is to add a value (such as <<All Customers>>) to the top of a list of choices, and then include all customers in a report when this choice is made. Chapter 10, “Expanding the Power of Your Forms with Controls,” covers union queries in more detail and provides a complete example.
Nested Queries
When you save queries in a QueryDef, you can reference them in many of the same places that you can reference tables, including from the query design grid. If a whole series of reports will be based on the same general criteria, it sometimes makes sense to build one query with all the criteria in it and use it as the foundation to build other queries.
This technique can pay big dividends during the development of reports that will be printed from large amounts of data. Rather than base a summary on a base table, base it on a query that returns the first 50 rows by using the Top Values query property. In Figure 8.18, the Select query qryEmployee50 is limiting the data to only 50 records from the tblEmployee table.
Figure 8.18. By using the Top Values property, you can choose or enter the Top Values or Top Percentage (by adding a % after the 50).
With qryEmployee50 as the base query, you can build a new query, qrptEmployee, with all the criteria for selecting and sorting the employee data (see Figure 8.19). You can then use the query as input to a report and for testing.
Subqueries
Using a subquery in the criteria for a query allows you to base criteria on the results of another query without creating and saving another QueryDef. The Not operator has a special application for finding a list of records that aren't in another table.
By using the example with employees and companies, you can obtain a list of all companies that don't have employees by using Not In (), as shown in the qryCompaniesWNoEmployees query in Figure 8.20. The subselect limits the query to include only the company records that don't have matching CompanyIDs in tblEmployee.
Queries That Create Objects (DDL)
Select and Action queries allow you to manipulate records within the database. DDL queries allow you to create objects in the database. Although DDL provides the functionality, you can perform the same operations through DAO. For information on DAO, refer to Chapter 5, “Working with Data Access Objects.”