Video: Create basic queries

Try it!

For quick searches and questions, create a basic query to find the data you need and get your answers.

Create a query in Design view

Design view gives you more control over the query you create than the Query Wizard does.

1. Open Query Design view

   • To get started, select Create > Query Design.

     

2. Add data sources

   First, add the table or query that contains the data you want to see. (Yes, you can create new queries from saved queries. For example, suppose you built a query that finds all the products from your suppliers and sorts them from highest to lowest sales numbers. Use that query as a data source for a new query that, for example, finds sales of products from suppliers in a specific region.)

   1. In the Show Table dialog box, select the Both tab.

   2. Select the data sources you want, and, after each source, select Add.

   3. Close the dialog box.

3. Add output fields

   The output fields are the data that you want displayed or included in the result set for the query.

   • Drag the fields you want from the data source in the upper pane down to an empty cell in the Field row in the lower pane.

     

   Use an expression as an output field

   To perform calculations or to use a function to produce query output, include an expression as an output field. The expression can use data from any of the query data sources, in addition to functions, such as Format or InStr, and can contain constants and arithmetic operators.

   1. In an empty column of the query design grid, select the Field row.

   2. On the Design tab, in the Query Setup group, select Builder.

   3. Type or paste your expression. Preface your expression with the name you would like to use for the expression output, followed by a colon. For example, to label the expression "Last updated," start your expression with: Last updated:

      Alternatively, use Expression Builder to build your expression with Expression Elements, Expression Categories, and Expression Values.

   Note: Expressions are powerful and flexible, with many options. For more information, see Build an expression.

4. Specify criteria (optional)

   Use criteria to limit (to field values) the records that your query returns. For example, you might only want to see the products with a unit price of less than $10.

   1. To specify criteria, first drag the fields containing the values into the lower pane.

      Tip: By default, when you add a field, its data returns in the query results. If you’ve added the field you want already, you don’t need to add it again to apply criteria to it.

   2. If you don’t want the field contents to appear in your results, in the Show row for the field, clear the check box.

   3. In the Criteria row for the field, type an expression that the field values must satisfy (if the record is to be included in your results). For example, <10. (For more examples, see Examples of query criteria.)

   4. Specify any alternate criteria in the Or row, below the Criteria row.

   If you specify alternate criteria, records are selected if the field’s value meets any of the listed criteria. For example, if you included the State field from your Customers table, specified CA as the criteria, and included Or rows for ID and WA, you get records for customers living in any of the three itemized states.

5. Summarize data (optional)

   You might want to summarize data, especially if your data is numeric. For example, you might want to see the average price or total sales.

   1. If necessary, add the Total row to the query design grid in the lower pane. In Design view, on the Design tab, in the Show/Hide group, select Totals.

   2. For each field that you want to summarize, in the Total row, select the function to use. The available functions depend on the data type of the field.

6. Run or save the query

   • To run the query, in the Results group, on the Design tab, select Run.

   • To save your query for later use, on the Quick Access Toolbar, select Save . Type a descriptive name for the query, and then select OK.

Want more?

Excel training

Outlook training