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 and crosstab queries to retrieve and present data, and to supply forms and reports with data. When you run a select or a crosstab query, Access displays the results in Datasheet view.
Run the query
-
Locate the query in the Navigation Pane.
-
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, 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.
Run an action query
There are four types of action queries: append queries, delete queries, update queries, and make-table queries. 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:
-
View the action query in Datasheet view before you run it. To do this, open the query in Design view, click View on the Access status bar, and then click Datasheet View on the shortcut menu. To switch back to Design view, click View again, and then click Design View on the shortcut menu.
-
Change the query to a select query, and then run it.
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
-
Open the action query in Design view.
-
On the Design tab, in the Query Type group, click Select.
-
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.
Trust a database
-
On the Message Bar, click Options.
The Microsoft Office Security Options dialog box appears.
-
Select Enable this content and then click OK.
Run a parameter query
A parameter 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
-
Locate the query in the Navigation Pane.
-
Do one of the following:
-
Double-click the query you want to run.
-
Click the query you want to run, then press ENTER.
-
-
When the parameter prompt appears, enter a value to apply as a criterion.
Run a SQL-specific query
There are three main types of SQL-specific query: union queries, pass-through queries, and data-definition queries.
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.
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
-
Locate the query in the Navigation Pane.
-
Do one of the following:
-
Double-click the query you want to run.
-
Click the query you want to run, and then press ENTER.
-
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.
-
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.
-
In the Navigation Pane, right-click each table that has one or more fields joined in your query, and then click Design View.
1. Joined fields with different data types.
2. Right-click the table, then click Design View.
-
For each join, compare the values in the Data Type column of the table design grid for the fields involved in that join.
1. Check the data type of the joined fields in table Design view.
-
To switch to a table so that you can see its fields, click the tab with that table's name.
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
-
Click File > Options to open the Access Options dialog box.
-
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
-
Click Database Tools > Compact and Repair Database.
Change a field alias
-
Open the query in Design view.
-
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:.
-
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.
