Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

< Previous: Access Expert 13

Next: Access Expert 15 >

Access Expert Level 14

Expert Microsoft Access Tutorial - 1 Hour, 34 Minutes
This Microsoft Access video tutorial picks up where Expert Level 13 left off. In this class we will continue to learn how to use Update Queries to modify data, and Append Queries to copy records from one table to another. Topics include:
  - More Update Queries
  - Scrubbing (Cleaning) Data
  - Data with Inconsistent Formatting
  - Use Replace Function to Change Text
  - CStr, Nz, InStr, Left, Right Functions
  - Separate First & Last Names
  - Fix Non-Relational Tables
  - Append Queries
  - Daily Student Attendence Table

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.


Access Expert Level 14
Description: Access Expert Level 14
Versions: Recorded with Access 2013. Also use with 2007 and 2010. Everything in this class should work for all versions of Access.
Pre-Requisites: Access Expert Level 13 strongly recommended
Running Time: 1 Hour, 34 Minutes
Cost: $24.99

This class picks up where Expert Level 13 left off. In Level 13 we started learning about Update Queries. In today's class we will go over four more very popular examples of Update Queries. We will begin by learning how to "scrub" data. In this example, we've received a list of phone / fax numbers in a table, but there's absolutely no formatting in the table. We want to clean up the data so it's all in a unified format: exactly ten digits long and no punctuation (digits only). We'll use an Update Query for this. We'll learn how to use the Replace and Len Functions.


In the next example, one of our clients recently changed their web domain name, so we need to edit the email addresses of all of their clients in the database and change to We'll learn how hyperlinks are stored in the table (they're not just simple text fields). We'll use the CStr function to display them as simple text. We'll see how we can use the HyperlinkPart function to break up the components of a hyperlink. We'll use the NZ function to convert null values to zero (or in this case, empty strings).


Next is a very popular problem: how to split first name and last name into separate fields. I get emailed asking how to do this at least once a week. You've got a table that someone typed in the customer first and last names into a single field. We learned on day one that this is a big no-no. You should always put these in as separate fields, but whomever built the database you were given didn't know better. So, we'll learn how to split them apart. You'll learn how to use the InStr, Left, Right, and Mid functions to accomplish this goal.


Our last Update Query example involves a table that someone gave you that wasn't properly normalized (relational). It's a list of employees with their departments typed in as text. Again, a big no-no. So you need to create a department table and then convert the department names over to Department IDs.


Finally, we'll begin learning about Append Queries, which allow you to add records on to the end of a table, or copy records from one table to another. We will create a student table, and a daily attendance table. We'll run our Append Query to copy the list of students to the attendance table every day so that all we have to do is check off which students are absent, instead of having to enter ALL of the students EVERY day.


This is the 14th class in the Access Expert series. There's a lot of great material in this class. Learning Update and Append Queries will add tremendous power to your databases. Of course, if you have any questions about whether or not this class is for you, please contact me.


Complete Outline - Access Expert Level 14

00. Intro (9:13)

01. Scrubbing Data (15:49)
Cleaning Data with Update Query
Inconsistent Data Formats
Fax Numbers with Different Formats
Clean them to look uniform
Backup Your Data!
REPLACE() Function
Replace one string with another
Watch Field Names Carefully
Make sure you use [Brackets]
Null values can cause problems
LEN() Function
Length of String
Show FaxNumbers 7 digits long

02. Change Email Domains (12:39)
Hyperlink fields are not simple text
CSTR() Function
Convert to String
HyperlinkPart() Function
Change Domain Name change to
NZ() Function
Null to Zero
03. Separate First and Last Names (19:29)
Create Person Table
Put First and Last Names Together
INSTR() Function
Determine the position of the SPACE char
LEFT() Function
RIGHT() Function
MID() Function (Discussion)
Check for Prefix, Suffix, Middle Initial

04. Fix Non-Relational Table (9:51)
Employees and Departments
Turn Department Names into IDs
Query Join on Text Values
Update to IDs from other table

05. Daily Student Attendance (18:34)
Create Student Table
Attendance Table
Make Append Query
Append To Table Name
Current Database
Another Database (Discussion)
Only Students from One Class
Criteria in Append Query

06. Review (8:14)


Keywords: Update Queries, Append Queries, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, action queries, scrubbing data, replace function, len function, length of string, cstr function, hyperlinkpart function, change domain name, nz function, null to zero, instr function, left function, right function, daily student attendance

Student Interaction: Microsoft Access Expert 14

Richard on 9/28/2013:  Microsoft Access Expert Level 14 is 1 hour, 34 minutes long. This tutorial will continue our focus on Update Queries to manipulate data, and we'll begin learning about how to use Append Queries to copy records from one table to another. Topics include: - More Update Queries - Scrubbing (Cleaning) Data - Data with Inconsistent Formatting - Use Replace Function to Change Text - CStr, Nz, InStr, Left, Right Functions - Separate First & Last Names - Fix Non-Relational Tables - Append Queries - Daily Student Attendence Table Click here for more information on Access Expert Level 14, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 13. The next class in the series is Access Expert 15 which is now available.
Deon Riley on 10/13/2013: Hi Richard,

