Creating Access Action Queries

Microsoft Access 365 supports several query types that help users retrieve, analyze, create, and modify data. Earlier lessons introduced Select queries, joins, query criteria, and other query-design techniques used to display records from one or more tables. This lesson builds on those ideas by introducing Action Queries, which are used when a query must perform a bulk operation rather than simply display a result set.

An Action Query is different from a Select Query because it performs an operation against data. A Select Query answers a question such as, "Which records match this condition?" An Action Query goes further and performs a task such as creating a new table, adding records to another table, changing existing records, or deleting records that meet a condition. For this reason, Action Queries are powerful tools, but they must be designed and tested carefully.

In Microsoft Access 365, Action Queries remain part of the desktop Access database environment. They are especially useful when a database user needs to perform the same change across many records, prepare a reporting snapshot, archive a subset of records, append validated import data, or remove records that should no longer remain in a working table. Instead of editing records one by one, the user defines a query and lets Access apply the operation to every record selected by that query.

Why Action Queries Are Used

Action Queries are used for bulk operations. A bulk operation is a data operation that affects many records at once. For example, a project database may need to increase a billing rate for all projects belonging to a particular client. A time-tracking database may need to append approved timesheet records from a temporary review table into a permanent Hours table. A training database may need to create a new table containing only the records needed for a report or export.

These operations could be performed manually, but manual editing is slow and prone to inconsistency. An Action Query allows the database designer or database user to define the affected records through query criteria and then run a controlled operation against those records. This is one of the reasons Access continues to be useful for departmental databases, small business systems, administrative applications, and instructional database design work.

The central rule is simple: first identify the records, then perform the action. In practice, this means that most Action Query work should begin as a Select Query. The Select Query lets you preview the records before any data is changed or before a new table is created. After the correct records are visible in Datasheet View, the query can be converted into the appropriate Action Query type.

The Four Main Action Query Types

Microsoft Access provides four common Action Query types: Make-Table, Append, Update, and Delete. Each type performs a different bulk operation. Understanding the purpose of each one helps you choose the correct query type for the task.

Make-Table Query

A Make-Table Query creates a new table from the results of a query. This is useful when you need a physical table that contains a selected set of records. The new table may be used as an archive table, a reporting snapshot, a temporary analysis table, or a data export table.

A Make-Table Query does not update records inside the source table. Instead, it reads records selected by the query and writes those records into a newly created table. This makes it safer than an Update or Delete Query in many situations, but it is still an Action Query and Access still displays a confirmation warning before the operation is completed.

Append Query

An Append Query adds records to an existing table. This query type is useful when records have been imported, reviewed, staged, or prepared in one location and then need to be inserted into a permanent table. For example, a business may import customer leads into a temporary table, validate those records, and then append the approved records to the main Customers table.

Append queries require careful field matching. The source fields must be mapped to the correct destination fields. If the destination table has required fields, primary keys, validation rules, or referential integrity constraints, the appended records must satisfy those requirements.

Update Query

An Update Query changes existing records that match the query criteria. This type of query is useful when a repeated value must be corrected or a calculated business rule must be applied to many records at once. For example, an Update Query might change the status of all completed projects from Active to Closed, update a tax classification, or correct a misspelled category value.

Update queries require special caution because they overwrite existing values. The safest method is to preview the records with a Select Query first, confirm the criteria, and then convert the query to an Update Query only after you are confident that the correct records have been selected.

Delete Query

A Delete Query removes records that match the query criteria. This is the most dangerous Action Query type because it permanently removes data from the target table. Delete queries are useful for removing temporary records, test data, rejected imports, or records that fall outside a defined retention period.

A Delete Query should always be tested first as a Select Query. You should also verify relationships and referential integrity rules before deleting records. In a relational database, deleting a record from one table may be blocked by related records in another table, or it may affect related data if cascade delete behavior has been configured.

Start with a Select Query

The recommended workflow for creating an Action Query in Access 365 is to start with a Select Query. This allows you to identify the records before you perform the action. In Query Design, add the table or tables needed for the operation. Then add the fields needed to identify, filter, or display the records that may be affected.

Criteria are especially important. The criteria row in the query design grid determines which records are included. If the criteria are too broad, the Action Query may affect more records than intended. If the criteria are too narrow, the query may miss records that should be included. For this reason, criteria should be tested carefully before running the action.

After the Select Query is designed, switch to Datasheet View to preview the records. Datasheet View shows the records selected by the query without performing the action. This preview step is one of the most important safety checks in the entire Action Query workflow.

Convert the Select Query into an Action Query

After the selected records have been reviewed, the Select Query can be converted into an Action Query. In Access 365, this is done from the Query Design tools on the ribbon. The Query Type group includes the choices for Make Table, Append, Update, and Delete. The selected query type determines what additional settings Access requires.

If you choose Make Table, Access asks for the name of the new table and whether the table should be created in the current database or in another database. If you choose Append, Access asks for the destination table. If you choose Update, the design grid includes an update row where you specify the new value for the selected field. If you choose Delete, the query identifies the records that should be removed.

This conversion step should not be rushed. Once the query becomes an Action Query, it is no longer just a display query. It is now a command that can create or change data. The design still depends on the same query logic, but the effect is different.

Creating a Make-Table Query

A Make-Table Query is often the easiest Action Query to understand because it creates a new table from query results. The source records are selected by the query, and the output is written into a new table. This is useful when you want to preserve a snapshot of data at a particular moment in time.

For example, suppose a Projects table and an Hours table are used to track client work. A Select Query may identify all hours billed during a particular month. If those records need to be preserved as a reporting snapshot, the Select Query can be converted into a Make-Table Query. Access will ask for the name of the new table and then create that table when the query is run.

