Access 2013

Introduction

Video: Designing a Simple Query

Watch the video (5:07). Need help?

The real power of a relational database is in the 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 course, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2013 sample database. You will need to have Access 2013 installed on your computer in order to open the example.

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.

View Options

Click the View drop-down arrow to switch to another view of your query. In most cases, you will only need to use two main views: Datasheet View and Design View.

Datasheet View lets you view your query results in the form of a table. Design View, featured here, allows you to view and modify the design of your query.

Run Query Command

After you have designed your query, click the Run command to view the results of the query in a table.

Object Relationship Pane

All of the tables you choose to include in your query will appear as small windows in the Object Relationship pane. Each window contains a list of every available field within that table.

The Design Grid

The bottom part of the Query Design View is called the Design grid. It contains a table that lists all the fields included in the query. Within this table, you can set criteria to specify which information the query should retrieve.

Sorting

You can sort the data retrieved by a query. Simply click in the Sort: row of the field you wish to sort, and select either an Ascending or Descending sort. By default, query results are not sorted.

Fields and Table Names

The first row of the Design Grid contains the names of the fields included in the query. Directly beneath each field name is the name of the table that field belongs to.

Showing or Hiding Fields

You may wish to include fields in the design of your query, but hide them in the query results. To hide a field, simply uncheck the checkbox in the Show: row of that field.

Query Criteria

Entering query criteria lets you specify exactly what type of information you want your query to retrieve. Simply type the desired criteria in the Criteria: row of the field you wish to search.

Here, the criteria have been set so that the query will search for records with "Raleigh" in the City field, and zip codes other than 27610.

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 imagine that our bakery is having a special event and we want to invite our customers who live nearby, since 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.

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.
   Clicking the Query Design Command
3. Access will switch to Query Design view. In the Show Table dialog box that appears, select the table you would like to run a query on. We are running a query about our customers, so we'll select the Customers table.
   Selecting a table to use in the query
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 would like 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. We aren't planning on calling or emailing our customers, so we don't have to include the Phone Number or Email fields.
   Selecting fields to add to the query
6. Set the search criteria by clicking on the cell in the Criteria: row of each field you would like 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 them all, type the first criteria in the Criteria: row and additional criteria in the or: row and the rows beneath it.
   
   For this one-table query, we'll use simple search criteria.
   • We want to find our customers who live in the city of Raleigh, so in our City field we'll type "Raleigh". Typing "Raleigh" in quotation marks will retrieve all records with an exact match 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. Because we want to find customers who either live in Raleigh or in the 27513 zip code, we'll type "27513" in the or: row of the Zip Code field.
   Setting the search criteria so that the query will find records with either "Raleigh" in the City field or "27513" in the Zip Code field.
7. After you have set your criteria, run the query by clicking the Run command on the Design tab.
   Clicking Run
8. The query results will be displayed in the query's Datasheet View, which looks like a table. If desired, save your query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type in the desired name, then click OK.
   Naming the new query to save it

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 an existing Access database. If you want, you can use our Access 2013 sample 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 zip code 27514.
7. Save the query with the name Customers who live in Durham.