|
|
|
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 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 |
|
|
|