Run a query

A query is a set of instructions that you can use for working with data. You run a query to perform these instructions. In addition to returning results — which can be sorted, grouped, or filtered — a query can also create, copy, delete, or change data.

This article explains how to run queries and provides only brief overviews of the various types of queries. The article also discusses error messages you might encounter when you run different types of queries, and provides steps you can take to work around or correct those errors.

This article does not provide step-by-step instructions for creating queries.

 Important   You cannot run action queries if a database is operating in Disabled mode —a reduced functionality mode that Access uses to help protect your data in certain circumstances. You may see a dialog box warning, or you may see a warning in the Message Bar.

For more information about Disabled mode, and how to enable action queries, see the section, Run an action query.

What do you want to do?


Run a select or a crosstab query

You use select queries (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) and crosstab queries (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.) to retrieve and present data, and to supply forms and reports with data. When you run a select or a crosstab query, Microsoft Office Access 2007 displays the results in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).

Run the query

  1. Locate the query in the Navigation Pane.
  2. Do one of the following:
    • Double-click the query you want to run.
    • Click the query you want to run, and then press ENTER.

If the query you want to run is currently open in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), you can also run it by clicking Run in the Results group on the Design tab on the Ribbon, part of the Microsoft Office Fluent user interface.

Top of Page Top of Page

Run an action query

