Microsoft KB Archive/304325

From BetaArchive Wiki

Article ID: 304325

Article Last Modified on 3/29/2007


APPLIES TO

  • Microsoft Access 2002 Standard Edition


This article was previously published under Q304325

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 97 version of this article, see 304444.

For a Microsoft Access 2000 version of this article, see 304361.

IN THIS TASK

SUMMARY

REFERENCES

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 included with Microsoft Access 2002.

For additional information about viewing the Northwind sample database, click the following article number to view the article in the Microsoft Knowledge Base:

276376 ACC2002: Where Are the Northwind Sample Files?


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 2002.
  2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
  3. On the View menu, point to Database Objects, and then click Queries.
  4. On the Insert menu, click Query.
  5. In the New Query dialog box, click Simple Query Wizard, and then click OK.
  6. 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.
  7. 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.
  8. 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 2002.
  2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
  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 2002.
  2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
  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 2002.
  2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
  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 more information about creating queries, click Microsoft Access Help on the Help menu, type create a select query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

This information is also available on the World Wide Web at the following Microsoft Web site:

Create a select or crosstab query


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

304349 HOW TO: Create a Crosstab Query in Microsoft Access 2002


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

304352 HOW TO: Create a Parameter Query in Microsoft Access 2002


For additional information about how to convert a select query to an action query, click the following article number to view the article in the Microsoft Knowledge Base:

304354 HOW TO: Convert a Select Query to an Action Query in Access


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

304356 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 The "Access 2000 sample: query topics" database available in Download Center




back to the top









Additional query words: OfficeKBHowTo inf

Keywords: kbquery kbdownload kbhowtomaster KB304325



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/304325&oldid=329304’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki