What are Action Queries?

With an “Action query”, we can edit multiple records at once. There are four “Action Queries”: “Create table query”, “Update query”, “Append query” and “Delete Query”.

For “Action queries” (“Append query”, “Update query” and Create table query) to be executed, you must enable the database when it does not have digital signature or not in a trusted location.
(See) Lesson 5

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 you open the “Query” 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 a table of results. We use a “Create Table Query” to backup tables, temporary tables or export tables.

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

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

Now you need to only click the “Run” button in the “Ribbon” to create the table.

A window appears indicating how many rows it is inserting 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 a few articles, but if I have many and want to change them all, it will take quite a while. So to save time, I use an “Update Query”.

First, I connect the “Products” table and create a “Query” where I got to add the “Products” table. If you prefer 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 in 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. Then type [Price] +1 in the “Update to” (4) box.

Save the “Query” by clicking the “Save” button in the “Ribbon” and give it a name in the dialog box that appears. For example, “Price plus one”.

Click the OK button.

Then click the “Run” button in the “Ribbon”. “Access” displays a window, which warns you that you have a number of records that will be updated.

Click the “Yes” button.

To see the result, open the “Products” table. The prices are nicely updated and the update is in the “Navigation Panel”.

Now:

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

For example.

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

Featured Courses

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