Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you want to make changes to your data, you don't have to update all the records individually one at a time. You can use an Update action query to make the changes all at once. Right now, I am in my Customers table. If I scroll over to my right-hand side, I can see my SalesReps. When I click on any SalesRep and use the dropdown, I can see their name. Now let's say that my SalesRep Collins is leaving the company. We're going to give all of her customers to Pearl Davenport, SalesRep number 10. So we need to update all of the 8s to a 10.
I'll go up to my Create Ribbon, and then to Query Design. I'll add my Customers table to the grid and close the window. I'll scroll down to find my SalesRep field, and double-click on it to add it to the grid. Now I'll go up to the Ribbon, and change my Query Type to an Update query, and now I have a new row in my grid, Update To. So my Criteria is that I'm looking for SalesRep number 8, and I'm going to update it to SalesRep number 10.
I'll click on the Run button. It says I'm about to update three rows, and I'll click Yes. Now when I look back at my Customers table, I can see that I don't have any SalesReps number 8 anymore. They're all SalesRep number 10. Now let's do another example of an Update query. I am going to right-click on my Customers tab, and choose Close All, to shut both of these objects. When it asks if I want to save my query, I'll say No. Now let's open up our SalesReps table. It's time for our annual cost of living increase.
Now when I scroll over to the right-hand side, I can see all of my SalesRep salaries. I'll go to the Create ribbon, and click on the Query Design button. This time, I'll add my SalesReps to the grid and close the window. Change the query to an Update query. I'll scroll down. The field that we need is Salary. Now this time I don't need a Criteria, because I am going to apply it to everybody. What I am going to do is a calculation. So click in Update To, then come up to the Ribbon, and choose Builder.
I'll click on the Plus sign next to my database and on the Plus sign next to my tables, and then I'll click on my SalesReps. I'll scroll down to find my Salary and double-click on it. So that's the base salary right now, and then I'll type a Plus sign, and .25 to add 25 cents to their salary. Then click OK. I'll come up to the left side of the Ribbon, and Run the query. It says I'm about to update 25 rows. I'll click Yes.
Now when I look at my SalesReps table, all of my employees now have a 25 cent higher Salary. As we can see, using an Update action query allows you to make massive changes to your data in a flash.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98536 Viewers
80 Video lessons · 141532 Viewers
59 Video lessons · 59892 Viewers
52 Video lessons · 73080 Viewers
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.