Introduction

Welcome! Action Queries: Update & Report

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Expert Level 13. In this course we will focus primarily on action queries, with a special emphasis on update queries. We will begin by discussing report WHERE conditions to filter records when opening reports, then explore the different types of action queries: update, append, delete, and make table. Throughout the lessons, I will show you how to use update queries to modify data, fix data entry mistakes, assign sales reps, adjust product pricing, and mark orders as shipped. We will also review homework from the previous class and discuss related seminars and resources available on AccessLearningZone.com.

Navigation

Prev: X13
Next: Lesson 01

Keywords

Access Expert, action queries, update queries, append queries, delete queries, make table queries, report WHERE conditions, string manipulation, UCase, LCase, StrConv, grouping levels, open report WHERE, data backup, invoice report, sample database

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Expert Level 13 brought to you by AccessLearningZone.com. I am your instructor Richard Rost.

Today's class focuses primarily on action queries and update queries specifically. But we will begin by learning about report WHERE conditions. This is where you can open a report and specify conditions in the button that opens the report so you can show a single record, multiple records, records between two dates, and so on. We will see a couple of different examples today.

Then we will learn about all the different types of action queries: update, append, delete, and make table queries. We will spend the majority of time in today's class focusing on update queries.

We will build some simple update queries. We will change a company's name if it is in a particular state. We will learn how to use functions inside our update queries to assign sales reps based on what state customers are in. We will go through and fix mistakes in data entry using update queries. For example, we will capitalize all of the names of our states and set the first and last names of our customers in the proper case, which is the first letter capitalized and the rest of the name lowercase. We will see how to use queries to add 5 to all of our product prices, for example, and how to update all of the paid orders in our system, mark them as shipped, and set a ship date.

There are tons of examples in update queries in today's class.

This class follows Access Expert Level 12. I strongly recommend you watch level 12 and all of the previous classes before starting today's lessons. Level 12 focuses primarily on report grouping and sorting levels. You will see one example of how grouping levels are used in the first lesson of today's class.

This course is designed to be used with Microsoft Access 2013. If you are using 2010 or 2007, you should not have any problems following along. All three versions are pretty similar. If you are using Access 2003 or earlier, you should be okay with today's class. I did cover most of this material in my older Access 222 class. I covered update queries in that lesson as well. All of the topics and the concepts in today's class are the same. The menus are a little different, however. So if you are using 2003, look for my Access 222 class.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes, which you are in now. When you finish all of the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications.

Each group of classes is broken down into multiple levels: level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you. Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is real easy to use.

Now, I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database. One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now, let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson one, we are going to begin by reviewing the homework from last class. We will make an invoice based on report grouping levels instead of a subform. And we will learn about open report WHERE conditions where you can print batches of records in a report, such as print all of the orders for a particular customer.

In lesson two, we are going to learn about the different types of action queries, what they are, what they do, and when you should use them. And we will talk about backing up your data before running action queries.

In lesson three, we are going to look at the action query type that you are going to use most of the time: an update query.

In lesson four, we are continuing our look at update queries. We are going to learn about a couple of different string manipulation functions: UCase, LCase, and StrConv.

In lesson five, again, we are continuing with update queries. We are going to learn how to add 5 to all of our product prices and mark all paid orders in the system as shipped and set a ship date.

In lesson five, we are continuing our look at the action query type that you can print out.

Intro

Quiz

Q1. What is the primary focus of Access Expert Level 13?
A. Action queries, specifically update queries
B. Building forms and subforms
C. Database security settings
D. SQL programming

Q2. What is a WHERE condition used for in reports?
A. To format columns
B. To set the background color of the report
C. To specify which records to display or print
D. To create a new table

Q3. Which of the following is NOT an action query type mentioned in this lesson?
A. Update query
B. Search query
C. Append query
D. Delete query

Q4. Which function can be used in update queries to convert text to uppercase?
A. UCase
B. Format
C. Mid
D. Round

Q5. In which situation would you use an update query?
A. To add new records to a table
B. To remove duplicate records
C. To change existing data based on specific criteria
D. To create a copy of a table

Q6. What is recommended before running action queries on your data?
A. Increase your screen resolution
B. Backup your data
C. Compact and repair the database
D. Print all reports

Q7. What does an append query do?
A. Deletes existing records
B. Updates specific fields
C. Adds new records to a table
D. Sorts the records in a table

Q8. If you want to apply proper casing to customer names (first letter uppercase, rest lowercase), which function might you use?
A. StrConv
B. Sum
C. DLookup
D. Val

Q9. Which Microsoft Access versions are specifically mentioned as being compatible with the course material?
A. 2013, 2010, 2007
B. Only 2013
C. 2003, 2002, 2000
D. Only Access for Office 365

Q10. What is suggested as the best way to learn from this class?
A. Memorize vocabulary and theory
B. Build the sample database step by step while watching the videos
C. Join only the advanced classes immediately
D. Read the Microsoft Access manual

