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:
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:
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:
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.
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.
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.
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.
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:
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:
Create a table of ACS courses, but name the new table ScienceCourses.
Does the table ScienceCourses have a primary key? If not, create one.
Run a delete query on ScienceCourses to delete all non 3-credit hour courses.
Append all 3-credit hour MATH courses to ScienceCourses.
Run an update query on ScienceCourses to double the credit hours of all 3-credit hour courses.