Free Lessons
Fast Tips
Topic Index
Home   Courses   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  

< 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: This class was recorded with Microsoft Access 2013. The material is valid for Access 2007 up to 2021. Access has not changed that much over the years.
Pre-Requisites: Access Expert Level 13 strongly recommended
Running Time: 1 Hour, 34 Minutes
Cost: $25.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


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/25/2024 9:14:17 PM. PLT: 0s