Introduction

Welcome! Scrub Data, Split Names, Update Email

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

Welcome to Microsoft Access Expert Level 14. In this course we will continue working with Action Queries, focusing on advanced Update Query examples such as scrubbing unformatted phone numbers, changing email domain names, separating first and last names stored in one field, and fixing non-relational tables for better database design. We will also discuss Append Queries for tasks like adding daily student attendance records. This class builds on concepts from Access Expert Level 13, so you should be familiar with the basics of Action Queries before proceeding. Additional seminars and resources are available on my website for further learning.

Navigation

Prev: X14
Next: Lesson 01

Keywords

Access Expert, action queries, update queries, append queries, data scrubbing, phone number formatting, clean up data, email domain change, hyperlink fields, separate first and last name, relational tables, department IDs, attendance tracking, student att

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

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

Today's class continues our look at Action Queries. Action Queries change or manipulate the data in your tables.

We'll start out by covering four more examples of Update Queries. First, we'll learn how to scrub the data in your tables. For example, let's say we were given a table where the users were able to type in phone numbers or fax numbers without any formatting in any particular way they wanted to. So we want to take those fax numbers, remove any hyphens or parentheses or spaces or dots, make sure they're all exactly ten characters long, and add area codes where needed. We'll learn how to do that with our Update Queries.

Then, we'll learn how to change the domain name on an email address. Manipulating hyperlink fields can be a little tricky, so we'll learn how those work. We'll learn about the different parts of a hyperlink field, and we'll learn how to replace the domain name. For example, if all of the Yahoo.coms need to be changed to Microsoft.com, we can do that with one Update Query.

Then, one of the most popular questions I get asked is how to separate first and last name. You've got a database someone else gave you, or maybe you built it this way before you knew better. Instead of making two separate fields, one for first name, one for last name, you put them together in one field. In this class, I'll teach you how to separate them into first name and last name fields.

Another popular question is, "I typed in data in my table and it's not relational." For example, maybe we'll use employees and departments. Instead of putting a department ID and a separate department table, I typed in department names like sales, stock, counter, management, and so on. So we'll fix that.

Then, we'll learn about Append Queries. Update Queries change data in a table. Append Queries add rows onto a table. We'll make a student table with a list of students. Then, we'll make an attendance table. Each day, when I want to take attendance, instead of having to type in all of the students, I'll run an Append Query, which will append all of the students into my attendance table. Then, all I have to do is check the box for present. The next day, I run the Append Query again, it copies all the student names into the table, and again, I just check who is present and who is not.

This class follows Access Expert Level 13. In Level 13, I taught you how Action Queries work, and specifically, we learned about Update Queries. We're going to spend a lot of time today covering additional Update Query lessons. You should, however, have taken Expert Level 13 first, so you get the basics of how Update Queries work. I'm not going to take time reviewing that.

This class was recorded with Access 2013. However, if you're using 2007 or 2010, the examples should be almost identical. If you're using Access 2003 or earlier, the examples should still work. However, the interface is much different. I did cover most of this material, but not all of it, in my Access 222 class, which is the older version. However, try following along if you've gotten this far already and you've already purchased the class. Try following along, you should be able to figure it out. If not, and you've purchased this class already, contact me. If you're using Access 2003, I'll give you a copy of 222.

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're 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're 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. I actually create the same database that I make in the video, step by step. Don't try to apply what you're learning right now to other projects until you've mastered the sample database from class. If you get stuck or don't understand something, watch the video again from the beginning or tell me what's wrong in the student forum, and I'll 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'll see that it's 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'll find there's a sample database for each of my courses on my website.

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

In lesson one, we're going to learn how to scrub the data in our tables using an update query. A popular request that I get all the time is from customers who say they've got a field like a fax number or a phone number field, and their users have been just typing in data in any particular format because there were no restrictions on the field like an input mask. So we're going to take fax numbers out of all different kinds of formats and clean them up so they're all exactly ten digits long and have no special formatting characters in them.

In lesson two, we're going to learn how to change the domains in our email addresses. One of our big clients, for example, just changed their internet domain address from, let's say, amacron.com to 599cd.com. So I'm going to show you how to go through all of the email addresses and change the domain names over. This is going to involve understanding how hyperlink parts work.

