MS Access - Action Queries


Advertisements


In MS Access and other DBMS systems, queries can do a lot more than just displaying data, but they can actually perform various actions on the data in your database.

  • Action queries are queries that can add, change, or delete multiple records at one time.

  • The added benefit is that you can preview the query results in Access before you run it.

  • Microsoft Access provides 4 different types of Action Queries −

    • Append

    • Update

    • Delete

    • Make-table

  • An action query cannot be undone. You should consider making a backup of any tables that you will update by using an update query.

Create an Append Query

You can use an Append Query to retrieve data from one or more tables and add that data to another table. Let us create a new table in which we will add data from the tblEmployees table. This will be temporary table for demo purpose.

Let us call it TempEmployees and this contains the fields as shown in the following screenshot.

Tempemployees

Tempemployees Table

In the Tables tab, on the Show Table dialog box, double-click on the tblEmployees table and then close the dialog box. Double-click on the field you want to be displayed.

Table Dialog Box

Let us run your query to display the data first.

Data First

Now let us go back to Query design and select the Append button.

Append

In the Query Type, select the Append option button. This will display the following dialog box.

Append Table Name

Select the table name from the drop-down list and click Ok.

Query1

In the Query grid, you can see that in the Append To row all the field are selected by default except Address1. This because that Address1 field is not available in the TempEmployee table. So, we need to select the field from the drop-down list.

AppendTo

Let us look into the Address field.

Address Field

Let us now run your query and you will see the following confirmation message.

Confirmation Message

Click Yes to confirm your action.

Data Added

When you open the TempEmployee table, you will see all the data is added from the tblEmployees to the TempEmployee table.



Advertisements
E-Books Store