HOW TO: Create a Select Query in Microsoft Access 2000

In Microsoft Access, you can create a query to retrieve information from a table or from multiple tables. You can then use the query as the basis of a form, a report, or a data access page. This article shows you how to create simple queries in Microsoft Access and how to apply sorting and criteria to your queries. This article contains the following sections:

• What Is a Select Query?
• How to Create a Select Query by Using the Wizard
• How to Create a Select Query in Design View
• How to Add Criteria to Your Query
• How to Group Records and Create Sums and Other Totals
• How to Sort Records in a Query

What Is a Select Query?

A select query is the most common type of query. You use it to:

• Retrieve data from one or more tables by using criteria that you specify and then to display the data in the order that you want.
• Edit and update records in the datasheet of a select query (with some restrictions).
• Group records and calculate sums, counts, averages, and other types of totals.

How to Create a Select Query by Using the Wizard

The following steps show you how to create a query to retrieve information about customers and orders from the Northwind sample database that is included with Microsoft Access.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. On the View menu, point to Database Objects, and then click Queries.
3. On the Insert menu, click Query.
4. In the New Query dialog box, click Simple Query Wizard, and then click OK.
5. In the Simple Query Wizard dialog box, click the Customers table in the Tables/Queries list. Double-click each of the following fields to add them to the Selected Fields box: CustomerID, CompanyName, ContactName, ContactTitle.
6. On the same page of the Simple Query Wizard, click the Orders table in the Tables/Queries list. Click >> to add all of the fields from the Orders table to the Selected Fields box.
7. Click Finish. The Simple Query Wizard constructs the query, and displays the results in Datasheet view.

How to Create a Select Query in Design View

1. Start Microsoft Access.
2. Open the Northwind.mdb sample database.
3. On the View menu, point to Database Objects, and then click Queries.
4. In the Database Window, double-click Create Query in Design View.
5. In the Show Table dialog box, click Customers, and then click Add.
6. Repeat step 5 for the Orders table.
7. Click Close to close the Show Table dialog box.
8. In the Customers table field list in the top half of the query design window, double-click to add the following fields: CustomerID, CompanyName, ContactName, ContactTitle.
9. In the Orders table field list in the top half of the query design window, double-click the *. Adding the * is the equivalent of selecting all the fields from a particular table.
10. On the File menu, click Save. Type qryCustomerOrders for the name of the query.
11. On the Query menu, click Run to view the results of the query.

How to Add Criteria to Your Query

The following steps will modify the query that you created in the "Creating a Select Query in Design View" earlier in this article.

1. Start Microsoft Access.
2. Open the Northwind.mdb sample database.
3. On the View menu, point to Database Objects, and then click Queries.
4. Click the qryCustomerOrders query that you created in the previous section, and then click Design to open the query in Design view.
5. In the Orders table field list in the top half of the query design window, double-click the ShipCountry field to add it to the query design grid.
6. In the ShipCountry column in the query design grid, click to clear the Show check box. Because this field is included by selecting the * from the Orders table, clearing this check box will avoid including the field twice.
7. Enter UK in the Criteria row for the ShipCountry field.
8. On the File menu, click Save As. Type qryUKOrders for the name of the query.
9. On the Query menu, click Run to view the results of the query. Note that the query returns those orders that were shipped to the UK.

How to Group Records and Create Sums and Other Totals

The following steps show you how to create a totals query to retrieve summary information about orders from the Northwind sample database.

1. Start Microsoft Access.
2. Open the Northwind.mdb sample database.
3. On the View menu, point to Database Objects, and then click Queries.
4. In the Database window, double-click Create Query in Design View.
5. In the Show Table dialog box, click to select Orders, and then click Add.
6. Click Close to close the Show Table dialog box.
7. In the Orders table field list in the top half of the query design window, double-click to add the following fields: ShipCountry, Freight.
8. On the View menu, click Totals. Note that a row named Total is added to the query design grid.
9. Click in the Total row cell under the Freight column, click the arrow that appears, and then click Sum.
10. On the File menu, click Save. Type qryFreightByCountry for the name of the query.
11. On the Query menu, click Run to view the results of the query. Note that the query returns the total freight for each country in the Orders table.

How to Sort Records in a Query

1. Open the qryFreightByCountry query that you created in the "Grouping Records and Creating Sums and Other Totals" section in Design view.
2. Click in the Sort row cell under the Freight column, click the arrow that appears, and then click Descending.
3. On the File menu, click Save As. Type qryFreightByCountrySorted for the name of the query.
4. On the Query menu, click Run to view the results of the query. Note that the query returns the total freight for each country in the Orders table, sorted in descending order.

For additional information about creating queries in Microsoft Access, click the "Working with Queries" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site: For additional information about creating crosstab queries with multiple value fields, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about creating select queries, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about creating parameter queries, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about how to convert a select query to an action query, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about modifying query properties, click the article number below to view the article in the Microsoft Knowledge Base: To download a sample Microsoft Access database that contains over 20 query examples, including the techniques described in these articles, see the following article in the Microsoft Knowledge Base: