|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Update Queries By Richard Rost 
6 years ago
How to Create Update Queries in Microsoft Access In this video, I will show you how to create Update Queries. You will learn how to fix the values in one table, and I will show you how to update values based on records in a different table. Bruce in Jacksonville FL (a Gold Member) asks: My customer table is all a mess. I didn't properly design it from the beginning, and now I've got all kinds of crazy data in it that I need to fix. For example, in the State field people have typed: New York, NY, N.Y., and so on. How can I fix this quickly and easily? MembersI'll show you how to update product pricing in your database based on information given to you by your vendor. You will see how to review products with different prices, see discontinued items, and add new items with an append query. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! Links
Course LinkUpdate Queries are covered in more detail in Access Expert 13.
IntroIn this video, we will learn how to use update queries in Microsoft Access to quickly fix inconsistent data in your tables, such as standardizing entries in a state field. I will show you how to create a basic update query to correct values, preview the changes before running them, and offer important tips about making backups and handling warning messages. You will also see how to use an auxiliary table to handle multiple corrections at once for more complex scenarios.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we are going to learn about update queries - how to change values in your tables using update queries. Today's question comes from Bruce in Jacksonville, Florida, one of my Gold members. Bruce says, "My customer table is all a mess. I did not properly design it from the beginning, and now I have all kinds of crazy data in it that I need to fix. For example, in the state field, people have typed New York all out like that, then NY, then N period, Y period, and so on. How can I fix this quickly and easily?" Well, Bruce, in the future, you should use something like a combo box where people can pick the state from a list. But, since your table is already messed up, let me show you how to fix it. Here I have a sample customer table, and you can see here is the state field. A few of them are typed in right, like Florida, Iowa. But when they came to type in New York, they typed it in like that, they have got it like that, they have got it like that. So there are several different ways people have typed in New York. Now, you could go through and use Find and Replace and fix them all. But if you have thousands and thousands of records, this can be very time-consuming. So, an update query makes the job much, much quicker and easier, and you can save an update query so that if this happens again in the future, you can just run the query and it will automatically run through the fix again. So, how do you create an update query? Well, same way you create a normal query: query design. Then, in here we are going to change it to an update query on the query type, and you will see down here you have got "Update To." That is a new row there. Let's add in the customer table and close that. Now, let's bring in the field we want to change, so that is the state field right here. Now for criteria, this is where you put in what you want to look for. For example, I want to look for New York. Like that, I will spell it out, put it inside quotes. Now, you can use the "Or" row if you want to, if there are multiple different misspellings for New York, like the N period Y period, or they might even put N period space Y period. Put any other variations that you can think of down below here. Now, what do you want to update it to? Well, in here put the correct spelling, which is NY like that. If you just want to preview the changes before actually running the query, click on this little spreadsheet button here that says "View" - that switches over to the spreadsheet view, or as they call it in Access, the datasheet view. And there you will see all the ones that will be changed. Let's go back to design view. Now, I like to throw this warning screen up before anybody does anything that involves changing your data automatically, and update queries are one of them. Make sure you have got a good backup of your database before you run this query, just in case you goofed. It's always easier to restore from a backup and redo the query than it is to try to fix if you accidentally put the update query in there wrong. So, make sure you have got a good backup of your data. Now, how do you run the query? Well, there is the Run button right there. Hit the Run button. Now, nothing appears to happen for me. You may get a warning message that says Microsoft Access is about to change four rows. Are you sure you want to do this? Just say yes. I have warnings turned off in my database, and I show you how to do that in a different lesson. But I can show you real quick here. Just go under File, then Options, and then come down to Client Settings. Right down here you see where it says Confirm. There is Record Changes, Document Deletions, and Action Queries. That is the one that I have turned off. Normally that is on, so any time you run an action query it gives you that warning message. Here, I will turn it on and you can see what it says. Now, if I try to run this, see you are about to update - well, I just ran it a second ago. It normally would say you are about to update four rows. Are you sure you want to do it? Say yes. I am going to turn that back off, though. Options. Client Settings. I like to leave it off because I am very careful before I run update queries. Let's go take a peek at our table, open up the CustomerT, and you can see, look, it fixed all of the states for us. That is what an update query does. It updates the data in the table. Want to save this for later? Sure. Save. We will call this "My Update StateQ." Now, what if you have got two states that are messed up? Well, you cannot put the same field in here twice. So if you have got a whole bunch of different states that are messed up like New York and, let's say, Florida, then you have to use a different table. You can do this between two tables. Let me show you. Let's close this guy. Save changes? Yes, sure. Now, let's create a new table that has the bad state and the good state in it. So, Create, Table Design. You could put an ID in here if you want to; you do not have to. I usually say that every table should have an ID in it, but it is not always required. I will put in here BadState and GoodState, or ReplaceWith, or whatever you want to call it. Save this. We will call this "BadStateT." Actually, let's call it "StateBadT." I like to keep it so that the beginnings of all my objects fit together that way. If I search for it, they are all alphabetically listed right next to each other. You can see that in here. So, under BadState, you are going to put your bad spellings. So, N period, Y period, which will be fixed to NY. New York will be fixed to NY. See how this works? You can make BadState indexed if you want to so you do not have any duplicates in here. That would be easy to do. Design View. Under BadState, go Indexed, no duplicates. Yes, no duplicates. There it is. Save that. Maybe they have got N period space Y period. Go through your table and figure out all the bad ones. They might have spelled out Florida and you want FL. They might have put NJ for New Jersey. Oops, I got them backwards. NJ - or New Jersey, just like that. I want you to build this table; you will never have to do it again. You may or may not decide you want to lock down that state field. If you want to make it only two characters or a combo box, sometimes companies like to leave that as free text because you might do business internationally, and their state might be something that is not a two-digit abbreviation. It is up to you. You can leave it open and then fix it afterwards, or lock it down ahead of time. Now that you have got this, you can create an update query based on two tables. Let me go back into my customer table real quick and break it. Let me put stuff back. I got New York. I got Florida. I was doing some playing around out of the video. So, NJ, let's do that. Maybe this one is an NY. So, I have got a couple of ones that are broken in here, that are bad. Now, let's make a new query. Create, Query Design. I am going to bring in my CustomerT and my StateBadT. Now, Access does not see any relationships between these because the fields are not named the same, and I did that on purpose. So, let's come over here and let's find State. Now, this and the bad table will match up with BadState. We are going to change it to the GoodState. So, click and drag and then drop it on BadState. That will form that relationship. Now, it is still a select query. Right now, if I run this, I did not put any destination fields in here. Let me hit State. And if I run this now, you should see just the bad state show up. So, what I want to do now is I am going to change this to an update query, and I am going to update it to GoodState. So, right in here, put in GoodState. But be careful, because that happens. See that? It put it inside quotes. You want to make sure you put it inside square brackets, like that. It is going to look for a field called GoodState. If you want to be really careful, put the table name in it too. Come in the beginning here and put StateBadT.GoodState, just like that. Actually, put an extra bit in there. That is what you really want, like that. It should work with just GoodState because this table does not have GoodState in it. But to be safe, that is what you want to do. Now, we should not need a criterion here because the only records that show up are going to be the ones that are already related to a BadState over here, and this relationship should take care of it. So let's go ahead and hit Run. That should do it. You should get the error message if you do not have that warning turned off. Let's take a look in the customer table. And there we go. They are all fixed. See, we got New York, we got the New Jersey was right, and the Florida down here on the bottom. We did not need a criteria because of this relationship. And that is how you do an update query, both just on the table itself, and if you want to make it based on a value in a different table. It is a little more advanced, but I hope you learned something. If you want to learn more about update queries, check out the members only extended cut. I will show you how to take a product sheet that you get from your vendor, whether it is sent in Excel or whatever, import that into Access. Once you have got it there, I will show you how to update your product pricing. We'll go through with a query, we will see the differences between the old prices and the new prices, and we will have an update query where with one click we can update all of your prices based on the new prices given from the vendor. I will show you how to see what products have been discontinued. In other words, they are missing from the vendor's sheet. I will show you how to append or bring in any products that are new, that the vendor added to their sheet that are not in your database. We will append those using an append query, slightly different from an update query, but it works very similarly. So, we will see what is in the members only extended cut video. How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you will see a list of all the different perks that are available: Silver, Gold, Platinum, and Diamond. But do not worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making them, and they will always be free. If you enjoyed this video, please give me a like and share. Click on the Subscribe button to subscribe to my channel and be notified of any new releases. Check for additional resources down below the video. Click the Show More button, and you will see a list of other links to other videos, downloads, resources, lessons, and lots more. If you have not yet tried my free Access Level 1 course, it is three hours long. You can find it on my website or on my YouTube channel. And if you like Level 1, Level 2 is just one dollar. And that is free for my members. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can post your question there. Also, be sure to stop by my Access Forum on my website and also look for me on Facebook, Twitter, and of course YouTube. Once again, my name is Richard Rost with AccessLearningZone.com. Thanks for learning with me and I will see you next time. QuizQ1. What is the main purpose of an update query in Microsoft Access?A. To change values in your tables automatically based on specified criteria B. To create new tables from existing data C. To permanently delete records from a table D. To view table data without making changes Q2. What was the initial problem Bruce faced with his customer table? A. Duplicate records B. Incorrect relationship links between tables C. Inconsistent data entry for state names D. Missing primary key Q3. What is a recommended practice to prevent inconsistent state data in the future? A. Use a memo field for the state B. Allow users to type any value C. Use a combo box for state selection D. Only use uppercase letters Q4. What is a major advantage of using an update query over Find and Replace for large datasets? A. Update queries are always reversible B. Update queries are faster and can be saved for repeated use C. Update queries show a warning for every change D. Find and Replace cannot be used on tables Q5. In the Query Design view, what step is required to make a query an update query? A. Change the query type to Update Query B. Add a totals row C. Select "Append Query" D. Set all fields to "Group By" Q6. Where do you specify the new value that records should be updated to in an update query? A. Criteria row B. Field row C. Update To row D. Sort row Q7. What should you always do before running an update query that modifies your data? A. Delete all tables B. Back up your database C. Turn off all warnings D. Run a delete query first Q8. What warning does Access typically provide before running an update query? A. That it will backup your database B. That it is about to change a certain number of rows C. That it is about to close Access D. That it cannot save your work Q9. What is the effect of turning off "Confirm Action Queries" in Access options? A. Update queries will not run B. No warning prompt will appear before running action queries C. Queries will run in read-only mode D. Access will automatically back up your database before running queries Q10. When using an update query based on two tables to fix multiple state names, which field is used to match bad spellings to good ones? A. Primary Key B. State field from both tables C. BadState from StateBadT and State from CustomerT D. Any field that is available Q11. What is the benefit of creating a lookup table (such as StateBadT) for updating values? A. It allows for dynamic mapping of incorrect values to their correct ones B. It increases the size of your database significantly C. It makes your forms read-only D. It is required for every kind of query Q12. When mapping the "Update To" value to a field in another table within an update query, which syntax should you use? A. Just type the value in quotes B. Use the field name in square brackets or include the table name for safety C. Use parentheses around the field name D. Leave it blank for Access to decide Q13. If you want to fix several states in one update query using a lookup table, what must you set up between the tables in the query? A. A sort order B. A one-to-many relationship by dragging the state fields together C. A calculated field D. A unique index Q14. Why might a company decide not to limit the state field to a two-character abbreviation? A. Some companies like to allow international addresses where states may differ B. It is easier to type long state names C. Combo boxes do not work for states D. Access does not support field restrictions Q15. What is the main difference between an update query and an append query? A. Update queries change existing records, append queries add new records B. Append queries delete old records C. Update queries only work on one table D. Append queries cannot be saved Answers: 1-A; 2-C; 3-C; 4-B; 5-A; 6-C; 7-B; 8-B; 9-B; 10-C; 11-A; 12-B; 13-B; 14-A; 15-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone tackles the topic of update queries in Microsoft Access. I want to show you how you can change values in your tables efficiently using update queries, which are an essential tool for fixing or standardizing data across many records at once.A common issue that comes up is inconsistent data entry in fields that should have standardized values. For example, in your state field, you might have entries like 'New York', 'NY', 'N. Y.', and several other variations. This can happen if the field is left as free text and users type in whatever they want. In an ideal setup, I recommend using a combo box for fields like state, so users select from a predefined list and errors like this are minimized. But if you already have existing data that is messy, let me explain how you can clean it up using update queries. To start, you first create a query in design view just like you would for any select query. Then, you switch the query type to 'Update'. This gives you a new 'Update To' row where you specify what you want the data to become. You can add your customer table, bring in the state field, and then set up your criteria to identify all the problematic values like 'New York', 'N. Y.', or 'N. Y.'. Put each variation you find in the criteria (using the OR row if needed). In the 'Update To' row, enter the correct abbreviation, like 'NY'. Before running the query, you have the option to preview the results by switching to datasheet view. This helps you confirm which records will be affected before making any actual changes. It is very important to back up your database before running any action queries like this. Updating data in bulk is a powerful tool but mistakes can be difficult to undo, so always make sure you have a recent backup just to be safe. Once you are ready, run the query. Normally, Access will warn you that you are about to update multiple rows and ask for confirmation. If you have previously turned off these warning messages, you can find the setting by going to File, then Options, then Client Settings. You will see confirm options for record changes, document deletions, and action queries. I usually leave these confirmations off, but if you are new to update queries, you might want to leave them on until you're comfortable. After running the update query, you can check your table to see that all the records have been updated as expected. You can also save this query for later use in case the problem comes up again. If you have more than one type of error to fix, for example, if both New York and Florida are entered inconsistently, you will need a more flexible approach. One method is to use an additional table that matches bad values to good ones. You can create a table with two fields, one for the incorrect entry (like 'N. Y.' or 'New York') and one for the correct version ('NY'). You can populate this table with every variant you find. Once this mapping table is ready, you can build an update query that joins your customer table to the mapping table, matching the state field in your customer table with the bad values in your mapping table. Then, you set the update to replace the value with the correct spelling from the mapping table. This method saves time and you only need to update your mapping table if new errors turn up in the future. Keep in mind, if you do international business, you may need to leave the state field flexible for longer names or abbreviations, but this mapping process still applies no matter what the data looks like. For those who want to learn more, in the members only extended cut I cover how to update product pricing based on a vendor's spreadsheet, how to identify and handle discontinued items, and how to append new products into your database. These techniques rely on similar update and append queries and can save you a lot of manual work. If you would like to become a member, you can join for access to all extended cut TechHelp videos, live sessions, and other perks. Membership starts at Silver and goes up to Platinum. However, my regular TechHelp videos like this one will always be free and available for everyone. If you found this useful, please like and share the video, and subscribe for updates on new tutorials. You will find additional resources, downloads, and links below. If you are new to Access, check out my free Access Level 1 course, which covers a lot of the basics and is available on my site or YouTube channel. Level 2 is just one dollar and also free to members. If you want your question answered in a future video, submit it through my TechHelp page. You can also join the discussions in my Access Forum or connect with me on various social platforms. A complete, step-by-step video tutorial covering everything I have discussed here can be found on my website at the link below. Live long and prosper, my friends. Topic ListHow to create an update query in AccessSetting criteria to find and fix inconsistent data entries Using the "Update To" row in query design Previewing query changes using datasheet view Running an update query and handling warning messages Creating a lookup table to standardize data corrections Building a relationship between source and correction tables Enforcing unique values with indexed fields Updating table records using a correction table Referencing fields in another table within an update query Saving and reusing update queries for future fixes |
||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access update query update queries access ms access update query ms access update query from another table access update query with join access update query multiple criteria access update query add to existing value access update field in table PermaLink Update Queries in Microsoft Access |