Q11. What should you do if you get stuck or do not understand something from the lesson?
A. Skip the topic and move on
B. Watch the video again or ask in the student forum
C. Wait for the next class
D. Close the database and restart the lesson

Q12. What is the student forum used for?
A. To share unrelated software programs
B. To copy assignments from other students
C. To ask questions and see responses about lesson topics
D. To report database corruption issues

Q13. What previous level does Access Expert Level 13 build upon?
A. Level 12
B. Level 8
C. Level 14
D. Level 1

Q14. What is the recommended action before trying to apply concepts from the class to your own projects?
A. Master the sample database from class first
B. Uninstall previous Access versions
C. Upload your project to the forums
D. Skip the sample database and work on your project directly

Q15. What is one way suggested for learning Microsoft Access, according to the video?
A. Taking apart someone else's database
B. Reading only theoretical concepts
C. Memorizing error codes
D. Using PowerPoint for database design

Answers: 1-A; 2-C; 3-B; 4-A; 5-C; 6-B; 7-C; 8-A; 9-A; 10-B; 11-B; 12-C; 13-A; 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.

Summary

Today's video from Access Learning Zone is part of my Expert Level 13 Microsoft Access course. My name is Richard Rost, and I'm here to guide you through the topics we'll be covering today.

This lesson centers mostly around action queries, especially update queries, but we begin by taking a look at report WHERE conditions. I'll show you how to open reports with specific filtering directly from a command button, which makes it possible to display single records, a group of records, or even records within a range, such as between two dates. We'll walk through several practical examples to illustrate how this works.

From there, we move on to the different types of action queries available in Access: update, append, delete, and make table queries. Most of today's class is devoted to the update query because it's the one you're likely to use most. Together, we'll build several update queries to handle real-world scenarios. For example, you'll see how to change company names for customers in a particular state, and how to use functions within update queries to assign a sales rep based on a customer's state. We'll also use update queries to correct data entry mistakes, such as capitalizing state names, and converting first and last names to proper case so they look professional.

You'll also learn how to perform calculations in update queries, such as adding 5 to all product prices, and how to automate marking all paid orders as shipped and set a ship date for them. There are plenty of demonstrations to get you comfortable using update queries to transform and clean up your data.

This class builds on the foundation laid in Access Expert Level 12, so make sure you have completed that and the earlier courses before starting here. Level 12 focused heavily on report grouping and sorting, which we'll reference at the beginning of this lesson to reinforce how report grouping levels are used.

I created this course for users of Microsoft Access 2013, but if you're working in Access 2010 or 2007, the material should still apply with little to no difference. Even if you're using Access 2003 or earlier, you can still follow along, since I have covered most of these concepts in my earlier Access 222 class, though the menu layouts may be different.

My series of Access courses are divided into beginner, expert, advanced, and developer levels. By the time you reach these expert classes, you should be familiar with everything presented in the beginner and earlier expert levels. Advanced classes will introduce event programming and macros, while developer courses dive into Visual Basic for Applications. Each group is organized into numbered levels so you can build up your knowledge step by step.

Beyond the regular course paths, I also offer seminars focusing on specialty topics. These include building web-based databases, calendar-style forms and reports, database security, handling images and attachments, service business management, accounts payable tracking, the SQL language, loan amortization, and much more. You can see the full list on accesslearningzone.com.

If you have any questions about today's lesson, I encourage you to participate in the student forums. If you're viewing this video in the online theater on my website, you'll find an integrated student forum for each lesson with questions, answers, and commentary from other students and myself. Reading through these discussions as you begin each lesson can be very helpful, and I welcome you to contribute your own questions and feedback.

If you're not watching these lessons on my website, you can still access the forums later at accesslearningzone.com/forums.

To get the most benefit from this course, I recommend first watching each lesson all the way through without pausing to work alongside me. Afterward, replay the lesson and build the same sample database I demonstrate, step by step. Focus on mastering the sample project before trying to use the concepts for your own projects.

If you run into trouble or something isn't clear, feel free to rewatch the lesson or post your question in the student forum. I'm happy to help clarify anything you're struggling with. Approach the material with an open mind, and remember that while Access can feel overwhelming at first, the more you practice, the easier it becomes.

While I do encourage you to follow along and build the database yourself, if you'd like to download my finished sample database, you can find it at accesslearningzone.com/databases. Sometimes, working through someone else's completed database is the quickest way to figure out how something works. This is how I learned a lot myself by exploring the Northwind Traders sample database that comes with Access. Each of my courses includes a sample database on the website.

Let's review the structure of today's class and what we'll be covering. In the first lesson, we'll start by reviewing the homework from the previous class. You'll learn to create an invoice using report grouping levels rather than a subform, and I'll explain how to open a report and apply WHERE conditions to print batches of records, such as all orders for one customer.

In the second lesson, I'll introduce the concepts of action queries, explain their purposes, and stress the importance of backing up your database before running any action queries.