Your teaching makes so easy for me to split bad data and to replace and minipulate data; however, this next scenario frustrates me, which is:

I've inherited and now have to fix a Table with Staff data that looks like this:

ID StaffName
1 DeonRiley
2 RicardoRosti
3 KarynJones
4 BevCramer
5 StevePlessis
6 JockHobbs
7 MelSmithy
8 PeachesGedore
etc. over 10000 records

As you can see the above bad data the First and Last names are joined into one string with no spaces or any delimeter except, the UCase letter of the surname.

What process would I use to Parse the first and second part of the name into separate FirstName and LastName fields.

Thank you for your help with this Richard.

Kindest regards,


Reply from Richard Rost:

That's not going to be easy, but it's not impossible. You would have to create a custom function in VBA and loop down the field looking for the first capital letter (either by ASC char value or with a string). Once you have that, you can use the rest of my techniques to split the field. If you can't figure out how to do it, let me know and I can put the function together for you. Probably take me about half an hour.

Deon on 10/13/2013: Hi Richard,

Thanks for this advice. I'll now give this a serious go.



Reply from Richard Rost:

You're welcome.

Alex Hedley on 11/1/2013: Saw a nice piece of SQL to do this whilst surfing some forums:

Left([Names of employees], InStr([Names of employees]," ")) As FirstName,
Mid([Names of employees], InStr([Names of employees]," ")+1) As LastName
FROM Names;

Alan Lipps on 8/18/2014: Perhaps you already cover this somewhere but I would like to know how to take column headings from an Excel file and place them in a single Access field.

for Example:

In Excel:
Id Q1 Q2 Q3
1 5 6 5
2 6 5 6
3 5 5 5

(ID is the foreign key; Q 1-3 values can be any number--in my case between 0 and 9)

to (Access)
ID Question Score
1 1 5
1 2 6
1 3 5
2 1 6
2 2 5
2 3 6
3 1 5
3 2 5
3 3 5

Reply from Richard Rost:

You would need to do this with action queries. Set up the table, then you'll need APPEND queries for each of your fields.

Richard Wilson on 8/21/2014: Richard, I have watched all the lessons on Many to Many Relationships, the Relationship Seminar, the Action Queries, etc and cannot find an answer to my problem. I have a Many to Many situation in which teachers hold faculty positions at several Universities, and a University will have many teachers. I have all the data which I have imported from Excel Spreadsheets into tables: SchoolT and TeacherT. Now I need to get it straightened out via a junction table. Out of about 750 Teachers and 300 schools there are about 100 teachers with at least two schools. Can you point to the lesson in which you might have covered something that would help in this problem. I can do it by hand, but that is tedious at best. Thanks.

Reply from Richard Rost:

This all depends on how you have your data set up. You've got the schools and the teachers in their respective tables, but where do you have listed which teachers belong to which schools? If that's in a spreadsheet somewhere, you may be able to import it and then use an UPDATE QUERY to change the school name, for example, to an ID value, and then use an APPEND QUERY to add all of those to a junction table. Without seeing a sample of your data, I really can't tell. I will say that in most cases like this, you can't easily get away from some manual data entry. Hire an intern. :)

Richard W on 8/26/2014: I did it myself. No inter!! I'm "on vacation" (ha). I had imported both the schools and teachers into separate tables. I then exported them in to separate Excel spreadsheets with the ID numbers, I matched the names up (with some effort), changed the numbers by hand and reimported them into Access. No perfect, but I am getting there. What's more, my wife, for whom this is all being done, SEEMS to be using the not yet complete database successfully. I don't tackle small problems to begin with. Actually, I have three separate databases in Act! that I am trying to consolidate into one in Access. I think it will work, but NONE of it deals with products, vendors, etc, so I have to use my imagination to interpret what you teach into my situation! But I think I can at about the third or fourth time through a lesson.

Reply from Richard Rost:

That's wonderful. Playing around with it like that is the best way to learn... watch my examples and then figure out how to apply them to YOUR project.

Robert Stockey on 11/24/2014: Great Tips
Bruce Reynolds on 11/25/2014: Couldn't you use the find function to find the space in the FN calculated field in the query instead of SpacePos-1?

Reply from Alex Hedley:

There are usually more than one way to achieve the same goal.

Do you have an example where you've used FIND you can share?

There's the FIND Function that's a member of the Access.Module but it's not well documented from my quick research.

Frank Fish on 11/30/2014: Richard, I was updating a expiration date in my (backup)database, and used:

The result of the update removed the old date, and left the field blank. Did I miss something? are dates "updated" differently? Thanks FWFII

Frank Fish on 11/30/2014: OK, I found it; I needed to place [] around the field (as you showed in the video)
Cheryl Hokanson on 4/1/2016: Could you use an Append query to add records into the end of a checkbook type table which comes from an Excel spread sheet?

Reply from Alex Hedley:

Certainly, Expert 20 shows import from other sources.


You may want to read these articles from the 599CD News:


Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP