HOW TO: Create a Select Query in Microsoft Access 2000 (304361)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q304361
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2002 version of this article, see 304325.

IN THIS TASK

SUMMARY

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

back to the top

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.
back to the top

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.
back to the top

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.
back to the top

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.
back to the top

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.
back to the top

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.
back to the top


REFERENCES

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:

Microsoft Access 2000 Help

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:

209143 ACC2000: Creating a Crosstab Query with Multiple Value Fields

For additional information about creating select queries, click the article number below to view the article in the Microsoft Knowledge Base:

304361 ACC2000: How to Create a Select Query in Microsoft Access

For additional information about creating parameter queries, click the article number below to view the article in the Microsoft Knowledge Base:

304353 ACC2000: How to Create a Parameter Query in Microsoft Access

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:

304355 ACC2000: How to Convert a Select Query to an Action Query

For additional information about modifying query properties, click the article number below to view the article in the Microsoft Knowledge Base:

304357 ACC2000: How to Modify Query Properties in Microsoft Access

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:

207626 ACC2000: Access 2000 Sample Queries Available in Download Center



back to the top





Modification Type:MinorLast Reviewed:8/9/2004
Keywords:kbdownload kbhowto kbHOWTOmaster KB304361
©2004 Microsoft Corporation. All rights reserved.