Creating Queries in Access 2010

In this section:

• Creating a Query Using the Query Wizard

• Editing a Query in Design View

• Using Criteria to Focus Query Results

• Using Queries to Calculate Values

• Creating a Parameter Query

• Finding Duplicate Records

• Finding Unmatched Records

• Writing Query Results to a New Table

• Creating an Update Query

• Creating a Crosstab Query

• Finding the Largest and Smallest Values in a Field by Using a Query

Database tables store data, but even the best-designed table has limitations. For example, if a table holds more than a few dozen records, it’s difficult to look through the table and find records that meet a particular criterion. You might, for example, want to display all orders from a specific customer without having to wade through the entire table to find them.

Enter the query. A query is a Microsoft Access 2010 object that lets you find just those table records you’re interested in, whether you want to see all orders from customers in Germany or to identify customers who have never placed an order. You can also create queries that let you and your colleagues type the value for which they want to search. For example, rather than always search for orders by customers in Germany, you could create a query that asks which country to look for.

Introducing Query Types

When you want to retrieve table records that meet particular criteria, you create a query. The type of query you create, however, depends on the records you want to return and what, if anything, you want Access to do with the results.

The most basic query type is the select query, which reaches into one or more database tables and locates records. While you can have Access return every field in a record, you can also choose which fields are displayed in the results. For example, you could get information about customers that placed an order in a given month and, instead of displaying every field relating to the company, display just the company’s name. You can also limit the records returned by the query by specifying one or more criteria or rules the query uses when deciding which table rows to return. If your table contains data that relates to two different values, such as a company name and sales representatives, you can create a crosstab query to display the quantity of items sold by each employee to each company (as shown in the following figure).

A version of the select query is the parameter query. Like a select query, the parameter query uses one or more criteria to limit the records returned by the query. The difference, however, is that a parameter query lets the person running the query specify the criteria Access uses to decide whether or not to return a specific record. You can add a message to the criteria entry dialog box that lets the searcher know what kind of value to enter.

A separate type of query is the action query, which makes changes to the physical makeup of your database. You see two types of action queries in this chapter: the update query, which lets you change values in a table; and the make-table query, which writes query results to a new table in the current database (or another database entirely).

The final query type discussed in this section is the crosstab query. Unlike a select query, which presents its results in a worksheet, a crosstab query presents its results in a layout like that of a spreadsheet. Every value in the body of the query’s results is related to two other values. In this case, those values are your suppliers and your product categories.

As in a spreadsheet, you can choose the mathematical operation Access uses to summarize the data in the body of the crosstab query’s results. Available operations include finding a sum, average, the number of occurrences (as in the crosstab query results shown previously), or even the minimum or maximum value.

After you create a query (as shown following), you can display its results by double-clicking the query in the Navigation Pane. If the query is open in Design view, you can run it by clicking the Run button on the Design tab.