Try These Helpful Tips in Access Select Queries

by Avantix Learning Team | Updated August 7, 2016

Applies to: Microsoft® Access® 2010, 2013 and 2016 (also applies to 2007)

A select query in Microsoft Access is the most common type of query. There are a number of ways of working efficiently in select queries to get the results you want and save some time. You can also use many of these tips in other types of queries.

Recommended article: 10 Timesaving Shortcuts in Microsoft Access

1. Create a select query in Design View

To create a select query and go to Design View (and avoid the wizard):

  1. Click the Create tab in the Ribbon.
  2. In the Queries group, click Query Design. A dialog box appears.
  3. Double-click the desired tables or queries (click on the Queries tab in the dialog box to select queries).
  4. Click Close. A field list for each table or query will appear in the top pane in Design View.
  5. If you need to add another table or query to the top pane, click the Show Table button on the Query Tools Design tab in the Ribbon, double-click the desired table or query (click on the Queries tab first) and click Close.

2. Move between Design View and Datasheet View

You can run a select query by clicking on the Run button on the Query Tools Design tab in the Ribbon or by simply going to Datasheet View.

To quickly move between Design View and Datasheet View:

  • Right-click the query tab (or title bar) of the current query and select the desired view, or
  • Click the View button on the Home tab or the Query Tools Design tab in the Ribbon and select the desired view.

3. Add fields to the grid in Design View

In Design View, the lower pane is called the QBE (Query by Design) grid. Display the fields you wish to query in this grid. The upper pane in Design View displays the data sources – field lists of tables or queries.

To add a field to the QBE grid:

  • Double-click a field in a field list in the top pane in Design View to add it to the next available column in the grid.
  • Drag a field from a field list by holding down the left mouse button and then dragging down to a column to add it to the grid.
  • Click the field row in a column in the QBE grid and from the drop-down menu, select the desired field. If there’s more than one table and the same field name occurs in both, you must also specify the source table in the Table row or Access will default to the first table (left to right in the upper pane) that contains the field.

4. Add multiple fields to the grid in Design View

You can add multiple fields to the QBE grid.

To select multiple fields that are contiguous (adjacent):

  1. In the top pane, click a field in a field list and then hold down Shift and click the last field you want to include below.
  2. Point to one of the selected fields, hold down the left mouse button and drag down into a column in the QBE grid.

To select multiple fields that are non-contiguous (not adjacent):

  1. In the top pane, click a field in a field list and then Control-click on subsequent fields below.
  2. Point to one of the selected fields, hold down the left mouse button and drag down into a column in the QBE grid.

5. Add all fields to the grid in Design View

You can add all fields to the grid. However, keep in mind that this can slow down your query depending on the number of fields and the amount of data.

To select all fields in a source table or query field list:

  1. In the top pane, double-click the title bar of a field list to select all of the fields.
  2. Point to one of the selected fields, hold down the left mouse button and drag down into a column in the QBE grid.

All fields selected in a field list in a Microsoft Access query.

You could use the asterisk (*) which appears at the top of the field list in the top pane and drag it to the QBE grid to represent all of the fields. However, this method has limitations. For example, you can’t include an aggregate function or run a Totals query using the asterisk character.

6. Use AND and OR logic

In the QBE grid, in the criteria row(s):

  • Enter criteria in the same criteria row as an AND scenario.
  • Enter criteria in different criteria rows as an OR scenario.

7. Rearrange columns

You can rearrange columns in Design View in a query. This is helpful in many ways including grouping and sorting. If you specify a sort order for more than one field, Access sorts from left to right.

To rearrange columns in Design View:

  1. Click the thin bar at the top of the column you want to move to select the entire column.
  2. Point to the same area on the selected column, hold down the left mouse button and drag the column to its new position.

8. Set properties

You can display properties in Design View in a number of ways:

  • Double-click the top pane in a blank area.
  • Press F4.
  • Right-click in either pane in Design View and select Properties from the drop-down menu.
  • Click the Properties button on the Query Design Tools tab in the Ribbon.

To set query properties:

  1. Click the top pane in a blank area.
  2. Select the desired properties in the Properties pane.

Below are query properties:

Query properties in Microsoft Access.

To set field properties:

  1. Click a column in the QBE grid.
  2. Select the desired properties in the Properties pane.

Below are field properties:

Field properties in a Microsoft Access query in Design View.

9. Add a button to easily clear the grid

You can add a Clear Grid button to the Quick Access Toolbar to make it easy to clear the entire grid in Query Design View.

First, display the Quick Access Toolbar below the Ribbon (this makes it easy to see and allows more buttons to be added):

  1. Click the arrow to the right of the Quick Access Toolbar (which is above the Ribbon by default) and select Show Below Ribbon.

To add a Clear Grid button to the Quick Access Toolbar:

  1. Click the arrow to the right of the Quick Access Toolbar and choose More Commands.
  2. In the menu at the top, choose All Commands.
  3. In the list of commands, select Clear Grid.
  4. Click Add.
  5. Click OK.

Adding buttons to the Quick Access toolbar in the Options dialog box in Microsoft Access.

From this point on, you’ll be able to select the Clear Grid button (X) on the Quick Access Toolbar in Design View in a query.

Below is the Quick Access Toolbar with the Clear Grid button (X):

Customized Quick Access Toolbar in Microsoft Access with Clear Grid button.

10. Display totals in Datasheet View

You can now display totals automatically in Datasheet View:

  1. Go to Datasheet View.
  2. Click the Totals button on the Home tab in the Ribbon. A Total row is added to the datasheet.
  3. Click the column you wish to total in the Total row and select a function from the drop-down list. Access calculates the total automatically.

Display totals in datasheet view in a query in Microsoft Access.

There are many more tips and tricks for working with select queries but these are a great set to start.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

Recommended Microsoft Access training

If you’re just getting started with Access, check out our Microsoft Access: Introduction course or take your Access skills to the next level in our Microsoft Access: Intermediate / Advanced course.

VIEW MORE COURSES >

Our instructor-led classroom courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada. If you’d like to arrange custom training, contact us to arrange a date.

To request this page in an alternate format, contact us.

Copyright 2018 Avantix® Learning Inc.

You may also like

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Pin It on Pinterest

Share This