Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 36 ยท Queries

Select Queries and Query Design View in Access

Build Access select queries in Query Design view: pick fields, sort, hide columns, set Top Values and Unique Values, and read the SQL Access writes for you.

Tables hold your data, but you rarely want to see a whole table. You want the customers in one city, the ten most expensive products or last month's orders sorted by date. A query is a saved question you ask of your tables. The most common kind is the select query, which returns a set of rows and columns and changes nothing.

Access offers a visual Query Design view where you drag fields into a grid, and an SQL view where you can read or write the same query as text. Learning to move between the two is the fastest way to become fluent. This lesson uses a small shop database that the later query lessons reuse.

The sample data

The examples use four tables: Customers, Products, Orders and OrderDetails. Each runnable example below rebuilds a compact version, so you can run any of them alone. First, a look at the data and the simplest query, SELECT *:

SQL A first select query

Press Run (or Ctrl + Enter) to see the result here.

SELECT * returns every column. In real work, ask only for the columns you need.

Creating a query in Design view

  1. Choose Create > Query Design.
  2. In the Add Tables pane (or Show Table dialog), double-click Products and close it.
  3. The table appears as a small box listing its fields. Double-click ProductName, Category and UnitPrice. Each lands in a new column of the grid below.
  4. Click Run (the red exclamation mark) on the Query Design tab, or switch to Datasheet View.

Alternatively, drag a field from the box into the grid, or double-click the asterisk * to add all fields. The asterisk is convenient while exploring and risky in saved queries, since forms and reports built on it change when the table changes.

The query is not saved until you press Ctrl+S and name it. A common convention is to prefix it with qry, such as qryProductsByCategory, though this is a matter of taste.

The query design grid

The lower half of the window is the design grid. Each column is one field in the result. The rows are:

Row Purpose
Field The field name or an expression (calculated field).
Table The table the field comes from. Useful when two tables share a field name.
Sort Ascending, Descending or blank.
Show A check box. Clear it to use a field for criteria or sorting without displaying it.
Criteria A condition a row must meet. Conditions in one row are joined with And.
or Extra rows of criteria. Conditions on different rows are joined with Or.

The criteria rows are the subject of the next lesson. For now, focus on choosing fields, sorting and showing.

Sorting

Set Sort to Ascending or Descending in the grid. If you sort by more than one field, Access applies them left to right: the leftmost field with a sort is primary. To sort by Category and then by price, put Category to the left of UnitPrice. If you want a sort field that does not display, clear its Show check box, but remember the position still counts.

SQL Sorting by two fields

Press Run (or Ctrl + Enter) to see the result here.

The Access SQL for this query is nearly identical; Access writes ORDER BY Products.Category, Products.UnitPrice DESC;, qualifying each field with its table name.

Renaming columns with aliases

To give a column a different heading in the result, type a new name followed by a colon in the Field row: Price: UnitPrice. In SQL view, Access writes SELECT UnitPrice AS Price. Aliases do not change the table. They are useful for friendlier headings and for naming calculated fields.

Top Values

Sometimes you want only the first few rows: the five most expensive products, or the single oldest order. On the Query Design tab, find the Return box (Top Values) and choose 5, 25, 100, All or a percentage, or type your own number. Combine it with a Sort. Without a sort, "top" is meaningless.

SQL
SELECT TOP 3 ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

Access SQL uses TOP. Standard SQL uses LIMIT (SQLite, MySQL, PostgreSQL) or FETCH FIRST. One Access quirk: TOP returns ties. If the third and fourth rows have the same price, TOP 3 returns four rows. Add a unique field, such as the ID, as a final sort to break ties.

SQL Top three by price

Press Run (or Ctrl + Enter) to see the result here.

Unique Values and Unique Records

Open the query's Property Sheet (F4 with the background of the top pane clicked) to find:

  • Unique Values = Yes adds DISTINCT, which removes duplicate result rows based on the displayed fields.
  • Unique Records = Yes adds DISTINCTROW, an Access-specific option that removes duplicates based on whole records of the source tables, not just the displayed columns.

Use Unique Values when you want a list of distinct values, such as every category in use.

SQL Distinct categories

Press Run (or Ctrl + Enter) to see the result here.

Using more than one table

Add several tables to the top pane. If a relationship exists between them in the Relationships window, Access draws a join line automatically. Fields from both tables can then share the grid. Joins deserve their own lesson: Joins in Access Queries.

Reading SQL view

Switch from Design View to SQL View via Home > View or the status bar button. Access shows something like:

SQL
SELECT Products.Category, Products.ProductName, Products.UnitPrice
FROM Products
ORDER BY Products.Category, Products.UnitPrice DESC;

Edit the text and switch back to Design view; the grid updates if the SQL is something the designer can represent. Some statements, such as union queries, can only be written in SQL view.

Things that differ from standard SQL are worth noting early:

  • Fields and tables with spaces use square brackets: [Order Date].
  • Text criteria can use double or single quotes.
  • Dates are wrapped in #: #2026-03-05#.
  • The wildcard in Like is * (and ?), not %.
  • The statement ends with a semicolon, which Access adds.

Editing data through a query

The datasheet of a simple select query is updatable: change a value and Access writes it back to the table. Queries that group, use DISTINCT or join in certain ways become read-only. If you cannot edit a query datasheet, that is usually why. The Recordset Type property can be set to Dynaset (Inconsistent Updates) to relax the rules, at some risk.

Saving, renaming and reusing

Queries behave like tables in Access: forms, reports and other queries can use them as a source. A saved query is not a copy of the data; it is a stored definition, so it always shows current results. Building a query on top of another query is a good way to break a complex problem into steps.

Common beginner mistakes

  • Adding every field with * and then adding the same fields again produces duplicate columns.
  • Forgetting to clear Show on a helper field puts an unwanted column in the output.
  • Expecting Top Values to work without a sort.
  • Adding two tables with no join line, which produces a huge cross product (every row of one paired with every row of the other).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which grid row decides whether a field appears in the query result?

    Clearing the Show check box hides a field from the output, while it can still be used for sorting or criteria.
  2. 2. How does Access apply sorts on several fields?

    The leftmost field with a sort is the primary sort key. Reorder columns to change priority.
  3. 3. What does TOP 3 do if the third and fourth rows tie on the sort field?

    Access's TOP includes ties. Add a unique field to the sort to get exactly three rows.
  4. 4. Which property removes duplicate rows from the displayed columns?

    Unique Values = Yes adds DISTINCT to the SQL.

Summary

  • A select query shows chosen fields and rows without changing the tables; build it in Query Design view or SQL view.
  • The grid rows Field, Table, Sort, Show, Criteria and or define each column.
  • Sorting is left to right; Top Values needs a sort and returns ties.
  • Unique Values adds DISTINCT; aliases rename columns.
  • Access SQL uses square brackets for odd names, # around dates and * for the Like wildcard.

Next: Criteria and Expressions