Describe the field list and the design grid in the Query window in design view

Open topic with navigation

The design grid appears at the bottom of the query design below the design canvas.

The design grid is divided into rows and columns. Each column represents a field that will be included in the output. For example, in the following image, the columns that will appear in the output are Asset ID, Asset Description, and Asset Installation Date.

You can adjust the width of the columns for viewing purposes by placing your mouse pointer on the column border and dragging the divider until the column reaches the desired width. Alternatively, you can double-click a column divider to expand the column to the width of the largest row. The column highlighted in yellow represents the currently selected column. As you scroll across the design grid, the first column containing the rows Field, Alias, Table, Sort, and so on remains stationary so that you can always see the row for which values are defined.

For each column, the design grid displays the following rows:

• Field: The name of the family field.

You can add or modify a field by clicking the down arrow and selecting the desired field. Alternatively, you can drag a field from any query source into a field cell of the design grid. You can remove a field by right-clicking it and choosing Delete.

• Alias: The alternate name for a given field. The alias will appear as the column heading in the query output.

• Table: The name of the family to which a field belongs.

• Total: A value that specifies how output will be grouped: Group By, Sum, Avg (average), Min (minimum), Max (maximum), Count, Expression, or Where. You can also create aggregate functions for fields using the Expression Builder.

  Note: This row appears by default for Crosstab queries. To make this row appear for Select queries, on the Query Tasks menu, click Options, and then click Show Totals.

• Crosstab (appears for Crosstab queries only): The cross-tabulation setting for the field, whether the field will be a Row Heading, Column Heading, or Value.

• Sort: The sorting method that will be used for this column in the output: Ascending, Descending, or (not sorted).

• Sort Index: A value that indicates the order in which sort criteria will be applied. For example, the first field to which you apply sort criteria receives a Sort Index of 1; this is the value on which results will be sorted first. The second field is assigned a Sort Index of 2, the third a 3, and so on.

• Include (appears for Select queries only): A flag that specifies whether or not the field will be included in the SQL code. If this check box is selected, the field will be included in the SQL code.

• Display (appears for Select queries only): A flag that specifies whether or not the field will appear in the results. If this check box is selected, the field will appear in the results.

• Hyperlink (appears for Select queries only): The URL that will be used to generate links for this field. If a ,URL has been defined, when you run a query, the content of the column will appear as a hyperlink.

• Criteria: The conditions that will be applied to the query results. Note that criteria for a field must be appropriate for the data type of that field.

• Or: Another set of conditions that will be applied to the query results.

Copyright © 2017 General Electric Company. All rights reserved.

Lesson 8: Designing a Simple Query

/en/access2016/sorting-and-filtering-records/content/

Introduction

The real power of a relational database lies in its ability to quickly retrieve and analyze your data by running a query. Queries allow you to pull information from one or more tables based on a set of search conditions you define. In this lesson, you will learn how to create a simple one-table query.

Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.

Watch the video below to learn more about designing a simple query in Access.

What are queries?

Queries are a way of searching for and compiling data from one or more tables. Running a query is like asking a detailed question of your database. When you build a query in Access, you are defining specific search conditions to find exactly the data you want.

How are queries used?

Queries are far more powerful than the simple searches or filters you might use to find data within a table. This is because queries can draw their information from multiple tables. For example, while you could use a search in the customers table to find the name of one customer at your business or a filter on the orders table to view only orders placed within the past week, neither would let you view both customers and orders at once. However, you could easily run a query to find the name and phone number of every customer who's made a purchase within the past week. A well-designed query can give information you might not be able to find out just by examining the data in your tables.

When you run a query, the results are presented to you in a table, but when you design one you use a different view. This is called Query Design view, and it lets you see how your query is put together.

Click the buttons in the interactive below to learn how to navigate the Query Design view.

One-table queries

Let's familiarize ourselves with the query-building process by building the simplest query possible: a one-table query.

We will run a query on the Customers table of our bakery database. Let's say our bakery is having a special event, and we want to invite our customers who live nearby because they are the most likely to come. This means we need to see a list of all customers who live close by, and only those customers.

We want to find our customers who live in the city of Raleigh, so we'll search for "Raleigh" in the City field. Some customers who live in the suburbs live fairly close by, and we'd like to invite them as well. We'll add their zip code, 27513, as another criteria.

If you think this sounds a little like applying a filter, you're right. A one-table query is actually just an advanced filter applied to a table.

To create a simple one-table query:

1. Select the Create tab on the Ribbon, and locate the Queries group.
2. Click the Query Design command.

3. Access will switch to Query Design view. In the Show Table dialog box that appears, select the table you want to run a query on. We are running a query on our customers, so we'll select the Customers table.
   

4. Click Add, then click Close.
5. The selected table will appear as a small window in the Object Relationship pane. In the table window, double-click the field names you want to include in your query. They will be added to the design grid in the bottom part of the screen. In our example, we want to mail invitations to customers who live in a certain area, so we'll include the First Name, Last Name, Street Address, City, and Zip Code fields.

6. Set the search criteria by clicking the cell in the Criteria: row of each field you want to filter. Typing criteria into more than one field in the Criteria: row will set your query to include only results that meet all criteria. If you want to set multiple criteria but don't need the records shown in your results to meet all of them, type the first criteria in the Criteria: row and additional criteria in the or: row and the rows beneath it. Because we want to find customers who either live in Raleigh or in the 27513 zip code, we'll type "Raleigh" in the City field and "27513" into the or: row of the Zip Code field. The quotation marks will search these fields for an exact match.

7. After you have set your criteria, run the query by clicking the Run command on the Design tab.

8. The query results will be displayed in the query's Datasheet view, which looks like a table. If you want, save your query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type the desired name, then click OK.

Now you know how to create the simplest type of query with only one table. In the next lesson, you'll learn how to create a query that uses multiple tables.

Challenge!

1. Open our practice database.
2. Create a new query.
3. Select the Customers table to include in your query.
4. Add the following fields from the Customers table to your query:
   First Name
   Last Name
   City
   Zip Code
5. Set the following criteria:
   In the City field, type "Durham" to return only records with Durham in the City field.
   In the Zip Code field, type "27514" in the or: row to return records that are either in Durham or zip code 27514.
6. Run the query. If you entered the query correctly, your results will include customers who live in Durham OR in zip code 27514.
7. Save the query with the name Customers who live in Durham.

/en/access2016/designing-a-multitable-query/content/

When you define multiple sort fields in design view describe how the sort fields must be positioned in the design grid?

What is the difference between a query datasheet and a table datasheet?

What command can you use in datasheet view to remove the display of one or more fields from the datasheet?

What happens when you run a query from query design view?