|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Action Queries By Richard Rost 
5 years ago
What are Action Queries in Microsoft Access? Action Queries in Microsoft Access can be used to Update data in your tables, Append records to the end of a table, Delete data from tables, and Make Tables. I start covering Action Queries in detail in Microsoft Access Expert 13. This video is Lesson 2 of that course. Enjoy!
IntroIn lesson 2, we will learn about action queries in Microsoft Access, including what they are, what they do, and when you should use them. We will cover the four main types of action queries: update, append, delete, and make table queries, and discuss the importance of backing up your data before making any changes. You'll see the difference between select queries and action queries, and get tips on keeping your database safe while working with these powerful tools.TranscriptIn Lesson 2, we are going to learn about the different types of action queries, what they are, what they do, and when you should use them. We will also talk about backing up your data before running action queries.So far, all of the queries that we have designed in class have been select queries. Select queries are by far the most popular type of queries, and they are used for displaying data from the tables in different ways. You can say, show me all the records from the customer table, or show me all of the orders from the order table that are less than a month old. Essentially, select queries are used for selecting and displaying specific sets of data, but not actually doing anything to that data. Action queries, on the other hand, are used for modifying or manipulating the data in the tables. Whereas you only look at data with select queries, you are actually going to change data with action queries. There are four main types of action queries: update queries, append queries, delete queries, and make table queries. Update queries are used for changing the data in your table. For example, you can say, change all of the customers with the last name Smith to Jones, or change all of the orders with a 5 percent discount to a 7 percent discount. An update query is used to change the data in existing records. An append query is used to add records to a table. Let's say you have imported some new data. Maybe you downloaded some financial information, or someone sent it to you on a spreadsheet. You can import that information into your database, and now you have another table that you want to put it in. You can use an append query to copy the data from one table to another one. For example, in class, we are going to take our contacts, we are going to take our old contacts, and we are going to append them into a backup table, like an archive table. Then we are going to use the next item here, a delete query, to delete them out of the original table. So an append query adds records onto a table, basically copying them from one table to another one. Then a delete query can delete records out of a table. Be careful with delete queries. They can be dangerous. Usually, append and delete queries work together to archive data from one table to another one - old orders, old customer information, etc. Finally, you have a make table query. Make table queries are great for making new tables. Kind of obvious, but that is what they do. For example, let's say you want to create a special table with only a set of your customer data. You have to send it maybe to a different division or a mailing list company. You do not want to give them all of your data, just some of it - maybe the customers that are getting holiday greeting cards, for example. You can run a make table query to export a specific set of data. These four types of queries make up the action queries. Before we get started with working with action queries, I have to remind you and warn you, please back up your database before you start playing with action queries. If you are just working on the database from class with me, you can just download another copy and that is not a problem. But if you are working with an actual production database with your customer information, and with stuff that you might not want to have to retype, please back up your database file before you start playing with action queries. Backing up your database is quite simply this easy: you find your database file, in whatever folder you have it saved. I have mine saved in a trusted folder on my desktop. Copy and paste it - Control C, Control V - and it is as easy as that. I have just made a copy of my database. Now if I mess this one up, I have got the backup copy sitting right there. It goes without saying that you should be backing up your critical files every night anyway, using either a backup device in your office or preferably online backup. Online backup services are so inexpensive now, and with high-speed internet, there is no reason why you should not be backing up in an offsite facility. In case your office or your house burns down, all your data is safe somewhere else. Make sure you use an online backup system. For some of my recommendations for the services that I use, check out that link right there at 599cd.com/xbackup. That will take you to a list of some of my recommended backup services. You should also back up any tables that you are planning on running an update query, append query, or delete query on inside the database. This will prevent you from having to restore the whole file, because that could be a pain too. If you are going to do any kind of an action query on the ActivityT, for example, click on it, Control C, Control V, Copy and Paste. What do you want to copy it as? "Copy of ActivityT" is fine. Structure and data, and then hit OK. There is your copy right there. Just do that before you run any action queries in your database. I am going to delete that real quick. Now that we know what the different types of action queries are and what they do, and you have been warned about backing up your data, in the next lesson we will get started with an update query. QuizQ1. What is the main difference between select queries and action queries?A. Select queries only display data, while action queries modify or manipulate data. B. Action queries only display data, while select queries delete records. C. Select queries can make new tables, while action queries only view records. D. Action queries add styles to your data, while select queries import files. Q2. Which of the following is NOT one of the four main types of action queries? A. Update query B. Select query C. Append query D. Delete query Q3. What is the purpose of an update query? A. To change data in existing records in a table B. To create brand new tables C. To display filtered data sets D. To print your tables Q4. What does an append query do? A. Deletes records from a table B. Changes field names in a table C. Adds new records from one table to another table D. Exports data to a mailing list Q5. When might you use an append and delete query together? A. To archive old data from one table to another and then remove it from the original table B. To rename all records in a table C. To format dates within your data D. To import images into a database Q6. What is the function of a make table query? A. It changes the structure of an existing table B. It creates a new table with specific data from existing tables C. It only displays related data on a form D. It sorts records in descending order Q7. What is the recommended precaution before running action queries? A. Backup your database file or the tables you are working on B. Turn off your computer C. Increase your computer's RAM D. Print all your tables Q8. Why is it recommended to use an online backup service? A. It provides offsite data safety in case of local disasters like fire B. It allows you to email your database faster C. It speeds up your action queries D. It upgrades your Access version automatically Q9. What is the easiest way to backup a database file before running action queries? A. Copy and paste the file using Control C and Control V B. Save as a PDF C. Write down all the data on paper D. Use the print command in Access Q10. If you want to backup just a specific table before running an update query, what should you do? A. Copy and paste the table inside Access, including structure and data B. Delete the table and hope nothing goes wrong C. Run a make table query on the table D. Rearrange its fields alphabetically Answers: 1-A; 2-B; 3-A; 4-C; 5-A; 6-B; 7-A; 8-A; 9-A; 10-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 focuses on action queries in Microsoft Access. I want to talk about what action queries are, when to use them, and the importance of backing up your data before working with them.Up to this point, all of the queries we've discussed in class have been select queries. These are the most common type and are designed to display information from your tables in various ways. For example, you might use a select query to view all the records from the customer table or filter orders that are less than a month old. Their main job is to help you view specific sets of data without changing anything in the actual tables. Action queries have a much different purpose. Rather than just showing you data, action queries let you modify or manipulate the actual data stored in your tables. With an action query, you're actively making changes to the information in your database. There are four main types of action queries: update queries, append queries, delete queries, and make table queries. Update queries allow you to change existing data in your tables. Suppose you want to change every customer with the last name Smith to Jones, or perhaps update all orders that currently have a 5 percent discount to 7 percent instead. This is where an update query comes in handy, as it edits data within the current records. Append queries are used to add new records to a table. For example, if you have imported new data from an external source like a spreadsheet or downloaded some financial records, you might want to bring that information into your main database. An append query lets you copy records from one table into another. In class, we will take old contact records and use an append query to add them to a backup or archive table. After that, we can use a delete query to remove those same records from the original table. Delete queries are exactly what they sound like. They let you remove records from a table. You need to be very careful when using delete queries, because they can remove a lot of data quickly. Often, append and delete queries are used in pairs to move old or outdated data from your main tables into archive tables. This is common for old orders, customer info, and anything else you want to store, but not keep in your main working tables. The last type is the make table query. This is useful when you need to produce a new table based on a specific set of records. For example, suppose you want to send a list of certain customers to another department or an outside company, but you only want to include a subset of your customers - like those who should receive holiday cards. A make table query extracts the data you need and creates an entirely new table with just those records. So, those are the four main action queries: update, append, delete, and make table. Before you start working with action queries, I have to stress the importance of backing up your database. If you're following along with the class using the sample database, just download a fresh copy if something goes wrong. But if you're working with your own production database, one that contains important data you don't want to lose, make sure you back up your file before you start making changes with action queries. Backing up your Access database couldn't be simpler. Find your database file in your folder - for example, I keep mine in a trusted folder on my desktop. Just make a copy by using Control C and Control V. That gives you a backup copy on the spot. If you make a mistake or something happens, you can restore your backup right away. Ideally, you should be backing up important files every night using a backup device in your office, or even better, by using an online backup service. With today's fast internet and low costs, there's really no excuse not to store your backups offsite for extra protection in case something disastrous happens to your workplace or your computer. For recommendations on reliable online backup services, you can visit 599cd.com/xbackup, where I share some of my top picks. Another tip: before you run any action queries (update, append, or delete) on specific tables, back up those individual tables inside your database. Just select the table, use copy and paste, and save it as a new table. Make sure to include both structure and data in your copy. This way, if you need to restore a specific table, you won't have to restore the entire database. Now you have an overview of what the different types of action queries are, how they work, and why backing up your data is critical before using them. In the next lesson, we'll start working with update queries. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListTypes of action queries in AccessDifference between select and action queries Function of update queries Using append queries to add records to a table Using delete queries to remove records from a table Using make table queries to create new tables Importance of backing up your database before running action queries How to back up your database file manually How to back up specific tables within your database |
||
|
| |||
| Keywords: TechHelp Access action queries PermaLink What are Action Queries in Microsoft Access? |
