ACC97: How to Create a Select Query in Microsoft Access (304444)

MORE INFORMATION

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

These steps show you how to create a query by using the query design grid in Design view of a query to retrieve information about the Customers and the Orders tables from the sample database Northwind.mdb that is included with Microsoft Access.

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. Click New.
4. In the New Query dialog box, click Design View, and then click OK.
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 field list for the Customers table, double-click each of the following fields to add them to the query design grid:
   • CustomerID
   • CompanyName
   • ContactName
   • ContactTitle
   
   
9. In the field list for the Orders table, double-click the asterisk (*). Adding the * is the equivalent of selecting all the fields from a given table.
10. On the File menu, click Save. Type qryCustomerOrders for the name of the query, and then click OK.
11. On the Query menu, click Run to view the results of the query.

How to Add Criteria to Your Query

These steps modify the query named qryCustomerOrders that you created in the "How to Create a Select Query in Design View" section earlier in this article.

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. Click the qryCustomerOrders query, and then click Design View to open the query in Design view.
4. In the field list for the Orders table, double-click the ShipCountry field to add it to the query design grid.
5. In the ShipCountry column in the query design grid, click to clear the Show check box. Because the ShipCountry field was already included in the query when you clicked the * from the Orders table, clearing the Show check box avoids including the ShipCountry field twice in the query.
6. Enter UK in the Criteria row for the ShipCountry field.
7. On the File menu, click Save As. Type qryUKOrders for the name of the query, and then click OK.
8. 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 sample database Northwind.mdb.

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. Click New.
4. In the New Query dialog box, click Design View, and then click OK.
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 field list for the Orders table, double-click the Freight and ShipCountry fields to add them to the query design grid.
8. On the View menu, click Totals. This adds a row named Totals to the query design grid.
9. Click in the Totals row under the Freight column, click the arrow, and then click Sum.
10. On the File menu, click Save. Type qryFreightByCountry for the name of the query, and then click OK.
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

The following steps modify the query named qryFreightByCountry that you created in the "How to Group Records and Create sums and Other Totals" section earlier in this article.

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. Open the qryFreightByCountry query in Design view.
3. Click in the Sort row under the Freight column, click the arrow, and then click Descending.
4. On the File menu, click Save As. Type qryFreightByCountrySorted for the name of the query.
5. 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.