There are four types of action queries: append queries (append query: An action query that adds the records in a query's result set to the end of an existing table.), delete queries (delete query: A query (SQL statement) that removes rows matching the criteria that you specify from one or more tables.), update queries (update query: An action query (SQL statement) that changes a set of records according to criteria (search conditions) that you specify.), and make-table queries (make-table query: A query (SQL statement) that creates a new table and then creates records (rows) in that table by copying records from an existing table or query result.). Except for make-table queries (which create new tables), action queries make changes to the data in tables they are based on. These changes cannot be easily undone, for example, by pressing CTRL+Z. If you make changes using an action query that you later decide you didn't want to make, usually you will have to restore the data from a backup copy. For this reason, you should always make sure you have a fresh backup of the underlying data before running an action query.

You can mitigate the risk of running an action query by first previewing the data that will be acted upon. There are two ways to do this:

 Note   Make sure to note what type of action query (append, update, make-table, or delete) you are starting with, so you can change the query back to that type after you preview the data with this method.

Run an action query as a select query

  1. Open the action query in Design view.
  2. On the Design tab, in the Query Type group, click Select.
  1. On the Design tab, in the Results group, click Run.

Run the query

When you are ready to run an action query, double-click it in the Navigation Pane, or click it and then press ENTER.

 Important   By default, Access disables all action queries in a database unless you indicate that you trust the database. You can indicate that you trust a database by using the Message Bar, just below the Ribbon.

The Message Bar

Trust a database

  1. On the Message Bar, click Options.

The Microsoft Office Security Options dialog box appears.

  1. Select Enable this content and then click OK.

If you do not see the Message Bar

  • On the Database Tools tab, in the Show/Hide group, click Message Bar.

Top of Page Top of Page

Run a parameter query

A parameter query (parameter query: A query in which a user interactively specifies one or more criteria values. A parameter query is not a separate kind of query; rather, it extends the flexibility of a query.) prompts you for a value when you run it. When you supply the value, the parameter query applies it as a field criterion. Which field it applies the criterion to is specified in the query design. If you do not supply a value when prompted, the parameter query interprets your input as an empty string.

A parameter query is always also another type of query. Most parameter queries are select queries or crosstab queries, but append, make-table, and update queries can also be parameter queries.

You run a parameter query according to its other query type, but, in general, use the following procedure.

Run the query

  1. Locate the query in the Navigation Pane.
  2. Do one of the following:
    • Double-click the query you want to run.
    • Click the query you want to run, then press ENTER.
  3. When the parameter prompt appears, enter a value to apply as a criterion.

Top of Page Top of Page

Run a SQL-specific query

There are three main types of SQL-specific query (SQL-specific query: A query that consists of an SQL statement. Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.): union queries (union query: A query that uses the UNION operator to combine the results of two or more select queries.), pass-through queries (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of the data being processed by the Access database engine.), and data-definition queries (data-definition query: An SQL-specific query that contains data definition language (DDL) statements. These statements allow you to create or alter objects in the database.).

Union queries combine data from two or more tables, but not in the same manner as other queries. Whereas most queries combine data by concatenating rows, union queries combine data by appending rows. Union queries differ from append queries in that union queries do not change the underlying tables. Union queries append the rows in a recordset that does not persist after the query is closed.

Pass-through queries are not processed by the database engine that comes with Access; rather, they are passed directly to a remote database server that does the processing and then passes the results back to Access.

Data-definition queries are a special type of query that does not process data; instead, data-definition queries create, delete or modify other database objects (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.).

SQL-specific queries cannot be opened in Design view. They can only be opened in SQL view, or run. Except for data-definition queries, running a SQL-specific query opens it in Datasheet view.

Run the query

  1. Locate the query in the Navigation Pane.
  2. Do one of the following:
    • Double-click the query you want to run.
    • Click the query you want to run, and then press ENTER.

Top of Page Top of Page

Troubleshoot an error message

The following table shows some common error messages you may encounter. These errors can appear either as a message in a cell (instead of an expected value), or as an error message. The sections that follow the list include procedures you can use to resolve these errors.

 Note   This content of this table is not exhaustive. If it does not include the error message you received, you can submit feedback by using the form at the end of this article and including specific information about the error message in the comment box provided.

Error message Problem Solution
Type mismatch in expression The query may be joining fields that have different data types. Check the query design and ensure that the joined fields have the same data type. For instructions, see the section Check the joined fields in your query.
Record is Deleted This can occur if either the object or the database is damaged. Compact and repair the database. For instructions, see the section Compact and repair your database.
Circular reference caused by alias

The alias assigned to a field is the same as a component of the expression for that field.

An alias is a name that is given to any expression in the Field row of the query design grid that is not an actual field. Access assigns the alias for you if you do not do so yourself; for example, EXPR1. An alias is immediately followed by a colon (:) and then by the expression. When you run the query, the alias becomes the column name in the datasheet.

Change the alias. For instructions, see the section Change a field alias.
#Error This error can occur when the value of a calculated field is greater than the value allowed by the field's FieldSize property setting. This also occurs when the denominator of a calculated field is or evaluates to zero (0). Ensure that the calculated field's denominator does not evaluate to zero (0). If appropriate, change the FieldSize property.
#Deleted The record being referred to has been deleted. If the record was deleted accidentally, it must be restored from a backup. If the deletion was intentional, you can dismiss this error message by pressing SHIFT+F9 to refresh the query.

Check the joined fields in your query

To check the data types of fields in a query, you look at the source tables in Design view and inspect the properties for the fields you are checking.

  1. Open the query in Design view. Joins appear as lines that connect fields in the source tables. Note the table and field names for each join.
  2. In the Navigation Pane, right-click each table that has one or more fields joined in your query, and then click Design View.

Joined fields with dissimilar data types

Callout 1 Joined fields with different data types.
Callout 2 Right-click the table, then click Design View.
  1. For each join, compare the values in the Data Type column of the table design grid for the fields involved in that join.

Check the data type of the joined field in table Design view

Callout 1 Check the data type of the joined fields in table Design view.
  1. To switch to a table so that you can see its fields, click the tab with that table's name.

Top of Page Top of Page

Compact and repair your database

Running the Compact and Repair Database utility within Access can improve the performance of your database. This utility makes a copy of the database file and, if it is fragmented, rearranges how the database file is stored on disk. After the compact and repair process has completed, the compacted database will have reclaimed wasted space, and is usually smaller than the original. By compacting the database frequently, you can help ensure optimal performance of the database application, and also resolve errors that arise from hardware problems, power failures or surges, and similar causes.

After the compact operation has completed, query speed is enhanced because the underlying data has been rewritten to the tables in contiguous pages. Scanning contiguous pages is much faster than scanning fragmented pages. Queries are also optimized after each database compaction.

During the compact operation, you can use the original name for the compacted database file, or you can use a different name to create a separate file. If you use the same name and the database is compacted successfully, Access automatically replaces the original file with the compacted version.

Set an option that automates this process

  1. Click the Microsoft Office Button Button image, and then click Access Options.
  1. Click Current Database and, under Application Options, select the Compact on Close check box.

This causes Access to automatically compact and repair the database every time it is closed.

Manually compact and repair your database

  1. Click the Microsoft Office Button Button image, point to Manage, and then, under Manage this database, click Compact and Repair Database.

 Note   For the compact operation to succeed, you must have enough storage space on your hard disk for both the original and the compacted database.

Top of Page Top of Page

Change a field alias

  1. Open the query in Design view.
  2. In the query design grid, look for fields that have aliases. These will have a colon at the end of the field name, as in Name:.
  3. Check each alias to ensure that the alias does not match the name of any field that is part of the alias' expression. If it does, change the alias.

Top of Page Top of Page

 
 
Applies to:
Access 2007