Creating Action Queries in MOS Access

Key Concepts

1. Update Queries

Update queries are used to modify existing records in a table. They allow you to change specific fields across multiple records based on specified criteria.

2. Delete Queries

Delete queries are used to remove records from a table. They allow you to delete multiple records that meet certain conditions, helping to clean up and maintain the database.

3. Append Queries

Append queries are used to add new records to an existing table. They allow you to insert data from one table into another, facilitating data migration and integration.

4. Make-Table Queries

Make-Table queries are used to create a new table based on the results of a query. They allow you to extract specific data from one or more tables and store it in a new table for further analysis.

Detailed Explanation

Creating an Update Query

To create an update query, follow these steps:

1. Open your database in MOS Access.
2. Go to the "Create" tab and click on "Query Design."
3. Add the table you want to update by clicking "Add Table" and selecting the table.
4. Choose the fields you want to update by dragging them from the table to the query grid.
5. Switch to the "Design" tab and click on "Update Query."
6. Set the criteria for the update and specify the new values.
7. Run the query to update the records.

Creating a Delete Query

To create a delete query, follow these steps:

1. Open your database in MOS Access.
2. Go to the "Create" tab and click on "Query Design."
3. Add the table you want to delete records from by clicking "Add Table" and selecting the table.
4. Choose the fields you want to use for the deletion criteria by dragging them from the table to the query grid.
5. Switch to the "Design" tab and click on "Delete Query."
6. Set the criteria for the deletion.
7. Run the query to delete the records.

Creating an Append Query

To create an append query, follow these steps:

1. Open your database in MOS Access.
2. Go to the "Create" tab and click on "Query Design."
3. Add the source table by clicking "Add Table" and selecting the table.
4. Choose the fields you want to append by dragging them from the source table to the query grid.
5. Switch to the "Design" tab and click on "Append Query."
6. Select the destination table and map the fields.
7. Run the query to append the records.

Creating a Make-Table Query

To create a make-table query, follow these steps:

1. Open your database in MOS Access.
2. Go to the "Create" tab and click on "Query Design."
3. Add the source tables by clicking "Add Table" and selecting the tables.
4. Choose the fields you want to include in the new table by dragging them from the source tables to the query grid.
5. Switch to the "Design" tab and click on "Make-Table Query."
6. Specify the name and location of the new table.
7. Run the query to create the new table.

Examples and Analogies

Think of an update query as a librarian updating the catalog to reflect new editions of books. The librarian changes the edition information for all books by a specific author.

A delete query is like a librarian removing outdated books from the shelves. The librarian deletes all books published before a certain year to keep the collection current.

An append query is like a librarian adding new books to the catalog. The librarian inserts records from a new shipment into the existing catalog, expanding the collection.

A make-table query is like a librarian creating a special reading list. The librarian extracts books by a specific genre and stores them in a new list for easy reference.

By mastering these action queries, you can efficiently manage and manipulate data in MOS Access, ensuring your database remains accurate and up-to-date.