Action Query in Action 2016

In MS Access and other DBMS systems, queries can do more than just display data. Specifically, the query can perform various actions on the data in your database.

1. Action queries are queries that perform actions on data, can add, change or delete records.
2. You can preview the results in Access before executing the query.
3. Action queries cannot be undone. You should consider backing up tables that you will update using update queries (update queries).

Append Query

Append Query is a query that allows adding new data (adding new records) to the end of an existing table. You can use Append Query to retrieve data from one or more tables and add that data to another table.

For example, create a new table called TempNhanvien and add data from the table tblNhanvien. This will be a temporary table for demo, including the following fields:

Go to the Create tab> Queries > Query Design group to display the Show Table dialog box:

In the Tables tab , select tblNhanvien by double clicking and Close . Double click on the name of the field you want to display.

Run query to display data.

Next, go back to the Design tab and select Append.

Select the TempNhanvien table in the Append dialog box that appears.

In the design grid at the bottom of the screen, you can see the Append To row displayed in all fields by default except Diachi1.

This is because Diachi1 does not exist in the TempNhanvien table . So we need to select the field from the drop down list.

Next, we run the query and you will see the following confirmation message.

Click Yes to confirm your action. Now you open the TempNhanvien table and you will see the data added from the tblNhanvien table .

Update Query

Update Query is the type of action query, used to change the content of data on the database more specifically to update the data of certain fields in the table.

Update Query can also be combined with criteria to specify which rows will be updated

Try a specific example on the database we demoed. Go to the Create tab and click on Query Design .

In the Tables tab , select tblNhanvien by double clicking and Close.

On the Design tab , Query Type group , select Update, and double-click the field you want to update the value.

In the Update row in the grid section design the bottom of the screen, enter the update value into Update To, enter the Criteria initial value and run the query. The following message will appear:

Select Yes and go to Datasheet View and you will see the staff with ID 5 has updated the original Ten to Huong.

Delete Query

Delete Query is a type of Action Query that also changes the data of tables. Specifically, it is used to delete data and records from the data table in the database to satisfy certain conditions.

The Delete Query can also be combined with criteria to specify which rows will be deleted

Try a specific example on the database we demoed. Go to the Create tab and click on Query Design.

Select the table tblNhanvien. On the Design tab , Query Type group , select Delete, and double-click on NhanvienID.

In the Criteria row in the grid section design the bottom of the screen filled in value 9, that is, we will delete the record related to the employee whose ID is 9.

Run the query, the following message will appear:

Select Yes and go to the Datasheet View and you will see the data record of the employee whose ID is 9 has been deleted.

Make Table Query

Make Table Query is a type of Action Query used to create a new table with data from another Table or Query.

First, go to the Create tab and click Query Design.

On the Design tab , the Query Type group , select Make Table, and you will see the following dialog box appear. Enter the name of the new table you want to create and click OK.

Run the query, the following message will appear:

Select Yes and you will see a newly created table appear in the navigation pane.

Previous lesson: Set up Query Criteria in Access 2016

Next lesson: Parameter Query in Access 2016