Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you need to make changes to the data in your Table, you can do it quickly using an Update Query. Let's say it's time for me to raise my prices. I don't need to go into my Products line-by-line to make the change. I can do the whole table at once. Create a new query and click on Query Design. Add the Products Table and click Close. Double-click on Price to add it. And now we're going to change the query type, up here, to Update. So let's say we're going to do a 10% across-the-board price increase.
In this new Update To line, put in a square bracket and the word "Price." The square brackets, again, tell it that it's a field. Put in an asterisk for multiply and then 1.1. This 1.1% represents the same price, plus a 10% increase. When we click on the View button, we will see the prices that will be updated. Click on the Design again. When we click Run, it will now actually change the prices in the table.
When we run it, it will ask, "You are about to update 90 rows. Once you click Yes, you can't use the Undo command reverse the changes. Are you sure you want to update these records? Yes, we are sure." Now when we go into our Products and look at our Prices, they are 10% higher than they were a few minutes ago. Now let's clear this grid and make another query. Click on the gray row at the top of Price, and then hit Delete to clear it. And click on the Products Table and clear that as well, by pressing the Delete button.
For our second query, let's say that SalesRep number 5, April Walters, is leaving, and we are going to give all of her customers to SalesRep number 2, Lila Douglas. Click on the Query tools Design and click on Show Table, and add Customers to the Wuery window and click Close. Scroll down and double-click on SalesRep to add it to the Customers table. Make sure that Update is selected. And in the Criteria line, type 5. That specifies that we are looking for SalesRep number five.
In the Update To line, enter 2. View the results. We are going to have one customer that's going to change. Click back on Design, and run the query. Click Yes. Let's open up the Customers Table to see the change. Double-click on Customers and Bread Express, who used to be April Walters, will now say Douglas. Close the Customers table. Now most of the time, these queries don't get saved, because they are made for one time use.
Update queries make it convenient to change your Table data as needed, all at once, instead of one at a time.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 80767 Viewers
80 Video lessons · 132949 Viewers
52 Video lessons · 66405 Viewers
59 Video lessons · 52206 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.
Your file was successfully uploaded.