Skip to Main Content
University of York Library
Library Subject Guides

Essential Access: a Practical Guide

Query essentials

In this section, we'll explore what a query is, how to construct a query from scratch by adding tables, fields, and configuring any sorting and filter criteria, and how to work with queries in which data is combined from multiple tables.

What is a query?

Queries are the bread and butter of databases: they are crucial for working with them. But what actually are queries?

Queries are a way of interrogating databases, which means retrieving particular information so you can view, modify, and delete records in your database. Some queries allow you to view subsets of your data, whilst others allow you to perform actions on your data (we'll get onto these later in this guide).

Queries do not themselves store records. Instead, when you create a query, you are creating the instructions that describe which records to retrieve from the database's tables. You run a query to see the records that these instructions will retrieve at that point, creating a temporary dataset that matches the query's parameters.

This means that you can create many subsets of your data, whilst actually only storing the data once, in the individual tables. Saved queries just store the instructions for how to retrieve the subset of data you are looking for.

Constructing queries

With a query, you can:

  • Choose which fields of the table are displayed
  • Specify criteria so only the matching records are shown
  • Define sorting orders
  • Combine data from multiple related tables

Queries are created in Access using the Query By Example (QBE) grid, using Design view. Their results are seen in the Datasheet view. You can switch between views from the Design tab on the ribbon, where you can use the View option to switch between Design view, Datasheet view, and SQL view (which shows your query as it is written in SQL, the database coding language).

The first part of the QBE (Query By Example) grid allows you to access the selected tables and add fields to the query and the second part displays the chosen fields and allows you to select filtering criteria or sort them.

The top pane of the QBE grid shows any tables selected to be used by the query. The bottom pane shows the fields from these tables that will be used in the query, either displayed or used to sort or filter.

Making a new query

The best way to make a new query is to go to the Create tab, then in the Queries section choose Query Design. There is a Query Wizard, but this is generally more difficult than designing the query from scratch.

The process for creating a query using the Query Design option is as follows:

  1. Choose Create > Queries > Query Design to begin a new query.
  2. Using the Show Table dialogue box that automatically appears, either select the table you want to include and click Add, or double click on any tables you want to include. Then click Close (you can reopen the dialogue from the Show Table button on the ribbon.
  3. Add the fields you want to include in the query by double-clicking on the field name in the table list or dragging the field name onto the bottom part of the grid. You may need to resize the boxes to see all of the fields at once.

Editing which tables and fields appear

You may need to delete or rearrange tables or fields in your query in the QBE grid.

  • To delete a table from the QBE grid, select the table and press the Delete key on the keyboard.
  • To delete a field from the bottom part, select the field and press the Delete key on the keyboard. It can be tricky to select a field, so there's guidance below.
  • To move a field (as the order reflects the order they will appear, and also the sort order of the fields), select the field and drag it to a new location.

To select fields on the grid, position the mouse pointer at the top of the grid until it changes to a black arrow, then click to select the column. If this is successful, the column will be highlighted in black.

Click at the top of the field column to select the field, then drag to rearrange.

Configuring sorting, visibility, and criteria in queries

The QBE grid has sort, show, and criteria options for setting the sort order, toggling visibility of fields, and setting filter criteria.

Sorting

A sort order can be applied to one or more fields, including text and numeric fields. To do this, click on the Sort: row for the field in question and select Ascending or Descending. The sorting is applied from left to right, so you may have to reorder the fields to get the result you want.

Visibility

The Show: option must be ticked for a field to display in the Datasheet view of the query. You might want to hide fields if they are a criteria for a filter or are used for sorting, but don't need to display.

You can also combine sort and show to have the right order for sorting, but have the field duplicated elsewhere so it also displays in the order you want (for example, if you want to sort by last name first, but see first name first, you could have the last name field sorting, but not visible, and then the first name field and the last name field again, this time visible).

Criteria

Using criteria to define filters to be applied to the fields in your query is often a crucial part of query design. Enter your criteria in the box in the corresponding field, bearing in mind the following tips:

  • Criteria for text fields should be enclosed in double quotation marks (though if you forget, Access will often add these in once you click away from the box). They are not case sensitive.
  • Criteria for numeric fields must not be in quotation marks (as this means they'll be treated as text instead).
  • Criteria for date fields must be enclosed in hash characters e.g. #01/01/1993#
  • Criteria set on more than one field must both be met for a criteria to be display (as criteria across separate fields are connected with an AND, if you know Boolean operators).
  • You can list multiple criteria in the same field, by using the box below labeled or: to add multiple criteria. The query will display records that match either criteria in this case (as it is an OR connection).
  • You can use symbols and wildcard syntax in your criteria to refine ranges or represent unknown characters. We'll explore this in the next section.

Text field criteria must be in double quote marks, but are not case sensitive. Date field criteria must be in hash symbols.

Tip

Whilst it is best to configure sorting and filtering in a query, you can also apply filters and sorts directly in the datasheet view of a query or table, using the arrows next to each field heading (similarly to how this works in a spreadsheet). There are also sorting and filtering tools on the Home tab. However, it is better to save your sorting and filtering as a query.

Conditions, ranges and wildcards in query criteria

You might not want to use a single value as a criteria, but instead you might need to use a range, define what something is not equal to, or use wildcards that allow you to not define all the characters in text (e.g. to find words and phrases that start or end with particular words or characters).

Here are some of the useful commands you can use as part of your criteria to define exactly what you need in your query.

Number ranges
>Greater than
<Less than
>=Greater than or equal to
<=Less than or equal to
Between ... And ...Insert values in place of the "..." to get a range between those values
Date ranges
>After
<Before
>=On or after
<=On or before
Between ... And ...Insert dates (wrapped in # symbols) in place of the "..." to get a range between those values
Negatives
NotNot equal to
<>Not equal to
Is NullRecords where no value is entered for the field (NULL is a special value for locating whether there is a value in a field)
Is Not NullRecords where any value is entered for the field (NULL is a special value for locating whether there is a value in a field)

Wildcards for partial matching

As well as ranges, Access allows the use of wildcards that represent one or more characters when specifying criteria. When using wildcards, the expression must be preceded by the keyword Like.

The asterisk symbol * matches 1 or more characters. For example, used in particular fields:

  • Like "ch*" would return any names that begin with Ch such as Charles and Charlotte.
  • Like "*.co.uk" would return any email addresses that end with .co.uk
  • Like "*Theory*" would return titles like 'Quantum Theory for Beginners' and 'Thermodynamics Theory'

A question mark ? will match a single character. For example:

  • Like "al?n" would return 'Alan' and 'Alun' but not 'Allen'.

Square brackets [] are used to match a list or range of values. For example:

  • Like "[a,e,i,o,u]*" returns any value beginning with a vowel
  • Like "[a-d]*" returns any value starting with the letter a, b, c, or d

An exclamation mark ! is used to exclude a character, for example:

  • Like "[!a]*" returns all values that do not begin with the letter a

Combining data from multiple tables in queries

A query can contain data from two or more related tables if needed. This can be done just by adding more than one table and then adding fields from multiple tables into the query.

When multiple tables are added to the QBE window, their relationship must be specified or unexpected results will be returned. There are three possibilities:

  • There are already pre-defined relationship between tables (which we'll cover under Designing tables and databases). These will be shown when tables are added, in the top section of the QBE window.
  • A predefined relationship may not exist between two tables, but they contain matching field names. A relationship will be created automatically when the tables are added.
  • No predefined relationship or obvious corresponding field names exist between two tables. No relationship will be created and you will need to make one.

Whatever the case, the important thing is that nay tables you add to the QBE grid must be joined correctly, reflecting the relationships between your data.

Some tables might have a predefined relationship that appears in the QBE grid connecting two fields, and others may have no connection.

Creating a relationship

To create a relationship between tables in the first part of the QBE window:

  1. First identify two fields (one from each table) that form the connection. If you cannot identify any common fields, you have probably omitted one or more linking tables.
  2. Drag the field from one table onto the corresponding field of the related table.
  3. Check the correct fields are used to form the relationship.

A relationship is created by dragging one field onto another

Constructing multi-table queries

Once you have added more than one table and ensured there are suitable relationships, the methods for adding/removing tables, adding/removing fields, and configuring the query are essential the same as for single-table queries.

If you need to add a field that links to tables, it obviously will appear in both tables, so you might wonder which to add to the query. With simple queries this often won't matter, but with more complex queries you might have to check you're getting what you expect.

Exercises

Make sure you've downloaded the Essential Access exercise files before attempting these exercises.

Feedback
X