In lesson three, we're going to use an update query and some string manipulation functions to perform one of the most requested functions that I get asked about: separating first and last names if your user put them into the same field.

In lesson four, we're going to mix together our skills with update queries and our skills with relational tables, and we're going to fix a table that wasn't designed to be relational in the beginning. Now we're going to make it relational. For example, we have an employee table and whoever built the database was typing in department names as straight text. As we know, that's not good relational design. So in this lesson, we'll fix that and turn department names into department IDs.

In lesson five, we're going to learn how to set up an append query to track daily student attendance for students in a class.

Quiz

Q1. What do Action Queries do in Microsoft Access?
A. They change or manipulate the data in your tables
B. They add new tables to your database
C. They only create new forms
D. They are used to customize the Access interface

Q2. What is an Update Query used for in Access?
A. To modify existing data in a table
B. To add rows to a table
C. To delete a table
D. To change the database structure

Q3. When scrubbing phone or fax number data in a database, what is a typical goal?
A. Remove all formatting and ensure exactly 10 digits
B. Add more formatting characters
C. Shorten all numbers to 7 digits
D. Allow any text in the field

Q4. How can you change the domain in an email address for all records in a table?
A. Use an Update Query with string manipulation functions
B. Use an Append Query
C. Manually edit each record
D. Change the primary key

Q5. Why might it be necessary to update hyperlink fields when modifying email addresses?
A. They have multiple parts that need to be changed consistently
B. They only store plain text addresses
C. Hyperlinks cannot be updated
D. Hyperlinks are not stored in tables

Q6. If a database has a single field containing both first and last names, what function is described to separate them?
A. Use an Update Query with string manipulation
B. Create a relationship between tables
C. Apply a validation rule
D. Change the data type to Lookup

Q7. What is the relational database design best practice for storing departments in an employee table?
A. Store a Department ID linked to a separate Department table
B. Type the department name as text in each employee record
C. Use a memo field for departments
D. Store multiple departments in one field, separated by commas

Q8. What is the main function of an Append Query?
A. Add rows to a table
B. Modify existing data in a table
C. Remove duplicates from a table
D. Recalculate totals

Q9. What scenario was given as an example for using an Append Query in this course?
A. Adding students to an attendance table each day
B. Removing all absent students from a table
C. Changing course names in a student table
D. Merging multiple tables

Q10. What does the instructor recommend you do to get the most out of the course?
A. Watch each lesson through once, then replay and follow along with examples
B. Only watch the videos and do not practice
C. Skip to the advanced lessons immediately
D. Try applying what you learn right away to other projects without practicing

Q11. Where can you download completed sample database files for this course?
A. accesslearningzone.com/databases
B. Microsoft.com
C. Your local Access install folder
D. Only available by email request

Q12. What should you do if you are having trouble following along with Access 2003 or earlier?
A. Contact the instructor for a copy of Access 222
B. Skip the lesson
C. Switch to using Access 2013 immediately
D. Reinstall Access

Q13. In which class did the instructor first cover Action Queries and Update Queries?
A. Access Expert Level 13
B. Access Beginner Level 2
C. Access Advanced Level 5
D. Access Developer Level 1

Q14. Which of the following is NOT mentioned as a seminar available at accesslearningzone.com?
A. Building web-based databases
B. Writing work orders for service businesses
C. Securing your database
D. Creating computer games

Q15. What is the best way to ask questions about the lessons presented in the course?
A. Post them in the student forums on the website
B. Send a handwritten letter
C. Call Microsoft support
D. Comment on a YouTube video

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-D; 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 continues our exploration of Action Queries in Microsoft Access. I am Richard Rost, your instructor for this course.

In this session, we are building on the foundation set in Access Expert Level 13, where we started learning about Action Queries, focusing mainly on Update Queries. Action Queries are an essential part of Access, as they allow us to change or manipulate the data in our tables in bulk. This time, we'll walk through several additional examples to sharpen your skills, specifically using Update Queries in new and practical ways.

First, I will show you how to clean up, or "scrub," the data in your tables. For instance, if you have a table full of phone or fax numbers entered in a variety of formats by users - with parentheses, hyphens, dots, or spaces - I'll demonstrate how to standardize these. The goal is to remove any special characters, ensure each number is exactly ten digits, and, if any area codes are missing, how to add them consistently across your records using an Update Query.

Next, we will tackle email addresses, focusing on situations where a business changes its domain. If you need to update every instance of an old domain (for example, changing every '@yahoo.com' to '@microsoft.com'), I will show you how to search through your records and update these efficiently. We'll also look at how Access stores hyperlink fields, since handling those can be a bit more involved. You will learn about the different parts of a hyperlink field and how to manipulate them with Update Queries.

Another common scenario I am frequently asked about involves splitting a combined name field into separate first and last name fields. Many databases come to me with names stored together, like "John Smith," instead of in separate fields. I will teach you techniques for separating these out, so your data conforms to better relational design standards.

We will also address the issue of normalizing tables. Sometimes when data is imported or entered by hand, you might find department names written directly in the Employee table rather than using a Department ID linked to a separate Department table. This is not relational, and I will show you how to fix this by updating your tables so they use IDs instead of plain text for lookups.

After that, I will introduce you to Append Queries. While Update Queries edit existing records, Append Queries let you add new rows to a table. A practical example we'll cover involves student attendance. Imagine you have a student list and want to quickly create a daily attendance record - for each class session, you can use an Append Query to quickly populate your attendance table with all student names, then just check off who was present or absent each day.

Please note, this course was recorded using Access 2013, but the instructions apply equally to Access 2007 and 2010. If you are using Access 2003 or an earlier version, the steps should still work, but the interface will look different. While I previously covered most of this material in my older Access 222 course, if you are struggling with an older version and have already purchased this class, feel free to contact me for help or a copy of that older course.

My training is structured into Beginner, Expert, Advanced, and Developer levels. Since you are in an Expert class now, I assume you are comfortable with all of the beginner topics and already have an idea of how basic Update Queries work. Once you have completed all of the Expert Level classes, you can move on to the Advanced classes, which cover event programming and macros, and then to the Developer classes, which focus on Visual Basic for Applications.

You will also find a number of special topic seminars on my website. These cover everything from building web-based databases to designing specialized reports, securing your databases, working with attachments, tracking accounts payable, and learning the SQL language, among many others.

If you have questions about anything we discuss today, I encourage you to participate in the student forums available on my website. You can view questions from other students, my answers, and shared comments. If you are not watching these lessons directly on my website, you can still access the forums later at accesslearningzone.com/forums.

For the most effective learning experience, I recommend you watch each lesson all the way through once before trying anything on your computer. After that, replay the lesson and follow along step by step, recreating the sample databases in the class. You will learn best by practicing with the same examples I use in the video. If you run into trouble, rewatch the video, or let me know in the student forums and I will do my best to help.

If it helps, you can download a sample copy of the finished database I create in the lessons from accesslearningzone.com/databases. Sometimes inspecting a completed database makes it easier to understand how all the parts come together.

Here's an overview of what you can expect in each lesson today:

- Lesson 1: Scrubbing data in your tables using an Update Query. We will clean up phone or fax numbers stored in various formats so that they are all ten-digit numbers without any special characters.

- Lesson 2: Modifying email domains in your records. I will show you how to replace all instances of an old email domain with a new one, including techniques for handling hyperlink fields.

- Lesson 3: Separating first and last names into individual fields using string manipulation functions and Update Queries.

- Lesson 4: Refactoring a non-relational table to use proper table relationships. Specifically, we will convert department names in an employee table to department IDs.

- Lesson 5: Creating and using an Append Query to set up daily attendance tracking for students, so you can easily generate attendance sheets for each session.

You will 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 List

Scrubbing phone and fax numbers with update queries
Removing special characters from phone fields
Ensuring phone numbers are ten digits
Adding area codes to phone numbers
Changing email domain names with update queries
Manipulating hyperlink fields
Understanding hyperlink field components
Replacing domain names in email addresses
Separating first and last names in one field
Splitting name fields using string functions
Fixing non-relational tables
Converting department names to department IDs
Making tables relational with update queries
Using append queries to add records
Setting up append queries for attendance tracking
Copying student data into an attendance table

Article

Welcome to this tutorial on advanced action queries in Microsoft Access. In this lesson, I will guide you through techniques for updating and manipulating data in your Access tables. We will look at methods for cleaning up data, changing information in bulk, and structuring your tables more efficiently, focusing on practical examples that you are likely to encounter when working with real-world databases.

