Creating Simple update, append, make table, delete and crosstab queries

🔧 Creating Simple Update, Append, Make Table, Delete, and Crosstab Queries

In Microsoft Access, queries are not just used for selecting and displaying data; they can also be used to modify, update, delete, and even summarize data. This guide will explain how to create various types of queries in MS Access: Update, Append, Make Table, Delete, and Crosstab queries.

🎯 What are Action Queries?

Action queries perform actions on the data in your tables. Unlike Select Queries, which only retrieve data, action queries modify the data in some way. There are four main types of action queries:

• Update Queries – Modify existing data in a table.
• Append Queries – Add records to an existing table.
• Make Table Queries – Create a new table from existing data.
• Delete Queries – Remove records from a table.
• Crosstab Queries – Summarize data in a tabular format, typically used for reporting.

🛠 Creating an Update Query

An Update Query is used to modify existing records in one or more tables. You can update one or more fields with new values based on specific criteria.

Steps to Create an Update Query:

1. Open the database and click on the Create tab, then select Query Design.
2. Select the table you want to update and click Add.
3. In the query design grid, select the fields that you want to update.
4. In the Update To row, enter the new value or expression you want to assign to the field.
5. Enter criteria in the Criteria row to filter the records that should be updated.
6. Click on Update in the Query Type section of the toolbar, then click Run to apply the changes.

Example: To increase the Salary by 10% for all employees in the HR department, you would enter:

 UpdateTo: [Salary] * 1.10

And in the Criteria row under the Department field, you would enter "HR".

🛠 Creating an Append Query

An Append Query adds new records to an existing table. It can be used to transfer data from one table to another or add new records manually.

Steps to Create an Append Query:

1. Open the database and click on the Create tab, then select Query Design.
2. Select the table containing the data you want to append, then click Add.
3. In the query design grid, select the fields that you want to append to the destination table.
4. Click on Append in the Query Type section of the toolbar, then select the table where you want to add the data.
5. Click Run to add the records to the destination table.

Example: If you have a NewEmployees table and want to append the data to the Employees table, select the fields from NewEmployees and append them to the corresponding fields in Employees.

🛠 Creating a Make Table Query

A Make Table Query is used to create a new table from the results of a query. It is commonly used for creating temporary or summary tables based on specific criteria.

Steps to Create a Make Table Query:

1. Open the database and click on the Create tab, then select Query Design.
2. Select the table(s) you want to use to create the new table.
3. Click on Make Table in the Query Type section of the toolbar.
4. In the dialog box that appears, enter a name for the new table.
5. Click Run to create the new table with the selected data.

Example: If you want to create a new table for employees in the HR department, you can select the fields and use a Make Table Query to create a new table with the data from the HR department.

🛠 Creating a Delete Query

A Delete Query is used to remove records from a table based on specific criteria. It is important to be cautious when using this type of query, as deleted records cannot be recovered unless you have a backup.

Steps to Create a Delete Query:

1. Open the database and click on the Create tab, then select Query Design.
2. Select the table from which you want to delete records, then click Add.
3. In the query design grid, select the fields you want to filter by.
4. Enter the criteria for deleting records in the Criteria row.
5. Click on Delete in the Query Type section of the toolbar, then click Run to remove the records.

Example: If you want to delete all records where the Salary is less than 20000, you would enter <20000 in the Salary field under the Criteria row.

🛠 Creating a Crosstab Query

A Crosstab Query is used to summarize data by turning unique values of one field into column headings and applying aggregate functions (such as Sum, Count, etc.) to other fields.

Steps to Create a Crosstab Query:

1. Open the database and click on the Create tab, then select Query Design.
2. Select the table containing the data you want to summarize.
3. Click on Crosstab Query in the Query Type section of the toolbar.
4. In the Row Heading and Column Heading rows, select the fields you want to use as row and column headings.
5. In the Value row, select the field you want to summarize and choose an aggregate function (e.g., Sum, Count, etc.).
6. Click Run to view the summarized data in a crosstab format.

Example: If you want to summarize sales by City and Month, select City as the row heading, Month as the column heading, and SalesAmount as the value with the Sum function.

🎯 Learning Outcomes

• Learn how to create Update Queries to modify existing records.
• Master Append Queries to add records to existing tables.
• Understand how to use Make Table Queries to create new tables from query results.
• Learn how to use Delete Queries to remove unwanted records.
• Understand the functionality of Crosstab Queries for summarizing data in a pivot-table style.

By mastering these action queries, you can efficiently manage and manipulate data in your MS Access database, enabling powerful data analysis and reporting capabilities.