Sometimes when you are working with Access you encounter read-only queries. Some of the queries have to be read-only, such as totals, and you are not meant to change the query results. Other times, you want to be able to make changes, but find out after running the query that it is read-only. Most of the time you won’t know that a particular query is read-only until you try to make changes to a value, like a name or an address, but you get the Microsoft beep meaning “you can’t do that.” This doesn’t mean that you have to start over.


Depending on the type of query and some of the different query options, you may be able to make a few changes so that the results can be edited. 

There are actually five types of queries that you cannot change:

• Crosstab
• Union
• Pass Through
• Data Definition
• Totals Queries

These queries were designed for static information, so you will not be able to change the results. If this is the problem, then you will need to design a new query that does not have these same limitations.

Checking the Query Type

The first thing to do is to go into Design View and go to the Design tab. All of the details and information about your query are located in this area, including the name of the type of query it is. Look along the top ribbon for Query Type. It is in the second section of the ribbon. If the highlighted type is any of the ones mentioned in the previous section, you will not be able to change the query. These queries are meant to be read only.

Most Select types can be changed. If you look at the ribbon and it shows that the query is a Select query, your next step is to check the function of the query.

Check the Aggregate Functions

Aggregate queries, better known as Totals, cannot be changed because Access has made calculations based on existing information in the database. If you try to change the amount, Access won’t have any idea what field it is you want to update. Since the database won’t know what field you want to change, it won’t allow you to change the result. If you want to change these queries, you have to determine what field needs to be changed, then update the database instead of the query result.

To see if your query is a Total query, look at the ribbon again. At the far right side on the Show/Hide portion, the Totals icon is the mathematical E (for epsilon). If this is highlighted, you are looking at a Totals query and will have to change the fields to get a different result.

Review the Query Properties

If you have a Select query that is not a Totals type and you still can’t change the query, there could be something in Properties that isn’t allowing you to make the change. Make sure you are in Design View and tab. Look at the Show/Hide section (on the far right side), and click on Property Sheet. This will pop up the Property Sheet on the right side of your screen.

Click on this sheet and look at the values. Check for the following fields:

• Unique Values should be No. If this is set to Yes, that will lock down the query so you cannot make changes because it is a unique value.
• Recordset Type should not be Snapshot. If it is, change it to Dynaset. If this property is Snapshot, you won’t be able to make changes because Access is showing you a snapshot of a point in time.

If you fix these, you should be able to make changes to your Select query.

Review and Update Subqueries

Subqueries are another potential area that causes your query to be read only. Subqueries display at the bottom of the page when you are on the Design view and tab. Starting on the right side of the screen, look for places where the boxes are checked. These are your subqueries. To make changes, you will need to make changes in the SQL code.

1. Go up to the query tab and right click on the query tab. From here, select SQL View.
2. Check out the query. The first part of the top line is your parent query, and if it is a select query, it will read SELECT for the first word in the code.
3. Look along the line to see where the subquery is. If you have a Select subquery, it will be the second instance of SELECT on the first line.

If two SELECT statements appear on a single line, it will lock the query so that you cannot make changes. You now have three choices for how you want to resolve the issue. There are two ways to do this without updating the code.

Option 1

Your first option is to hide the subquery.

1. Go back to the regular view by right clicking on the query tab and selecting Design View.
2. Uncheck the subquery from the bottom area.

Option 2

Your second option is to change it to a domain aggregate function.

1. Go back to the regular view by right clicking on the query tab and selecting Design View.
2. Change the type of subquery by updating the title so that it is DMax, DMin, or something comparable.

Whenever you encounter a query that you cannot change, it does not mean you need to make a new query. Take a few minutes to go through and see what is causing a query to be locked down to determine the best way to open it up for editing.