Action Queries in MS Access

A Select Query displays records without directly changing the data in the table. An Action Query performs an operation that changes data or creates a table.

Types of Action Queries

These four query types are the main action queries commonly taught in Microsoft Access.

An Update Query changes the values of existing records. For example, you can increase course fees or update the course name for selected students.

Example: Students Table

SQL Example: Increase ADCA Fees

UPDATE Students
SET Fees = Fees + 1000
WHERE Course = "ADCA";

This query increases the Fees value by 1000 for every student whose Course is ADCA. Other students remain unchanged.

Creating an Update Query in Design View

1. Open the database.
2. Click Create and select Query Design.
3. Add the table you want to update.
4. Add the required fields to the query grid.
5. On the Query Design tab, select Update.
6. Enter the new value or expression in the Update To row.
7. Enter criteria to select the intended records.
8. Review the query and click Run (!).

An Append Query adds records to an existing table. It is useful when transferring records from a temporary table or an older table into a main table.

Example Tables

Suppose you have a table named NewStudents containing records that need to be added to Students.

SQL Example

INSERT INTO Students
    (StudentID, StudentName, Course, Fees)
SELECT
    StudentID, StudentName, Course, Fees
FROM NewStudents;

This query appends the selected records from NewStudents to Students.

Creating an Append Query in Design View

1. Create a new query in Design View.
2. Add the source table containing the new records.
3. Add the fields you want to append.
4. Choose Append on the Query Design tab.
5. Select the destination table.
6. Check the destination fields in the Append To row.
7. Add criteria if you want to append only some records.
8. Run the query after reviewing the selection.

A Delete Query removes selected records from a table. It deletes entire records, not just the contents of one field.

Example: Delete Records for a Particular Course

DELETE FROM Students
WHERE Course = "Tally Prime";

This query deletes every record from Students where the Course is Tally Prime.

Safer Practice: Preview the Records First

Before deleting records, run a Select Query using the same condition:

SELECT *
FROM Students
WHERE Course = "Tally Prime";

Check the returned records. If they are exactly the records you intend to remove, create and run the corresponding Delete Query.

Creating a Delete Query in Design View

1. Create a query and add the target table.
2. Add the field you want to use for filtering.
3. Choose Delete on the Query Design tab.
4. Set the criteria carefully.
5. Review the query and run it only when you are sure.

A Make-Table Query creates a new table using the results of a query. It is useful for preparing an archive, extracting a subset of records, or creating a separate working table.

Example: Create a Table of ADCA Students

SELECT *
INTO ADCAStudents
FROM Students
WHERE Course = "ADCA";

This query creates a new table named ADCAStudents containing the records selected from Students.

Creating a Make-Table Query in Design View

1. Create a query in Design View.
2. Add the source table.
3. Select the fields you want to include.
4. Set criteria if you want only selected records.
5. Choose Make Table on the Query Design tab.
6. Enter a name for the new table.
7. Run the query after checking the fields and criteria.

Microsoft Access lets you convert a query into an action query using the Query Design tab.

The available options may vary slightly depending on your version of Access and the query design.

Criteria control which records an action query affects. Always use a specific condition when you intend to update, append, or delete only a subset of records.

Example: Update Fees for One Student

UPDATE Students
SET Fees = 12000
WHERE StudentID = 101;

This query sets the Fees value to 12000 for the student whose StudentID is 101.

Example: Delete One Student Record

DELETE FROM Students
WHERE StudentID = 104;

This query deletes the record for StudentID 104. Check the ID before running the query.

Example: Append Only ADCA Students

INSERT INTO ADCAArchive
    (StudentID, StudentName, Course, Fees)
SELECT StudentID, StudentName, Course, Fees
FROM Students
WHERE Course = "ADCA";

This query appends only ADCA student records to the existing ADCAArchive table. The destination table must have compatible fields.

• Update many records with one query.
• Append records from one table to another.
• Delete records that meet specified conditions.
• Create a new table from selected data.
• Reduce repetitive manual database operations.
• Help automate common data-management tasks.

Action Queries are powerful, but their effects should be checked carefully before execution.

1. Make a backup before running important action queries.
2. Use a Select Query to preview the target records.
3. Check the WHERE condition carefully.
4. Confirm the destination table for Append Queries.
5. Check whether primary-key values may be duplicated.
6. Verify that a Make-Table destination name is correct.
7. Review the result after running the query.

Create a Students table with StudentID, StudentName, Course, and Fees fields. Insert at least ten sample records.

Tasks

1. Increase the fees of ADCA students by 500 using an Update Query.
2. Set the fees of one selected student to 12000.
3. Create a temporary table named NewStudents and append its records to Students.
4. Preview students enrolled in Tally Prime using a Select Query.
5. After checking the records, create a Delete Query for a selected course in your test database.
6. Create a new table containing only ADCA students.
7. Use a WHERE condition to update only one student record.
8. Check the results of each query after running it.

Goal: Understand Update, Append, Delete, and Make-Table Queries and use them safely.

1. What is an Action Query?
2. Name the four main types of Action Queries.
3. What is the purpose of an Update Query?
4. How does an Append Query work?
5. What happens when a Delete Query runs?
6. What is a Make-Table Query?
7. Why is the WHERE clause important in an action query?
8. Why should you back up your database before running an action query?