6.7: Action Queries

Action Query is a category that MS Access uses to distinguish queries that can modify the data in the database. We discuss the query types: Make-Table, Append, Delete, and Update. To create such a query one typically starts with a Simple Query that is subsequently changed (by clicking the pertinent button) to an Action Query type. You will notice as you experiment running action queries that MS Access gives a warning message asking you to confirm the changes the query will make to the database. The reason for this is that you cannot click some Undo button to undo such changes as you can in other Office applications. To undo a database action query you need to design and execute a compensating action query.

When you are in Design View for some query you will see the buttons for changing the query type:

Figure \(\PageIndex{1}\): Types of queries.

Make Table Query

Make-table queries are useful if you want to use existing data when you create a new table.

Consider the University database and suppose we need to create a table of ACS courses. We would start with a query that retrieves all ACS courses:

Figure \(\PageIndex{2}\): Begin with a select query.

Next we change the query to a Make-Table Query by clicking the Make Table button. When you do this MS Access will prompt you for the name for your new table:

Figure \(\PageIndex{3}\): Prompt for table name for Make-Table query.

The query does not run yet; you must either click the Run button or save the query and run it later. Each time you run the query MS Access will empty the table and insert rows into it.

Append Query

Suppose you wish to add rows to an existing table. To do that you must use an Append query. To create an Append query begin by creating a Simple query that lists the information you wish to see inserted to the table. Once you know the query retrieves the proper information click the Append button and MS Access will prompt you for the table name that should receive the new rows. After this you can run the query from the Run button, or you can save the query and run it later.

Delete Query

To remove entire rows from a table you use a Delete query. As in the previous query types discussed you can begin with a Simple query that retrieves the rows you wish to delete. Once the Simple query is working you can change its type to Delete and run the query (or save it and run it later). Be careful with these as a delete query can delete many rows in a single run.

Update Query

The type of query used to modify existing rows in a table is the Update query. In order to create such a query you should begin with a Simple query that retrieves the rows that are to be updated and then change the type to Update. When you change the type to Update MS Access will add a new row to the Grid area where you specify the new values for each field to be updated. The new value can be the result from a calculation.

Example

Suppose we wish to update the course Table so the credit hours are doubled for each ACS course. We begin with a Simple query to retrieve the PK field, the fields to be updated, and the fields needed for selection criteria purposes. In this case we will need a Simple query to retrieve the department code, course number, and credit hours fields:

Figure \(\PageIndex{4}\): Simple select query.

Next we change the query type to Update and MS Access modifies the Grid to include an UpdateTo line. On that line we enter an expression that generates the new values. To double the credit hours we need the expression [creditHours]*2, as in:

Figure \(\PageIndex{5}\): Update query with Update To line.

Exercises

Exercise \(\PageIndex{1}\)

Create a table of ACS courses, but name the new table ScienceCourses.

Exercise \(\PageIndex{2}\)

Does the table ScienceCourses have a primary key? If not, create one.

Exercise \(\PageIndex{3}\)

Run a delete query on ScienceCourses to delete all non 3-credit hour courses.

Exercise \(\PageIndex{4}\)

Append all 3-credit hour MATH courses to ScienceCourses.

Exercise \(\PageIndex{5}\)

Run an update query on ScienceCourses to double the credit hours of all 3-credit hour courses.