Lesson three zeros in on update queries, which you'll find are the most frequently used type of action query.

Lesson four takes a closer look at string manipulation within update queries. You'll learn how to use functions like UCase, LCase, and StrConv to standardize and clean up your text data.

In lesson five, we'll continue working with update queries to perform batch price updates and automatically mark paid orders as shipped, including setting the ship date.

If you want a thorough, step-by-step walkthrough of everything I've covered here, a complete video tutorial is available on my website at the link below.

Live long and prosper, my friends.

Topic List

Report WHERE conditions for opening reports
Printing single and multiple records in reports
Printing records between two dates in reports
Report grouping levels to make invoices
Action query types overview
Backing up data before action queries
Update queries basics
Simple update queries by criteria
Using functions in update queries
Assigning sales reps via update queries
Fixing data entry mistakes with update queries
Capitalizing state names using update queries
Setting customer names to proper case
Using string functions: UCase, LCase, StrConv
Adding values to all product prices via update queries
Updating paid orders as shipped with ship date
Append, delete, and make table queries introduction

Article

Welcome to this guide on working with action queries in Microsoft Access, focusing especially on update queries. We will also explore how to use WHERE conditions with reports to control the data you show. These are powerful tools for managing and editing your database efficiently.

Let us start with report WHERE conditions. In Access, you can use WHERE conditions to control exactly which records appear when you open a report. For example, rather than always seeing all orders, you could show orders for just one customer or a set of customers. To do this, you can set the WHERE condition when opening the report using a button or VBA code. Suppose you have a report called InvoiceReport and you want to open it to show only orders for CustomerID 7. In VBA, you could use the following code:

DoCmd.OpenReport "InvoiceReport", acViewPreview, , "CustomerID=7"

This line opens the report and only shows records where the CustomerID is 7. You can use more complex criteria too, such as showing invoices between two dates or only orders that are shipped. This is done by changing the WHERE condition in the VBA code when opening the report.

Now, let us talk about action queries. Action queries are queries that make changes to your data. There are four main types in Access: update, append, delete, and make-table queries. Update queries allow you to change the data in existing records. Append queries add new records to a table. Delete queries remove records, and make-table queries create a new table from your selected records.

Before you run any action queries, especially on important data, it is essential to back up your data. Once you run an update, append, or delete query, the change is permanent and cannot be undone by simply pressing Undo. To back up your data, you can copy your table, export your data, or make a backup of the database file.

Let us focus on update queries, since you will use them the most. Update queries allow you to change one or more fields in one or more records with a single action. For example, suppose you want to change the company name for all customers in New York State to NY Customers. You would build an update query using the design view, add your customer table, and set your criteria to limit the update to just the records you want. In the Update To row of the field you want to change, you enter the new value. In the Criteria row, set the filter for which records should be updated. When you run the query, it updates only those records.

You can use functions in your update queries to make changes based on the data in each record. For instance, you might want to assign a sales rep based on state. In the Update To row, you can use an IIf function to check the state and set the sales rep accordingly:

IIf([State]="NY","John Smith","Other Rep")

This example sets the SalesRep field to John Smith if the State is NY, and Other Rep otherwise.

Update queries are also great for fixing data entry problems. For example, you can standardize the way state names are capitalized or ensure that customer names are in proper case, where only the first letter is capitalized. Access includes string manipulation functions for this purpose. UCase turns text to uppercase, LCase to lowercase, and StrConv can convert text to proper case. Suppose FirstName is currently in all lowercase. You can use:

StrConv([FirstName],3)

The 3 tells StrConv to use proper case, capitalizing the first letter of the name and making the rest lowercase.

You can also adjust numeric values across many records with an update query. If you need to increase all product prices by 5, create an update query, add the Product table, and in the Update To row for the Price field enter:

[Price] + 5

When you run the query, all prices go up by 5.

Update queries are also useful for setting statuses in your tables. For example, to mark all orders as shipped and set a ship date for those that are already paid, you would set criteria to select only paid orders and use Date() in the Update To row for the ShipDate field. This would give them todays date as the ship date.

Throughout your work, remember to preview the records affected by your query before running it. You can do this by clicking the View button, which shows you which records will be updated before you actually make changes.

Action queries are powerful tools within Access. They let you rapidly change and correct your data with careful criteria and functions. Always work slowly, back up your data first, and use the View function to check your work before making updates permanent.

This guide has covered the essentials of using report WHERE conditions and action queries, especially update queries, in Microsoft Access. With these skills, you can clean up your database, automate changes across many records, and create more useful reports tailored to your needs. Take the time to practice these techniques on a sample database before applying them to your own projects, and always ask for help or guidance if you are not sure about a step. With these methods, you will find managing your data becomes much easier and more reliable.

Primary Topics

action queries, update queries, report WHERE conditions, data correction, string functions, batch processing, report filters, database backup

Secondary Topics

append queries, delete queries, make table queries, data normalization, report grouping, case conversion