Creating an Action Query in Microsoft Access
Microsoft Access, Microsoft Office
The action query in Access is a different thing from the normal query we create, called a “select” query. The latter simply selects data and shows the result. But an action query edits data, or even deletes it. And the key thing one needs to know about this is one cannot use Undo to reverse the effect. So though we can easily create action queries, we need to treat them carefully. Luckily, there is a way to test them first.
We can use a food database for our example. Let’s say we need to increase prices on soft cheese (Category 2) due to more touchy shipping conditions.
We create a regular (select) query to find the items in that category, and run it. This is how we test, prior to making it an action query. We see if it pulls up the right data first.
The next step is to return to Design view and go to the ribbon, specifically to the Query Tools Design tab and the Query Type group. Initially, a query defaults to being a Select query. We click the button for what we want now, in this case an (Update) action query.
Then we go into the query grid, click the proper cell in the Update To row, and write the formula to take the action. In this case, [UnitPrice] (the field name) *1.05 (up by five percent), in the UnitPrice column. It’s even possible to make this a variable, or parameter query, but the basic technique is the same.
But the most important item by far is the warning we get next. We’re told the action query can’t be reversed with Undo. And it does NOT say the query will then do what we told it. Nor do we get a “mission accomplished” message. The query simply runs. And changes data.
This means we need to understand that an action query assumes we know it will do this. Therefore if someone doesn’t know, and runs it again, the query will apply the same change again. And again. As long as we are aware, it’s not a problem. But it is a caution we must keep in mind.
You can find out more about this program in our MS Access courses.