To start, let's discuss how to clean or scrub data stored in your tables, particularly for fields like phone numbers or fax numbers. Often, when users input phone numbers without formatting rules, you end up with a mix of hyphens, parentheses, spaces, and dots, which can make searching and sorting much more difficult. To organize this information, we can use an update query to strip out unwanted characters and ensure that all phone numbers are exactly ten digits long. For instance, if users enter numbers as (555) 123-4567, 555.123.4567, or 555-123-4567, we want to transform them all into 5551234567. In Access, you can use string manipulation functions like Replace to substitute or remove characters. For example, in the update query, you can set the "Update To" field to:

Replace(Replace(Replace(Replace([PhoneField], "(", ""), ")", ""), "-", ""), " ", "")

This expression removes parentheses, hyphens, and spaces. If you have dots in your data, just add another Replace function for ".". After cleaning the field, you can also check the length using the Len function to ensure the result is ten digits. If you need to add a default area code to numbers missing one, you can use a query with criteria on the Len function and update those records accordingly.

Next, you might need to update email addresses in your table, such as when your company changes its domain name. Changing email addresses manually would be tedious, especially if you have hundreds or thousands of records. Fortunately, Access allows you to do this quickly with an update query. Email addresses are often stored as text, but sometimes they are stored as hyperlinks, which use a special syntax with display text, addresses, and subaddresses separated by the hash sign (#). You need to make sure you are replacing the domain part of the email, not the entire field. For example, if you need to change all occurrences of "@yahoo.com" to "@microsoft.com" in a field named Email, you can use:

Replace([Email], "@yahoo.com", "@microsoft.com")

If your field is a hyperlink, you may need to split and reconstruct the parts accordingly, but the Replace function is a good start. Knowing how the hyperlink is formatted will help you adjust your update query.

A common problem you may face with inherited databases is having first and last names combined in a single field. Although best practice is to separate them, sometimes you need to split them after the fact. Let's say you have a field called FullName with entries like "John Smith" or "Mary Ann Jones". To split these, we use string functions like Left, Right, Mid, and InStr. InStr finds the position of the first space character, which usually separates the first name and last name. You can create two update queries: one to extract the first name and another for the last name. For the first name:

Left([FullName], InStr([FullName], " ")-1)

For the last name:

Mid([FullName], InStr([FullName], " ")+1)

This handles most names where there is only one space. If you have middle names or multiple spaces, the logic can be adjusted as needed. Ideally, you create two new fields in your table, FirstName and LastName, run the above update queries, and store the separated names.

You may also encounter situations where a database was not designed with relational principles. For example, you might have an Employees table where the Department field contains department names as plain text, like "Sales", "Stock", or "Management", instead of using a DepartmentID referencing a related Departments table. This approach is not optimal because it can lead to inconsistencies, like spelling errors or duplicate department names. To fix this, first create a Departments table with a DepartmentID and DepartmentName. Add unique department names from your employees table into this new table. Then, add a DepartmentID field to your Employees table. Next, use an update query with a join between Employees and Departments on the department name fields, updating Employees.DepartmentID to match Departments.DepartmentID where the names are equal. Once you confirm that all records are updated correctly, you can remove the plain text Department field from Employees. This process establishes a relational structure, making your data more organized and less prone to errors.

Finally, let's talk about append queries. While update queries change existing data, append queries add new records to a table. This is useful for scenarios like attendance tracking. For example, suppose you have a Students table with a list of students and an Attendance table recording daily attendance. Instead of manually typing student names into Attendance each day, you can run an append query that copies all student names from Students into Attendance with the current date. After appending, you just need to mark who was present or absent. The next day, run the append query again to generate a new set of records for that date. This method saves time and ensures consistency.

To summarize, action queries in Access are powerful tools for manipulating large volumes of data efficiently. You can use update queries to clean up and standardize data, fix design issues, and change bulk information like email domains. Append queries let you quickly add groups of records for tasks like attendance or tracking repeated events. Understanding and using string functions like Replace, InStr, Left, and Mid, along with carefully structured queries, will let you maintain high-quality databases and speed up your workflow.

If you want to see sample databases or need further guidance, you can download example files or reach out for help in user forums. Practicing these techniques on sample tables is the best way to become comfortable working with action queries in Access. Focus on mastering the principles by trying out each example as described here, and you will be able to apply them to your own projects with confidence.