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:

Action Query in Action 2016 Picture 1

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

Action Query in Action 2016 Picture 2

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

Action Query in Action 2016 Picture 3

Run query to display data.

Action Query in Action 2016 Picture 4

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

Action Query in Action 2016 Picture 5

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

Action Query in Action 2016 Picture 6

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.

Action Query in Action 2016 Picture 7

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

Action Query in Action 2016 Picture 8

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

Action Query in Action 2016 Picture 9

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

Action Query in Action 2016 Picture 10

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 .

Action Query in Action 2016 Picture 11

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

Action Query in Action 2016 Picture 12

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

Action Query in Action 2016 Picture 13

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:

Action Query in Action 2016 Picture 14

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.

Action Query in Action 2016 Picture 15

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

Action Query in Action 2016 Picture 16

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.

Action Query in Action 2016 Picture 17

Run the query, the following message will appear:

Action Query in Action 2016 Picture 18

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.

Action Query in Action 2016 Picture 19

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.

Action Query in Action 2016 Picture 20

Action Query in Action 2016 Picture 21

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.

Action Query in Action 2016 Picture 22

Run the query, the following message will appear:

Action Query in Action 2016 Picture 23

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

Action Query in Action 2016 Picture 24

Previous lesson: Set up Query Criteria in Access 2016

Next lesson: Parameter Query in Access 2016