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.
With a query, you can:
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 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.
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:
You may need to delete or rearrange tables or fields in your query in the QBE grid.
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.
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.
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).
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:
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.
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.
|>=||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|
|>=||On or after|
|<=||On or before|
|Between ... And ...||Insert dates (wrapped in # symbols) in place of the "..." to get a range between those values|
|Not||Not equal to|
|<>||Not equal to|
|Is Null||Records 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 Null||Records where any value is entered for the field (NULL is a special value for locating whether there is a value in a field)|
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:
A question mark ? will match a single character. For example:
Square brackets  are used to match a list or range of values. For example:
An exclamation mark ! is used to exclude a character, for example:
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:
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.
To create a relationship between tables in the first part of the QBE window:
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.