💻Microsoft Access Update Queries: Modifying Data
0
0
0
Takeaways:
This video provides an introduction to action queries in Microsoft Access, focusing specifically on update queries. It explains that while small changes to a database are common, larger modifications often require action queries, which directly alter data within tables instead of just retrieving it. The video demonstrates how to create and run an update query to modify a large number of records at once, specifically showing how to increase prices for certain products. It also highlights the importance of backing up data before running action queries and cautions that running these queries multiple times will reapply the specified action.
Action Queries in Microsoft Access: A Study Guide
What is an action query in Microsoft Access?
An action query is a type of query in Microsoft Access that modifies data in the underlying table or tables. Unlike select queries which only retrieve data, action queries perform operations such as updating, deleting, appending, or creating new data.
What are the different types of action queries?
There are four main types of action queries in Microsoft Access:
Update Query: Modifies existing records in a table.
Delete Query: Removes records from a table.
Append Query: Adds records from one table to another.
Make Table Query: Creates a new table based on the results of a query.
When would you use an update query?
You would use an update query when you need to make changes to a large set of records in your database. For example, increasing the price of a range of products or updating customer addresses based on a specific criteria.
How do you create an update query from a select query?
To create an update query from an existing select query in Microsoft Access, open the select query in Design View. On the Design tab, locate and click on the "Update" option. This will change the query grid to include an "Update To" row, allowing you to specify how the data should be modified.
How do you specify the changes to be made in an update query?
In an update query's Design View, you specify the changes in the "Update To" row. This row appears below the field you want to update. You enter the expression or value that the field should be updated to. For example, to increase a price by 10%, you would enter [FieldName] * 1.1 in the "Update To" row under the price field.
Why is it important to back up your data before running an action query?
It is crucial to back up your data before running any action query because these queries permanently modify your data. Once an action query is run, the changes cannot be easily undone. Having a backup allows you to restore your data to its previous state if unintended changes occur.
How do you create a backup of a table in Microsoft Access?
To create a backup copy of a table in Microsoft Access, select the table in the Navigation Pane. Copy the table (Ctrl+C or right-click and choose Copy) and then paste it (Ctrl+V or right-click and Paste). When pasting, you will be presented with paste options. Choose "Structure and Data" to create a complete copy of the table including all records. You can also rename the copied table to easily identify it as a backup.
What happens if you run an update query multiple times?
If you run an update query multiple times, it will apply the specified action repeatedly to the current data. For instance, if you have an update query that increases a price by 10%, running it a second time will increase the new price by another 10%, not the original price. Action queries execute their operations each time they are run, modifying the existing data.
Quiz
What is the primary purpose of an action query in Microsoft Access?
Name the four types of action queries mentioned in the source.
Before running an update query that modifies a large set of records, what important step is recommended?
How do you create a backup copy of a table in Access?
How can you identify if a query is currently a select query or an action query in Design View?
In an update query, which row in the design grid is used to specify the calculation or value to update a field?
What happens if you run an update query multiple times without making changes or restoring the data?
After running an update query, how can you view the changes made to the data without running the query again?
How did the video recommend saving an update query to easily identify its purpose later?
What is the consequence of accidentally running an action query more than once on the same data?
Answers
Action queries modify data in the underlying table or tables instead of just selecting data. They perform operations on the data.
The four types are update query, delete query, append query, and make table query.
It is recommended to make a backup copy of the table(s) that will be affected.
You can copy and paste the table from the navigation pane, ensuring you select the "structure and data" paste option.
In Design View, the highlighted option on the Design tab will indicate the query type (e.g., "Select" or "Update").
The "Update To" row is used to specify the update value or calculation.
Running an update query multiple times will apply the specified action repeatedly to the already modified data.
You can click on the "View" button in the Design tab (not "Run") to see the results of the query's last execution.
The video recommended adding a prefix like "UPD" to the query name.
Running an action query more than once will continue to perform the action, potentially leading to unintended modifications of your data.
Glossary of Key Terms
Action Query: A query that modifies data in the underlying table or tables by performing an operation on the data, rather than just selecting it.
Update Query: A type of action query used to update records in a table or tables.
Delete Query: A type of action query used to remove records from a table.
Append Query: A type of action query used to add records from one table to another.
Make Table Query: A type of action query used to create a new table from the data selected by the query.
Navigation Pane: The area in Access that displays database objects like tables, queries, forms, reports, macros, and modules.
Design View: A view in Access that allows you to modify the structure and design of database objects like queries and tables.
Select Query: A standard query in Access that retrieves data from one or more tables based on specified criteria.
Update To Row: A row in the query design grid for an update query where you specify the new value or calculation for the field being updated.
Criteria: Conditions applied in a query to filter which records are included in the results or which records are affected by an action query.
Run (Query): To execute a query to see its results (for a select query) or to perform the specified action (for an action query).
View (Query): To see the data results of the most recent execution of a query (useful for viewing the results of an action query without running it again).
