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 xyz.com to abc.com. 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
amicron.com change to 599cd.com
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
 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
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