Viewers: in countries Watching now:
In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.
The Update query is one of four action queries in Access. Using it we can create a selection of our records that all require a specific update or modification to the data and then update all of those records throughout our entire database. The Update query will not only breeze through the task, it'll prevent any data entry errors that typically crop up when manually modifying large amounts of records. Before we begin, if you haven't already done so, please take a moment and review the previous movie on creating a backup of your database. We can begin our Update query by going to the Create tab and we'll create a new query in Design view.
Let's suppose that all the employees in our Human Resources department are getting relocated to the new corporate office in Dallas, Texas. We want to go through our database and find all of the employees in the Human Resources department. Then we want to change their city and state to Dallas and Texas. So first we'll create a Select query to identify which records will be modified. Let's go ahead and select our Employees table and say Close. From the Employees table we'll choose the employee ID and we'll scroll down and find City and State.
We'll also need a department so we can apply a criteria of Human Resources and I'll type that in the Criteria field here. Let's run our query to see which records which records will be affected by the change. So those are the 13 Human Resources employees that are all relocating to Dallas, Texas. Let's create the Update query and update our data table. I'll go back to Design view and I'll switch our query from a Select query to the Update query.
When I do that we'll get a new row here that says Update To. So what we want to do is update the city to Dallas and we want to update the state to Texas or TX. Now if we go and this query, Access is going to tell us that we're about to update 13 rows. Once you click Yes, you can't undo the command or reverse the changes, so make sure this is what you want to do. I'm going to click No here for a second and show you that we do have a difference with action queries between viewing the datasheet and running the action query.
Running the action query will actually apply those changes to your data tables, where as viewing an action query will show you which records are about to be effected. So if I click View here and change to Datasheet view, this shows me which city and states are about to be changed, I'll go back to Design view and this time I'll say Run, and we'll go ahead and make those changes. Now if I go back into my data tables for my employees, we can verify that that change has been made.
I'll open up my employees table, which is in the Chapter 1 groups, and I'll scroll over to identify our departments. Every employee that works for the Human Resources department now says Dallas, Texas under the City and State. Data may need to be updated for any number of reasons. You may change vendors or have an updated product ID number. Your business may move, change names, or get merged with another company, and all of your addresses and phone numbers and e-mail addresses might need to be altered.
Whatever the reason, you'll be able to quickly and easily keep your database up-to-date with an Update query.
There are currently no FAQs about Access 2010: Queries in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.