What are “Action Queries”?

With an “Action query”, we can edit multiple records simultaneously in a single move. There are four “Action Queries”: “Create table query”, “Update query”, “Append query” and “Delete Query”.

To run “Action queries” (“Append query”, “Update query” and Create table query), you must activate the database if it is not signed or not in a trusted location.
(See) Lesson 4

“Create Table Query”

So far, what we have seen were all simple queries, or queries of “Select” type, which can be seen in the “Ribbon” when the “Query” is open in “Design View”:

A simple “Query” displays the results from a table based on certain criteria.

The “Query” type “Create Table” does the same, only that here you create the table of results.
We use a “Create Table Query” to backup tables, create temporary tables or export tables.

In this example I used the “Total Article” “Query” which is already open.
Click the “Create Table” button in the “Ribbon”, under the “Design” tab.
This opens the “Create Table” dialog in which you enter a name for your new table.

If you wish, you can save the new table in another database, or choose “Current Database” and click the OK button.

Now you only need to click the “Run” button in the “Ribbon” to create the table.
A window appears indicating how many rows are going to be inserted into the new table.

Click the “Yes” button.
The table will be added in the “Navigation Pane” to the left of the application window.
Double-click this table to see the result.

“Update Query”

An “Update Query” allows us to change multiple values from a table at once.
In this example, I’m increasing all prices by 1 Euro in the “Products” table.
In the “Products” table, I have only 6 articles, but if I have hundreds of articles and I should change them all, it will take quite a while. So, I use an “Update Query”.

First, I close the “Products” table and create a “Query” to update the “Products” table.
Whether you like to do it in “Query Design” or the “Query Wizard”, it does not matter.

Click the “Update” button in the “Ribbon” (1).
This changes the type of “Query” into a “Update Query”.
As you can see, there is a change the grid and you now have an “Update to:” (2) row.
Place the “Price” field in the first column of the grid (3) by double-clicking on it.

Right-click the “Update to” and click the “Build” from the drop-down menu.
This opens the “Expression Builder” dialog box.
In this dialog box, double-click “Price” in the middle of the dialog box, click in the toolbar of the dialog and type the number, in this case 1, by which you wan to increase the price of each article.
Click the OK button.




This is what you will see in the “Update to” box.

You can also just type this in the field, you do not have to use the “Expression Builder” dialog box.

Save the query by clicking the “Save” button in the “Ribbon”.
Click the “Run” button in the “Ribbon”.
“Access” displays a window, which warns you that it will update a number of records.
Click the “Yes” button.
To see the result, you open the “Products” table:

All prices listed in the table are updated.

Now:

You can also enter multiple criteria, eg when you wish to update only a particular group in the table.

For example.
Suppose I just want to change for products of “Printer” type, I would have the “Type” field added to the grid and add criteria to this “printer” column.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats