Which query is used to retrieve data from one or more tables and display the record set in a datasheet?

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 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.

Configuring sorting, visibility, and criteria in queries

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.

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.

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

Which query is used to retrieve data from one or more tables and display the record set in a database?

Which query retrieves data from one or more tables and display the record set in datasheet?

Which query is used to retrieve data from one or more tables and display the record set in a database SELECT Update Cross?

Which query is used to SELECT data from one or more table and display in the desired format?