How to Create a Query in Access

Posted on by Ian

To create a query in Access 2013 or 2016:

  1. Click the CREATE > Query Design button on the Ribbon.
  2. Choose the tables to include in the query
  3. Choose the fields to include, and adjust the criteria
  4. Click the Run button (or just switch to Datasheet view)

The results of the query will be displayed.

You also have the option of saving your query. To save the query, right-click on the query tab. click Save, and name it at the prompt.

Below are screenshots showing the above steps.

The Query Design Button

Screenshot of the Query Design button on the Ribbon in Access 2013
Click the Query Design button to create a query in Design view. You could also use the Query Wizard button next to it to launch the Query Wizard, however, Design view gives you more control over the query.

Tables to Include in the Query

Screenshot of the Show Tables dialog in Microsoft Access 2013
The Show Table dialog allows you to choose which tables to include in the query. You can also include other queries to use within a query.

Query Design View

Screenshot of Query Design View in Access 2013
Query Design View allows you to specify the precise criteria for the query. You can choose which tables are shown in the results, which fields to use, add filtering criteria, and more.

The Run Button

Screenshot of the query Run button in Microsoft Access 2013
Clicking the Run button will run the query. Clicking the Datasheet view button next to it will also run the query.

The Query Results

Screenshot of query results in Datasheet view in MS Access 2013
The query results are displayed in Datasheet view.

Saving the Query

Screenshot of saving a query in Microsoft Access 2013
Saving a query

Modifying the Query

You can go back and forth between Datasheet view and Design view to modify your query. Design view allows you to specify the exact criteria for your query.

For example, you might want the query to return only those products from a certain company. Or you might need a list of all users who have spent over a certain amount. The query Design view enables this and much more.

Screenshot of the query in Design view with criteria added. MS Access 2013.
Design view allows you to add criteria with which to filter the results. Here, we’ve added criteria to filter the results to only those with a price over a certain amount (80000).

Result:

Screenshot of query results with only records worth more than $80,000
Only records with a value greater than $80,000 are returned. This is because we specified >80000 in the Criteria field.

More Options for the Query

You can extend your query further with options from the Ribbon. For example, clicking Totals will add a row into your query builder that will enable you to use the total sum of certain records.

Screenshot of the Totals option of the Ribbon in Access 2013.
The Ribbon contains further options for building your query, such as the Totals option.

Here’s how that affects the query designer:

Screenshot of Design view with the Totals row.
Clicking Totals in the Ribbon adds a Totals record in the query designer. You can then specify how each field will use the total.

In this case, we use the Total option to display the sum of all purchases from a given customer, and to group each customer.

Screenshot of query results including the SumOfPrice column.
Query results. We now see that the last column is called “SumOfPrice” and displays the sum of all products purchased by that customer.

Benefits of Design View

You can also use the Query Wizard to build basic queries. This can be handy for beginners who don’t feel confident enough to create queries in Design view. However, Design view allows you to build more complex queries, as you can be very specific with your criteria.

Behind the scenes of each query, Access is generating SQL code. If you know how to code in SQL, you could build your queries in SQL. However, Design view enables you to build complex queries without needing to know SQL.