Microsoft Access Queries – Action Queries

Action Queries 

Select queries show data from the database but do not change the original data whereas Action queries will always change the data in some way.

Because of this, you must be very careful with action queries, as they cannot be undone.   

The four types of Action queries are:

Make Table : Makes a new table, maybe a subset is needed for others in the company and needs to be transferred into another system.  Or if an archive system is needed.

Append : If data needs to be copied from one table to another.  Maybe merging different systems or adding to an archive system.

Delete : Delete data that meets particular criteria, e.g. deleting all the orders that were completed before a particular date.  (Maybe after they have been archived using other Action queries.)

Update : The values changed according to a calculation.  The prices for a particular category being increased by a percentage.


The action queries can be distinguished in the navigation pane by the exclamation mark displayed as part of the icon shown next to the name of the query.  Please be very sure that you wish to carry out the action – there is a warning but it can be turned off!

Creating Action Queries 

Before setting up an action query, make a normal select query to isolate the correct data and then change it over to an action query by changing the query type to the appropriate type.

Make Table Query 

You need to decide why you need to make a new table.  Would a normal select query do? As the data changes should the answer change or should it be static?  Some reasons for creating a new table:

• Starting an archive system.

• Separating a database into different databases.

•      Needing a static picture of the data at particular points of time.  (For accounting purposes maybe.)

Is the new table going to be in the current database or a separate database?  If it needs to be in a different database, then the database needs to be set up before the query is made. 

To create a make table query 

1. Create a normal select query with the criteria necessary.

2. In design view click on the design ribbon.

3. Select the make table query.

4. The table name must not duplicate a table or query (otherwise it will over ride the original).  As default the new table will be made in the current database, otherwise select the Another Database option and then browse for the database to save in.

Append Query

The append query transfers data from one table to another. This could be because: 

• you need to merge the data from different tables

•       archive data into an existing archive

To create an append query 

1. Create a normal select query with the criteria necessary.

2. In design view click on the design ribbon.

3. Select the append query

4. The table name should be chosen from the drop down to make sure that the correct table is chosen and the name is not mistyped.

5. This changes the Query by Example grid, where a new section has been added where the connection between the table that the query is based on and the table the data is to be appended to are made

(If the field names match exactly between the two tables this is filled in automatically, otherwise the user has to fill in the fields.)

Delete Query 

The Delete Query allows data to be deleted in a batch process rather than selecting the data to be deleted individually. 

When creating a delete query the only fields that need to be added are those that are required to define the criteria 

It is very important that the criteria is set, otherwise the all the data in the table will be deleted! 

Once the correct data is selected change the query type to the delete query. 

Update Query 

The update query is used to update data in a batch process.  This can be for the whole table or a selection of data.   

If there are several sub sets of the table that needs to be changed the criteria can be defined by using a parameter so that the different record sets can be selected.  If necessary, the update formula can also contain a parameter. 

The difference between datasheet view and run 

When using select queries it does not matter whether you use to view the data or run the query the results are the same.  With action queries, they do totally different things. 

On the Home ribbon you only have the view button, but on the design ribbon you also have the option of run.   

The design ribbon is also where you also choose what type of query you wish to set up. 

View : Shows the records that meet the criteria.  Does not change the data or database in any way unless the result is edited. 

Run : Carries out the action specified by the query.  If the query is a select query will give the same result as View 

In the navigation pane the double click is the equivalent of the Run option. 

Running an Action Query 

Action queries change data – please be very sure that you want to change the data before you run the query.  It cannot be undone! 

If the query is run from the navigation pane then you will receive a general warning about the type of action query. 

This warning is not displayed if the query is run from the design view. 

If you decide to carry on by answering yes, then the actual action is worked out and the impact of the action is then displayed as a message. 

It is at this point that the action is carried out and the data is changed. 

The exception to this on a make table query.  If the query has already been run once, then the table already exists and so there will be an extra warning asking if you wish to delete the original table 

If the answer to this warning is no then the query stops at this point. 

If you answer yes then the table is deleted.  It does not matter how you answer to the next warning about pasting the rows into the new table, the original table is deleted. 

If the make table query is set up to create an archive system then it should never be run again, as the data to make that table will have been deleted by using the delete query.