Advanced Module – Databases (Access)

Lesson 5 – Action Queries: Update, Append, Delete and Make-Table

This lesson explains how to use action queries to change data in bulk: updating values, adding records from one table to another, deleting multiple records, and creating new tables from query results. Action queries are powerful and must be used carefully.

1. What are action queries?

Action queries modify data or create new tables. They permanently change records, so you should always test them and back up the database first.

• Update query – modifies existing records.
• Append query – adds records to another table.
• Delete query – removes records.
• Make-table query – creates a new table from query results.

2. Creating an update query

Use an update query when you need to change many records at once.

Example: Increase all prices by 10% in a Products table.

1. Create a normal Select query on the Products table.
2. Add the fields you need (e.g. ProductName, UnitPrice).
3. On the Design tab, click Update.
4. A new row labelled Update To appears.
5. For UnitPrice, type: [UnitPrice] * 1.1.
6. Add criteria if needed (e.g. Category=”Electronics”).
7. Run the query and confirm when Access warns you.

Tip: Always run a Select preview first to check which records will change.

3. Creating an append query

Append queries copy records from one table or query into another table.

Example: Append new customers from a temporary import table.

1. Create a Select query using the source table (e.g. TempCustomers).
2. Add fields that correspond to the main Customers table.
3. Click Append on the Design tab.
4. Select the target table (e.g. Customers).
5. Check the field mappings (source → destination).
6. Run the query and confirm when prompted.

Important: Append queries fail if data types do not match, or if required fields are missing.

4. Creating a delete query

Delete queries remove multiple records at once. Use extreme caution, especially if referential integrity is enforced.

Example: Remove all records marked as “Test”.

1. Create a Select query to identify the target records.
2. Add criteria such as Category = “Test”.
3. Switch to Delete mode.
4. Run the query to remove the records.

Tip: If a Delete query fails, check for referential integrity rules that prevent deleting parent records.

5. Creating a make-table query

Make-table queries create a new table from the results of a query.

Example: Create a YearlySummary table from Sales data.

1. Build a Select query with the fields you want.
2. Click Make Table.
3. Enter a name such as YearlySummary.
4. Choose Current Database or another database file.
5. Run and confirm when Access warns you.

Note: Make-table queries overwrite existing tables with the same name.

6. Best practices for action queries

• Back up your database before running any action query.
• Preview as a Select query to check which records will be affected.
• Use clear and precise criteria to avoid unwanted changes.
• Keep field mappings tidy and check data types in Append queries.
• Use clear naming conventions (e.g. Update_Stock_2025).
• Avoid running action queries on linked or shared databases without coordination.

7. Practical Activity

• Create an Update query that increases prices by 5% for a single product category.
• Create an Append query that moves cleaned data from a temporary table into a master table.
• Create a Delete query that removes sample or test records.
• Create a Make-table query that builds a summary table of last year’s orders.