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.

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.

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.

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.

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

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.

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.