A Make-Table Query can create the table in the current database or in another Access database. Creating the table in the current database is convenient for reports, testing, or short-term analysis. Creating the table in another database may be useful when data must be exported, archived, or transferred to a separate Access file.

A Make-Table Query should still be planned carefully. If a table with the same name already exists, Access may require confirmation before replacing it. Also, because the new table is a separate physical object, it may not automatically inherit all indexes, relationships, validation rules, lookup settings, or formatting from the original tables. The Make-Table Query creates data output, but the resulting table may still need review before it is used as part of a larger database design.

Running an Action Query

After the query has been designed, previewed, and converted to the correct Action Query type, it can be run. In Access 365, the Run command executes the Action Query. Running the query is the point where the database operation is actually performed.

Access displays a confirmation message before completing the operation. This warning is important because Action Queries are not ordinary display queries. They can create tables, append records, update existing records, or delete records. The confirmation dialog gives the user a final opportunity to cancel the operation before it is applied.

The confirmation warning should be read carefully. When Access warns that the action cannot be undone, it means that the normal Undo command should not be relied upon to reverse the result. For high-value data, the correct safety procedure is to make a backup before running the query or to run the query against a copy of the database.

Viewing Results without Running the Query

One of the most useful safety techniques is to view the records affected by the query before running the action. In the original workflow, the user created a Select Query, tested the results, and then changed the query into the desired Action Query type. That remains the best general approach in Access 365.

If you have already converted a query into an Action Query and then need to recheck the selected records, you can use Datasheet View to inspect the records that the query identifies. This lets you examine the affected records without immediately executing the action.

This step is especially valuable for Delete and Update queries. With a Delete Query, the preview allows you to see which records are candidates for removal. With an Update Query, the preview allows you to confirm that the correct group of records has been selected before new values are applied. Viewing the records first can prevent broad accidental changes.

Safety Guidelines for Action Queries

Action Queries are efficient because they work on groups of records, but that same efficiency makes them risky when the query logic is wrong. A single incorrect criterion can affect hundreds or thousands of records. Therefore, Action Queries should be treated as controlled database operations rather than casual edits.

Use the following guidelines when working with Action Queries:

1. Back up the database first. Before running an Update or Delete Query, create a backup copy of the database or the affected table.
2. Start with a Select Query. Confirm that the correct records appear before converting the query into an Action Query.
3. Use specific criteria. Avoid vague criteria that may include more records than expected.
4. Check joins carefully. When a query uses more than one table, confirm that the join logic returns the intended records.
5. Test on a copy when the data is important. For production or high-value data, run the operation on a test copy first.
6. Read the confirmation dialog. Do not click Yes automatically. Confirm the number of records and the type of operation.
7. Document recurring action queries. If the query will be reused, give it a clear name and document its purpose.

These rules are especially important in relational databases because records may be connected through primary keys, foreign keys, and relationships. Updating or deleting data in one table can affect the meaning of records in another table. The query may be syntactically valid, but the business result may still be wrong if the data model and relationship rules are not understood.

Common Examples of Action Query Use

Action Queries are often used in practical Access applications. In a client billing database, an Append Query might move approved time records from a review table into a permanent Hours table. In an inventory database, an Update Query might mark items as discontinued when a supplier no longer carries them. In a student database, a Make-Table Query might create a temporary table of students enrolled in a specific program for reporting.

Delete queries are also useful, but they should be used sparingly. For example, a database may use a Delete Query to remove rejected import records from a staging table after the accepted records have been appended into the main table. In this case, the Delete Query is not removing important business records. It is cleaning a temporary work table after the import process has been completed.

In every example, the query must be connected to a clear business rule. A good Action Query does not merely change data. It enforces a known operation: archive this subset, append these approved records, update this status, or delete these temporary rows. The clearer the business rule, the safer the Action Query will be.

Action Queries in Access 365 Desktop Databases

Older Access training materials sometimes discuss Access web databases or Access Web Apps. That older platform context should not be confused with the current desktop Access database environment. In Access 365 desktop databases, Action Queries remain valid and useful. The workflow has been modernized through the ribbon interface, but the database concept remains the same.

The important distinction is that this lesson is about Microsoft Access desktop databases, such as .accdb files, where users design tables, queries, forms, reports, and macros inside Access. In that environment, Action Queries are still a normal part of query design. They should be used with care, but they should not be treated as obsolete.

Lesson Summary

Action Queries allow Microsoft Access 365 users to perform bulk operations. A Make-Table Query creates a new table from query results. An Append Query adds records to an existing table. An Update Query changes existing records. A Delete Query removes selected records. These query types extend Access beyond record selection and allow the database user to perform controlled data operations.

The safest workflow is to begin with a Select Query, preview the selected records in Datasheet View, convert the query to the correct Action Query type, review any additional query-specific settings, and then run the query only after reading the confirmation warning. This workflow helps prevent accidental bulk changes and reinforces the most important rule of Action Query design: verify the affected records before performing the action.

In the next lesson, you will move from changing data to summarizing data by working with a Totals query. Totals queries are used to group records and calculate aggregate values, while Action Queries are used to create or modify data. Together, these query types show how Access queries can support both analysis and controlled data operations.

Viewing Results without Running Queries

To review the related non-workflow page, use the link below. This supporting page explains how to inspect query results before executing an action.

Viewing Results without Running Queries

Click the Quiz link below to answer a few questions about query join types, query types, and action queries.
Query Join Types - Quiz

Click the Exercise link below to practice creating an action query.
Creating Action Queries - Exercise