Creating and Using Action Queries

Displaying data is one thing, but making changes to the data is another. Data often comes from many tables, and in a relational database system updating one table may create problems in another related table or tables. This tutorial will look at an isolated table, but in a following tutorial making changes to tables that are related will be described in detail.

Action query in MS Access is a query that makes changes to records, or moves many records. Two out of four basic action queries in MS Access will be described with examples. This tutorial is for the new users of MS Access and they will benefit greatly by reviewing the other MS Access tutorials on the ASP Free site.

The Employees table

In order to look at action queries, a new mdb file was created called CRUD. The Employees table from the Northwind database was imported into this file. This is easily done by following the File-->Get External Data-->Import.. command string. The design view of this table is shown in the next picture. This will be the starting point for this tutorial. From this table we will create a new table using the Make Table action query.

The Make Table query

This action query takes a table or a a number of tables and, using the data from them, creates a new table. In this way you can create a table that can be exported to a new Microsoft database, or set up an archive of important data. The make table query results in the creation of a view of the table that contains a subset of the columns in the table. In the example that follows a make table query will be created. The query will create and populate a table that has only the address information of the employees.

Creating an Employee Addresses Table Using a Select Query

We can start with a Select query. After making the selections and applying conditions for filtering, if any, we can convert it into a make table query. The following design view of a Select query shows the six fields that are to be included in the table, named Address. We will use an expression to create a field which has the full name in one column using two of the fields from this query.

Here a table called Address will be created from the data in the Employees table as shown. We will be choosing the columns needed for the Address table. We will also add a column which has the First Name together with the Last Name, for which we need to build an expression. In an empty Field row in the above window, right click to bring up the context-sensitive menu. Choose Build... to bring up the Expression Builder as shown, expand the Tables node, highlight the First name, and click on Value in the highlighted area. This adds the code [Employees] ! [FirstName] to the expression as shown.

Now complete by adding a space between First Name and Last Name, and then add the Last name in a way that is similar to the way you added the First Name. Use the available buttons on the Expression builder as shown.

When you click on the OK button on the Expression Builder the concatenated string is added to your design view of the query as shown. The SQL view of the query is now as shown in the next paragraph. Save the query as EmployeeAddress.

SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, 
Employees.Address, Employees.City, Employees.PostalCode, 
Employees.Country, [Employees]![FirstName] & " " & 
[Employees]![LastName] AS Expr1
FROM Employees;

When you run this query by double-clicking the EmployeeAddress query icon you will see the following output. As you can see the last column shows the concatenated expression.

Go back to the design of the query and refine the query so that you do not choose the First Name and Last Name as shown in the SQL View (they are not included in the select list). In some versions of MS Access, when you open the query in SQL View, you may not be able to change it to the Query Design view.

SELECT Employees!FirstName & " " & Employees!LastName AS Emp_name,  
Employees.Address, Employees.City, Employees.PostalCode,  
Employees.Country 
FROM Employees;

The column in blue is the 'Full Name' created by the Expression Builder. You replaced Expr1 with "Emp_name." The output of this query is now as shown in the next picture. You have rearranged everything just as you needed.

Create an Address Table from the Select Query

Open the EmployeeAddress query in design view as shown. Right click on an empty area by the side of the Employees table and click on the Make-Table Query... submenu item as shown.

This brings up the Make Table window as shown. You have the option of creating a new table in the current database, or in another database with a name you provide. Here a table called Address will be created in the current database. Type in the name Address and click on the OK button.

This brings up the Save As dialog. Save the query under a suitable name. Here it is called the AddressQuery.

Click on the OK button to the Save query. This adds the AddressQuery to the database.

The SQLView of the MakeTable query is as shown here. This is called the Select Into statement. This will dump the selected columns into a table called Address.

SELECT Employees!FirstName & " " & Employees!LastName AS Emp_Name, 
Employees.Address, Employees.City, Employees.PostalCode, 
Employees.Country INTO Address
FROM   Employees;

When you click this query in the main window you will get a message from MS Access as shown.

When you click on the Yes button you get another message warning you about what is going to happen, and how the action is not reversible (cannot be undone).

Go ahead and click on the Yes button one more time. This will create the 'Address' table as shown in the main window of the database.

Create an Append Query

Appending records to a table can be useful in some cases when you want to just add some new records to a table. Assume that a group of Employees joined from another company which was acquired and you want to append the employee addresses of the acquired company to the existing addresses. In this situation, you may want to just append the information. This is a better way of adding the data than typing all the new information into your Address table.

The address information from your acquired business is in the AcqBusiness table as shown. You want to append the information to your Address table.

The Append query

Click on "create a query" in the design view and add the table to the design pane as shown in the next picture. Select all the required fields as shown. Right click and choose Query Type from the drop-down menu. From the next drop-down menu choose Append Query... as shown.

This brings up the next window, which is Append, as shown. Now you are appending the result of the above query to the Address table in the current database if you accept the default choice. If you want you could choose to append the results to a table in another database.

When you click on the OK button to the above screen you will be asked to save the query by giving it a name. This query was saved as AppendAcqQuery in this tutorial. This picture also shows the other queries created in this tutorial. Notice the different icons for the various queries.

The SQL View of the AppendAcqQuery is shown in the next paragraph.

INSERT INTO Address 
( Emp_Name, Address, City, PostalCode, Country )
SELECT AcqBusiness.Emp_Name, AcqBusiness.Address, 
AcqBusiness.City, AcqBusiness.PostalCode, 
AcqBusiness.Country
FROM AcqBusiness;

Appending the Records

Now all that you need to do is double click the AppendAcqQuery. This brings up the following dialogue.

Now if you click on the Yes button, you will be presented with another message screen which tells you how many rows will be appended as shown.

When you click on the Yes button in this message, you will add the two rows from the AcqBusiness table to the Address Table as shown.

Summary

This tutorial is for those who have started using MS Access. The tutorial described step-by-step procedures for using these two data modification queries. Each of these queries were described together with the SQL statements that implement these data modification. This opportunity should be used to learn and brush up on SQL skills. An example of using the Expression builder was also described. The Jet specific syntax used in these queries will be slightly different from the standard SQL.